Mysql – Left outer join ruins query in MySQL

MySQL

I have two tables. One of them is big, long with more than 500 columns (most of those are tinyint) and 360000 rows. It contains info about photos. Second one is small with just three int columns (surrogate primary key and two foreign keys) and one decimal. It has just 45000 rows and contains info about what people are on those photos. It's so small it doesn't even have indexes. What I have trouble with is selecting photos within clause that have no people marked on them.
Query with clause within just first big table takes acceptable few seconds. Then I make something like that

SELECT photos.* FROM photos 
 LEFT OUTER JOIN 
 photos_people ON photos.photo_id = photos_people.photo_id 
WHERE photos_people.photo_people_id is NULL AND ... LIMIT 150;

Now it suddenly takes literally minutes. What am I doing wrong? Is there a way to speed up query?

Best Answer

Add an index on the column photo_id on both the tables- photos and photo_people.

Related Solutions

TheSQL query optimisation — multiple joins or select … where not in (select distinct…)

I would add an index on users_roles (rid, uid). In a many-to-many table with two columns (a,b), you almost always will need both indexes: (a,b) and (b,a) in one query or the other. I think this index would help in this query.
Try various rewritings of the query and the EXPLAIN EXTENDED they produce.
About your suggestions, the first is not correct (it will not show same results). For the second suggestion:

WHERE users.status = 1                                           -- Active users only

Yes, that's better than users.status <> 0. This change may have a better effect if there is an index on users (status) (and even more if there are not many active users). Optimizing queries with boolean columns (or ones that act as boolean) is not easy with B-trees.

  AND users.uid IN
     (SELECT DISTINCT uid FROM users_roles WHERE rid = 5)        -- Must be in rôle A

No. MySQL is known to have issues with column IN (SELECT ...), especially if the external table is big (and yours is 200K columns, so no, not good).

  AND users.uid NOT IN
     (SELECT DISTINCT uid FROM users_roles WHERE rid IN (6,8,9)) -- Not rôles B, C, D

Yes, that is one way to rewrite. The DISTINCT is redundant though.

  AND users.uid <> :users_uid                                    -- Not current user

Yes, removing the users.uid IS NOT NULL may help and does not change the result.

Other things you could try:

Moving the rid = 5 condition to the ON clause:

INNER JOIN users_roles users_roles 
  ON  users.uid = users_roles.uid
  AND users_roles.rid = 5

The (rewrite) to NOT IN can also be written with NOT EXISTS:

  AND NOT EXISTS 
      ( SELECT * 
        FROM users_roles ur 
        WHERE ur.uid = users.uid 
          AND ur.rid IN (6,8,9)
      )

MySQL slow queries – Fast using PHPMyAdmin , low CPU and RAM usage

You need to identify which queries are taking some time to execute and optimize accordingly. By default mysql does not log slow queries, so you have to add the following to your mysql config file -

log-slow-queries
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10     // in seconds - you can replace this with another number.

For any mysql performance issues, it is a good idea to use mysqltuner

Best Answer

Related Solutions

TheSQL query optimisation — multiple joins or select … where not in (select distinct…)

MySQL slow queries – Fast using PHPMyAdmin , low CPU and RAM usage

Related Question