Mysql indexes weird behavior
From: Nikhil Khekade <nikhil.khekade_at_gmail.com>
Date: Sat, 2 May 2015 01:03:49 -0700 (PDT)
Message-ID: <83d8a9eb-44a0-4bf5-b185-b2d725f01cc9_at_googlegroups.com>
I have a table with more than 200,000+ rows & 81 columns. I have total 31 indexes. Yesterday I added a new index on one column. Now when I use that column individually in a where clause, results are very fast. But as soon as I add another condition in where clause on different index column, the query becomes slower. For eg if I have columsn c1, c2, c3, c4 with indexes, all queries like "select c1,c2 from table1 where c1=a and c2=b and c3 = d" ar very fast. But when I added new index on existing column c5, queries like 'select c5 from table1 where c5>a' are fast but as soon as i add new condition like 'select c5 from table1 where c5>a and c1=b', queries become slower. When i am saying slow, its slower 15-20 times. Fast queries taking 0.2 secs, slow queries taking 3-4 secs.
Date: Sat, 2 May 2015 01:03:49 -0700 (PDT)
Message-ID: <83d8a9eb-44a0-4bf5-b185-b2d725f01cc9_at_googlegroups.com>
I have a table with more than 200,000+ rows & 81 columns. I have total 31 indexes. Yesterday I added a new index on one column. Now when I use that column individually in a where clause, results are very fast. But as soon as I add another condition in where clause on different index column, the query becomes slower. For eg if I have columsn c1, c2, c3, c4 with indexes, all queries like "select c1,c2 from table1 where c1=a and c2=b and c3 = d" ar very fast. But when I added new index on existing column c5, queries like 'select c5 from table1 where c5>a' are fast but as soon as i add new condition like 'select c5 from table1 where c5>a and c1=b', queries become slower. When i am saying slow, its slower 15-20 times. Fast queries taking 0.2 secs, slow queries taking 3-4 secs.
Help is much needed. Thanks Received on Sat May 02 2015 - 10:03:49 CEST