On Nov 12, 2007 7:57 AM, Stut <[EMAIL PROTECTED]> wrote:
> 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
>
If you have to deal with it again consider using a bunch of unions
instead of the 'IN'. Not prettiest thing, but it should fix your
performance issue.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
