Recently in a interview, i came across a question where i was asked what would be steps to optimise following query
select * from users
where id=1 or name='xyz'
My answer was
-
Me:Check if we need all the columns for select *, Panel: agreed all columns were needed as the table contained only 2 columns name(text) and id(integer).
-
Me:I assume ID column is PK and it already indexed, Panel: agreed but still went to say query is slow.
-
Me: Surely the second part of OR condition would be the issue, Name column can use a Hash Index as its a text field and is used with a equality operator, Panel still said query is slow its not using the index.
-
Me: Its tough to decipher without query plan, index may not be been picked up cause of variety of reasons Low cardinality, Cost of index scan is higher than sequential scan, stats not updated etc etc.
But the panel kept on saying, how can you optimise it, to my frustration at the end i asked what is the right answer, Panel replied google it:D and i did but couldn’t find anything relevant maybe there is some pattern here that i have not deciphered
Does anyone know what it is?