> -----Original Message----- > From: "Marcus Bointon" <[EMAIL PROTECTED]> [SNIPPED] > That takes 11 seconds to run. Without the order by it takes 0.13 sec. > I have simple indexes on both first name and last name (they are > sometimes searched separately). It strikes me that this is really > very slow - it really doesn't have much to sort. I tied doing an > explain, and though I could see that it was using the indexes, it was > also saying use where, use temporary, use filesort. Why is it falling > back to these methods? How can I make this faster?
Think you will find, should you study the query carefully, that in fact poor MySQL needs to query and actually sort all 400,000 records (was it not for the account id), before it can give you your batch of 30. If MySQL does not have enough ram allocated, may this even entail disk swapping for a NxMb table. This is of course the case because MySQL can not possibly give you the top 30, without first having to use the pertinent index to sort all of them. Indexes on the name and surname may also be a deathshot rather than a blessing, as these indexes would be almost as big as the original table. So instead of simply loading and sorting through one file, MySQL now has to do it with two equally big files. Dare I suggest the following: 1. Remove your name and surname indexes. 2. Ensure you have an index on the account column. 3. Insert a new composite column into the table which is of fixed width (CHAR) and at most 4/5 characters wide. Now populate this column with the first 2/3 characters of the surname and first 2 of the name, index this column and rather sort by it. (You can obviously change the containing data's permutation as you like, ex. first 4 of the surname, etc. 4. Also ensure MySQL has enough ram allocated (see show variables) such that it can load the complete table index in RAM (if possible, even the table's data), so that no disk/virtual mem swapping takes place. I think you will find MySQL much snappier with this source data, as it will first filter the data quickly by account and ordering of the subset should be very quick using only a 4 char column index. Chances are also good that if the first 4 chars of a surname match, the surnames are most likely identical (granted, the odd one will be sorted below rather than above it's actual position), but in the end is it a balance between speed and the odd mis-ordered record. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
