Re: Mysql indexes weird behavior

On 02/05/15 10:03, Nikhil Khekade wrote:

> 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.

The amount of columns feels ridiculous, you should have designed that a bit better, I understand that you just can't go and change things, but you may need to plan a normalization of the database.

You would need a joint index on the things you search, not individual indexes for each column.

https://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

You can use the EXPLAIN command to see what MySQL/MariaDB does when you make your query:

"explain select c1, c2 from table1 where c1 = 'a' and c2 = 'b' and c5 = 's'"

It may show that you do not use the indexes you already created.

Don't forget that the number of indexes will cause data input to get slower, as each index has to be updated before next insert can be done.

-- 

  //Aho