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
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
