> > >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.
