MySQL LEFT JOIN with ORDER BY – Improve Query Performance

If the foreign key is not nullable, then there is no reason for the join at all, is there? So why not let SQL Server scan the most efficient index available instead of computing a join that will result in the exact same count?

SELECT COUNT(*) FROM dbo.messages;

That said, you can do this much more efficiently:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.messages')
  AND index_id IN (0,1);

This will be slightly inaccurate due to in-flight transactions, but it also won't block (or be blocked by) any activity against the table, so pick your poison.

This is a "relational division" problem and there are several ways to solve.

One way is with two EXISTS subqueries:

SELECT p.id 
FROM photos AS p
WHERE EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cute'
      )
 AND  EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cool'
      ) ;

Another with two joins (assuming that the photo_tags table has a unique constraint on (photo_id, tag_id), i.e. no photo can been tagged with the same tag twice):

SELECT p.id 
FROM photos AS p
  JOIN photos_tags AS pt1
    ON  p.id = pt1.photo_id 
    AND pt1.tag = 'cute'
  JOIN photos_tags AS pt2
    ON  p.id = pt2.photo_id 
    AND pt2.tag = 'cool' ;

One more way is @Abhik's answer, with one join and GROUP BY.

You can see a few more ways (more than 13) in this awesome answer: How to filter SQL results in a has-many-through relation. Not all of them will work in MySQL as the question is for Postgres but the problem is identical.

Strictly speaking - and if there is a FOREIGN KEY from photo_tags to photos - the photos table can be removed from all the above queries. You can return the photo_id instead. But I guess you may want more columns from the photo table in the result set.