I am struggling to work out which columns are best to put my indexes on, when it seems adding additional indexes can have a detrimental effect on the query performance.
For example, I have the following query on a table with around 5m rows;
SELECT col1, col2 FROM table WHERE col1 = 'a' AND col2 = 'b' AND col3 = 'c';
Running this with no indexes takes 12 seconds!
I add a compound index on all 3 columns -
My query now drops down to 2 seconds - great!
I now have another query on the same table (with no indexes on any column):
SELECT col1, col2 FROM table WHERE col1 = 'a';
Running this on its own and the query takes 4 seconds - still pretty slow!
So now I add a single column index to col1
My query reduces down to 0.2 seconds. This is great, however I now run the original query again and notice that it is using this index opposed to the one I specified earlier. The original query is now back up at 6 seconds.
I am unsure how to go about ensuring that both queries can be optimised at the same time.