When search by only one of primary key in composite primary key table Spanner behaviors is differ. For Example if a table has ColA and ColB as primary key( mentioned in the same order when defining primary key). If you search by first key select * from table where ColA = 'dfdf' then it scans few rows and brings result much faster ~10ms. But if you search by second key select * from table where ColB = 'dfdf' then it does full table scan. Why this inconsistency, if we are not searching by full key then it should do full table or particular rows scan. primary keys are indexed so it should never go to full table scan.
1
votes
Composite primary-key index is always ordered by the keys. I.e. if you have keys: [ColA,ColB], you can search by ColA and only then by ColB. You can't use it to search by ColB only, this will require full table scan. If you do want to search by ColB without ColA - then you should use Secondary Index.
– shlomiw
1 Answers
0
votes
A composite key is not 2 separate keys, but one single key concatenated from 2 parts...
Imagine a list of words, alphabetically sorted... Finding words whose first letter is H is easy... But how would you find all words whose second letter is 'H'...
The only way is to do a complete scan of all the words -- unless there is a second index of words by their second letter, which is what secondary indexes are for ..
