lambooni
281
20
Laravel

Multiple indexes slowing down query

Posted 1 month ago by lambooni

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 - table_col1_col2_col3_index;

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 table_col1_index

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.

Please sign in or create an account to participate in this conversation.