List:General Discussion« Previous MessageNext Message »
From:Marko Knezevic Date:November 25 2005 8:43am
Subject:Re: How to compose index?
View as plain text  
>
> >I have following query:
> >
> > SELECT SQL_NO_CACHE users.user_name assigned_user_name, accounts.* FROM
> > accounts LEFT JOIN users ON accounts.assigned_user_id=users.id where
> > (accounts.assigned_user_id='1') AND  accounts.deleted=0  ORDER BY
> > phone_office asc LIMIT 620300,20
> >
> > In your opinion, what group of indexes should i use to gain maximum
> > performance out of this query?
>
> Table users
> ========
> index_id: id
>
> Table accounts
> ==========
> index_id: assigned_user_id, deleted
> index_phone: phone_office
>
> After that, do an EXPLAIN in the query.
> I´m not sure about index_phone will help you.


hmm, we already have all mentioned indexes.
idx_id (for users: id)
idx_id (for accouns: id)
idx_uid_del (for accounts: assigned_user_id, deleted)
idx_phoff (for accounts: phone_office)

This query on 600.000 records takes 9.30 seconds..

this is what explain says:
table accounts, type all, key null, rows 465230, extra: using where; using
filesort
table users is ok - using primary index...

but when I use FORCE INDEX (idx_uid_del) then it is ok and takes
0.01seconds.. how can I manage that mysql use this index without force
index ?
Is it possible ?

thanks.

Thread
How to compose index?Marko Knezevic24 Nov
  • Re: How to compose index?Ronan Lucio24 Nov
    • Re: How to compose index?Marko Knezevic25 Nov
      • Re: How to compose index?Gleb Paharenko25 Nov
      • Re: How to compose index?Gleb Paharenko26 Nov