Slow MySQL query

Question

I have a query in MySQL (used in a stored procedure) that searches by name and another field. When I use different combinations of these search parameters, I get quick results (from 1 to 2 s), but with some specific values, I get a query that takes 9 s to return the results on the production website. Here is what I got from the EXPLAIN instruction:

id, select_type, table, type, possible_keys, key,       key_len, ref,   rows, Extra
--------------------------------------------
1,  SIMPLE,      Names, ref,  IX_Name,       IX_Name,   17,      const, 3173, Using where

The name is declared as varchar (40) and the other field is Unsigned smallint (6). I use the index for the first 15 characters of the name (IX_Name), which is used in the query. I see that slow queries get enough rows to check the "rows" column of EXPLAIN output.

I'm not sure what I can do to improve performance. Is there anything noticeable with the EXPLAIN output above?

Thanks Tim

Answer 1

How did you fill out the table? Indexes are tree-like structures and to work efficiently they must be balanced - which will happen automatically if the table is loaded in batch or regular maintenance. If none of them is true, the index will be significantly less effective for those parts of the tree that have grown excessively.

The simplest test is to reset the index and re-create it. If after that you have the same behavior, it will be something else, but at least this possibility will be eliminated.

Answer 2

, . , " ". MySQL ( ) . , , MySQL, , , . , . , 3000 , , .

?

SQL? .. , , . , , . , , , .

, , , , , , . "using index".

Jacob

Answer 3

, . 15 , , 1, :

Al

, , , .

, "Al" , . "Al" "Al" , , , .

, "Alex", . "" "" , , , .

"Bud" ( -, "B" ), . , , , .

:

select substring( name, 1, 15), count(*)
from names
group by substring( name, 1, 15);

I think you will find that your fast searches are unique, while slow ones are where many names have a common prefix.

Answer 4

I find 3,173 lines to consider a fairly large number, suggesting that you want to give them to the user. If the actual number of records retrieved is significantly less, you should consider creating additional indexes. It would be helpful to know what the EXPLAIN message is for another search query.