Hi Rob, Thanks for your reply.
Rob Wultsch wrote:
On Nov 8, 2007 4:08 PM, Stut <[EMAIL PROTECTED]> wrote:Hi all, I've inherited a PHP app that uses a MySQL database. The following query is extremely slow and I've been battling for a couple of days on an off to try and get a combination of indexes to optimise it. Any help would be greatly appreciated. select household_d.id, household_d.ad_type, household_d.ad_catid, household_d.ad_renewed, household_d.ad_userid, household_d.ad_trade, household_d.price, SUBSTRING(household_d.description, 1, 301) as description, users.issuperseller, users.phone, users.town from household_d left join users on household_d.ad_userid = users.id where household_d.ad_catid in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and household_d.ad_status = "live" and household_d.id not in (1, 2) order by ad_renewed desc limit 0,14 Explain currently states that it's using the primary key for the users table, and the following for the household_d table... select_type = SIMPLE type = ref key = ad_status ref = const key_len = 1 key_len rows = 22137 extra = Using where; Using filesort Running locally this query is pretty fast, but on the live site it is currently taking anything up to a minute. My limited knowledge of MySQL indexes led me to add an index with ad_catid, ad_status, ad_renewed and id, but explain only says it's a possible key, it doesn't actually use it. Any tips appreciated.A few things pop out at me: 1. Mysql is known for not dealing with 'IN(' or 'OR' queries particulary well. Could you instead do something like: where household_d.ad_catid BETWEEN 1 AND 10 ? 1.1 Same deal with household_d.id
Unfortunately not. Neither ad_catid nor id are sequential.
2. I am going to guess that ad_status has very low cardinality. I generally build up composite index's by creating an index on whatever column I think would have the highest useful cardinality. I then test it to make sure mysql actually uses the new index. If it does not I figure out why. I then add another column to the index and test the query to make sure extra length gets used and the query did not get slower. Rinse, wipe, repeat.
That's basically what I was doing, but I wasn't getting anywhere. Since I posted the question I've thrown more RAM at the server and it's a lot better now but I still worry that it's using where and filesort but it's possible there's no way to make it any quicker. I've not had any complaints about the speed since I put more RAM in.
Thanks again. -Stut -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
