Index help 2007-11-08 - By Stut
Back
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.
-Stut
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
