Not the answer you need?
Register and ask your own question!

Performance issue (index not use when using "WHERE IN ( +10 elements )"

HaricotHaricot EntrantCurrent User Role Beginner
Hello ,
this is my first post ,
we are using Percona 5.6
and we encountered a performance issue
[IMG]file:///C:%5CUsers%5CNico%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image002.jpg[/IMG][IMG]file:///C:%5CUsers%5CNico%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_image002.jpg[/IMG]We have got a simple query using « WHERE xxx IN (‘foo1’,’foo2’,…) , » and mysql stop using indexes above 10 elements (foo1,foo10)
As you can see (on pictures) the number of row blow up when we use +10 elements
But if we remove lastname from the SELECT it works fine

There is an index on emailmd5
and the table is partitionned

can someone could help me ?

Thank you

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello there, thanks for your question.
    It's likely that based on your data, the optimizer chose a different path once it got to the additional 'in' ... we are assuming that the queries that you've provided as examples aren't modified to reduce the amount of information you're passing here.
    If it's different without last_name, is it because you index emailmd5 and not last_name? So that when you only search for emailmd5 it doesn't have to leave the index?

    Depending how your application is structured, one of the team here suggested you maybe might get more consistent performance by executing the 10 queries one a time. The alternative might be to influence the optimizer to use indexes through hints see here https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

    If you post again, it might be easier for the team here to work with text than to work with screen prints, since we can't copy the text of your queries from an image in order to easily provide different examples... and noone likes to type if they don't have to. :)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.