Slow MySQL Query

Question

Hi,

I have a query in MySQL (used in a stored procedure) which searches by name and another 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

Name is declared as varchar(40) and the other field is Unsigned smallint(6). I am using an index on the first 15 characters of the Name (IX_Name) which is being used in the query. I can see that the slow queries get a quite large number of rows to check in the "rows" column of the EXPLAIN output.

I am not sure what I can do to improve the performance. Is there anything noticeably wrong with the EXPLAIN output above?

Thanks, Tim

Answer 1

How did you populate the table? Indexes are tree structures and to work efficiently they need to be balanced - which will happen automatically if the table is loaded in batch or regular maintenance is applied. If neither of these is true then the index will be significantly less efficient for those parts of the tree which have grown excessively.

Simplest check is to drop the index and recreate it again. If you have the same behaviour afterwards it's something else, but at least that's one possibility eliminated.

Answer 2

Okay, you have an index on a prefix. You use the first 15 characters, but let's pretend you used only 1, and your table had these values for names:

Al Barb Beth Betsy Bill Biff Bob Bonny Buck Bud Carl

Since my index is only on the first character, the database has to get read all the rows the index gives back, and compare each entire name to the predicate.

Now, if I look for 'Al', my index gives one row back. I then compare 'Al' in the preicate to 'Al' in the row, and I have a match, so I return that row, and I'm done.

Now if I look for 'Alex', my index gives one row back. I then compare 'Alex' in the preicate to 'Al' in the row, and I have no match, and no more potential matches, and I'm done.

But if I look for 'Bud' (or anything beginning with 'B'), my index gives nine rows back. I have nine rows I need to read, and compare to the predicate, before I'm done.

Do this:

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

I think you'll find your quick lookups have are unique, while the slow ones are where many names share a common prefix.

Answer 3

I find 3173 rows to consider a fairly large number, assuming you want to render them to a user. If the actual number of records retrieved is substantially smaller, you should consider creating more indices. It would be useful to know what EXPLAIN reports for a different search term.

Answer 4

It appears that your query is only using the index for one field. You mentioned that you search by name and "another field". MySQL (except in very recent version in particular circumstances) is limited to one index per table occurrence in a query. This means that if you have an index on name and and index on the other field, MySQL will likely have to guess at which index would be most helpful and ignore the other one. It seems that a better query structure or index definition would be helpful. If your name is pretty unique and you are getting 3,000 rows in the explain plan, then either the metadata in the DB is not good or you have a ton of other possibilities on the other field.

Can you post the query and the schema for the table?

Are your queries always fast or always slow for the same SQL? i.e. if you search for Fisher sometimes it is fast and sometimes it is slow or are they consistent. If they are consistent, it is probably due to CPU or disk activity. If variable, it is probably due to other queries on the DB.

Also, depending on what you are selecting, if you can get your full result into an index, your query will fly since it will not have to hit the disk to verify the records. I have had pretty amazing improvements with "using index" queries.

Jacob

Answer 5

Hi, it's me again...I deleted my broswer cookie and cannot comment on individual answers...

Cruachan, I tried dropping all indices and recreate them but there are the same culprit queries which still take long unfortunately.

TheJacobTaylor, thanks for your reply.

The query plan shows that the index is being used and on different names the query can be instantaneous. However, it doesn't like when I search for Fischer and a particular value for another numeric field. Below is the schema of my table.

namenumber: Primary Key,int,unsigned,not null,auto inc f1: varchar(40) f2: 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,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13 FROM names IGNORE INDEX (IX_IGNORE1,IX_IGNORE2,IX_IGNORE3) WHERE ((f1 = 'fischer')) AND (f12 = 270) LIMIT 0,14

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

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

Any ideas?

Thanks in advance, Tim