> >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
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
Is it possible ?