MySQL slow query on rare cases even if index is in use
I have a query on a large MySQL table (> 4 million rows). This query is used in a stored procedure and searches by last name and another numeric field. When I use different combinations of these search parameters, I get fast results (1 to 2s), but with some specific values, I get a query that takes 9s to return results on the production website. This is what I got from 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
Surazh is declared as varchar (40) and the other field is unsigned smallint (6). The index used is IX_Name, which consists of the prefixes of last name (15) and first name (10)
I'm not sure what I can do to improve performance. Is there something noticeable with the EXPLAIN output above? The query is slow only on rare combinations of last name and another field, but it is consistent with the fact that they are always the same slow queries.
I tried dropping all indexes and re-creating them, but that did not resolve the problematic queries.
The query plan shows that the index is used and for different surnames (and different values for another numeric field) the query can be instantaneous. However, it doesn't like it when I search for the last name "Fischer" and a specific value for a numeric field (this is one particular slow query). Could it be because there are many overlapping Fischer names in my table? (about 3500). But then, what can be done to optimize this query? Below is a schematic of the 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
I am making the following request:
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. 15 char prefix for last name and 10 char prefix for filename.
For most queries this is very fast, but for rare, it takes about 9 seconds to return results to the web page, showing the EXPLAIN output as above.
Any ideas?
Thanks in advance, TM
source to share
Have you tried to change the IX_Name index using only the last name. Perhaps the index is delaying the delay - because it returns rows according to last name and first name (which is not part of your query) and requires as soon as part of the actual content of the column taking time to compute.
source to share