To demonstrate this usecase, we retrieve bunch of columns for a primary key. Typical SQL would be,
"SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE WHERE PRIMARY_KEY={X}".
Lets analyze what happens when we execute above SQL,
1) Query passes through SQL engine.
a) Lexical analysis & Parsing of SQL statement (Exception will be raised if any syntax error found).
b) SQL optimization to choose optimal execution path for the statement.
2) Searching the index for primary key
Most databses store table indexs on tree structure. B+ tree is most commonly and widely used indexing structure. Even with, highly optimized B+ index structure, some form of tree search is required to locate the primary key.
3) Primary key is located, perform data retrieval.
Primary key is located, corresponding row is returned for that primary key.
Is SQL way of retrieving data based on primary key effective?. Lets see how we implement same usecase with NOSQL,
"GET [TABLE][PRIMARY_KEY]"
In NOSQL, primary key and corresponding columns are stored as Hash (key => primarykey, value => column values as string/jason). With NOSQL, looking up primary key is performed in constant time and there is no need for lexical analysis, parsing and optimization. Does this pattern sound familer?. Many large websites use MySQL with Memcached. Memcached will serve as in-memory NOSQL.
NOSQL is better for fast retrieval of data using primary key. MySQL + Memcached combination could be used to achieve the same.
