MySQL slow query in rare cases even though index is being used

Question

Hi,

I have a query in a large MySQL table (>4 million rows). This query is used in a stored procedure and it searches by surname and another numeric field. When I use different combinations of these search parameters, I get quick results (between 1 and 2s) but with some particular values, I get a query which takes 9s to return results on the production website. The following is what I got out of the EXPLAIN statement:

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

Surame is declared as varchar(40) and the other field is unsigned smallint(6). The index which is being used is IX_Name which consists of the prefixes of surname(15) and forename(10)

I am not sure what I can do to improve the performance. Is there anything noticeably wrong with the EXPLAIN output above? The query runs slowly only on rare combinations of the surname and the other field, but it is consistent in that they are always the same slow queries.

I tried dropping all indices and recreated them but this did not solve the problematic queries.

The query plan shows that the index is being used and on different surnames (and various values for the other numeric field) the query can be instantaneous. However, it doesn't like when I search for the surname "Fischer" and a particular value for the numeric field (this is one particular slow query). Could this be because there are many "Fischer" matching names in my table? (about 3500). But in that case, what can be done to optimize this query? Below is the schema of my table.

namenumber: Primary Key,int,unsigned,not null,auto inc; surname: varchar(40); forename: varchar(40); f3: varchar(40); f4: varchar(40); f5: smallint(6),unsigned; f6: smallint(6),unsigned; f7: varchar(40); f8: varchar(40); f9: smallint(6); f10: varchar(10); f11: tinyint(4); f12: smallint(6),unsigned; f13: text

The query that I am running is the following:

SELECT namenumber,surname,forename,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13
FROM names IGNORE INDEX (IX_IGNORE1,IX_IGNORE2,IX_IGNORE3)
WHERE ((surname = 'fischer')) AND (f12 = 270)
LIMIT 0,14

MySQL uses the following index (IX_Name) which consists of the following fields: surname(15),forename(10) i.e. a 15 char prefix of surname and 10 char prefix of forename.

For most queries this is very fast, but for the rare ones, it takes about 9 seconds to return results to the web page, showing the output of EXPLAIN as above.

Any ideas?

Thanks in advance, TM

Answer 1

Have you tried to modify the index IX_Name using only the surname. It could be possible that the index causes the delay - because he returns the rows according to surname and forename (which is not part of your query) and needs for both only portions of the real column-content - which needs time to calculate.