How can I improve the speed of this MySQL query?

Question

Here's the query:

SELECT 
u.uid as UID,
fuo.uid as FUO_UID,
fo.prid as FO_NAME
FROM 
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)
WHERE 
u.status=1 AND u.uid>1 
ORDER BY u.uid 
LIMIT 3;

And the tables:

users
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| 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 | Extra |
+-------+------------------+------+-----+---------+-------+
| nid   | int(10) unsigned | NO   | PRI | 0       |       |
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+

firstpoint_organisations
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| nid      | int(10) unsigned | NO   | PRI | 0       |       |
| prid     | varchar(32)      | NO   |     |         |       |
+------------------------------------------------------------+

I wish to show users.uid and firstpoint_organisations.prid for every row in users, even though some users won't have a prid, in which case I show NULL (hence the left outer joins). The connection should be as follows:

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

So each user (users) has a user id (uid), and the organisation they're associated with (firstpoint_users_organisation) has a node id (nid) and stores this association. The organisation's details are then stored in firstpoint_organisations.

So every user will have a prid, but if they don't, show NULL.

Now, if I do an INNER JOIN on firstpoint_users_organisations and then on firstpoint_organisations, I get a good query speed (the above query runs in 0.02 seconds). But, when I switch both to LEFT OUTER JOIN, so I can get all users, prid or no prid, the above query takes ~90 seconds to run.

Is there anything I can do to speed this query up? There are approx. 70,000 rows in the users table, but even with LIMIT 3, the making the INNER JOIN a LEFT OUTER JOIN takes a horrible amount of time. Interestingly, the query takes the same amount of time to run with LIMIT 30, so I think there's something fundamentally wrong with my query.

EXPLAIN as requested:

+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                   | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
|  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)

Answer 1

Your query is pointlessly (because uid > 1 would include all but one of the users) using the index on uid, so use the IGNORE INDEX hint for that index:

SELECT 
  u.uid as UID,
  fuo.uid as FUO_UID,
  fo.prid as FO_NAME
FROM 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
WHERE u.status=1
AND u.uid > 1 
ORDER BY u.uid 
LIMIT 3

You should put an index on users(status), which may give you some benefit if there are enough rows with status != 1

It is quite expected that changing the LIMIT would have no effect, because 70000 rows must be sorted before the limit is applied to know which rows are the first rows to return - the limit has little effect, except that less rows are returned to the client (less comma IO)

---

I'm a believer in "less code is good", so from a strictly style point of view I have removed non essential code from your query:

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