How can I improve the speed of this MySQL query? -

here's query:

select  u.uid uid, fuo.uid fuo_uid, fo.prid fo_name  users u left outer join firstpoint_users_organisations fuo on (u.uid=fuo.uid) left outer join firstpoint_organisations fo on (fo.nid=fuo.nid)  u.status=1 , u.uid>1  order u.uid  limit 3; 

and tables:

users +------------------+------------------+------+-----+---------+----------------+ | field            | type             | null | key | default |          | +------------------+------------------+------+-----+---------+----------------+ | uid              | int(10) unsigned | no   | pri | null    | auto_increment | | name             | varchar(60)      | no   | uni |         |                | | status           | tinyint(4)       | no   |     | 0       |                | +-----------------------------------------------------------------------------+  firstpoint_users_organisations +-------+------------------+------+-----+---------+-------+ | field | type             | null | key | default | | +-------+------------------+------+-----+---------+-------+ | nid   | int(10) unsigned | no   | pri | 0       |       | | uid   | int(10) unsigned | no   | pri | 0       |       | +-------+------------------+------+-----+---------+-------+  firstpoint_organisations +----------+------------------+------+-----+---------+-------+ | field    | type             | null | key | default | | +----------+------------------+------+-----+---------+-------+ | nid      | int(10) unsigned | no   | pri | 0       |       | | prid     | varchar(32)      | no   |     |         |       | +------------------------------------------------------------+ 

i wish show users.uid , firstpoint_organisations.prid every row in users, though users won't have prid, in case show null (hence left outer joins). connection should follows:

users uid -      firstpoint_users_organisations       \---->uid            nid -          firstpoint_organisations                 \-------->nid                           prid 

so each user (users) has user id (uid), , organisation they're associated (firstpoint_users_organisation) has node id (nid) , stores association. organisation's details stored in firstpoint_organisations.

so every user have prid, if don't, show null.

now, if inner join on firstpoint_users_organisations , on firstpoint_organisations, query speed (the above query runs in 0.02 seconds). but, when switch both left outer join, can users, prid or no prid, above query takes ~90 seconds run.

is there can speed query up? there approx. 70,000 rows in users table, limit 3, making inner join left outer join takes horrible amount of time. interestingly, query takes same amount of time run limit 30, think there's fundamentally wrong query.

explain requested:

+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+ | id | select_type | table | type   | possible_keys | key     | key_len | ref                   | rows  |                                        | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+ |  1 | simple      | u     | range  | primary       | primary | 4       | null                  | 13152 | using where; using temporary; using filesort | |  1 | simple      | fuo   | index  | null          | primary | 8       | null                  |  3745 | using index                                  | |  1 | simple      | fo    | eq_ref | primary       | primary | 4       | dbdb-dbdb_uat.fuo.nid |     1 |                                              | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+ 3 rows in set (0.00 sec) 

your query pointlessly (because uid > 1 include 1 of users) using index on uid, use ignore index hint index:

select    u.uid uid,   fuo.uid fuo_uid,   fo.prid fo_name users u ignore index (uid) left join firstpoint_users_organisations fuo on u.uid=fuo.uid left join firstpoint_organisations fo on fo.nid=fuo.nid u.status=1 , u.uid > 1  order u.uid  limit 3 

you should put index on users(status), may give benefit if there enough rows status != 1

it quite expected changing limit have no effect, because 70000 rows must sorted before limit applied know which rows first rows return - limit has little effect, except less rows returned client (less comma io)

---

i'm believer in "less code good", strictly style point of view have removed non essential code query:

• removed outer because there no other kind of left join
• removed brackets around join conditions because don't need 'em