Sql Query Performance Question (multiple Sub-queries)

I have this query: SELECT p.id, r.status, r.title FROM page AS p INNER JOIN page_revision as r ON r.pageId = p.id AND ( r.id = (SELECT MAX(r2.id) from page_revision as

Solution 1:

If "active" is the first in alphabetical order you migt be able to reduce subqueries to:

SELECT p.id, r.status, r.title
FROM page AS p
    INNER JOIN page_revision as r ON r.pageId = p.id AND 
        r.id = (SELECT r2.id 
                FROM page_revision as r2 
                WHERE r2.pageId = r.pageId 
                ORDERBY r2.status, r2.id DESC
                LIMIT 1)

Otherwise you can replace ORDER BY line with

ORDERBYCASE r2.status WHEN'active'THEN0ELSE1END, r2.id DESC

These all come from my assumptions on SQL Server, your mileage with MySQL may vary.

Solution 2:

Maybe a little re-factoring is in order?

If you added a latest_revision_id column onto pages your problem would disappear, hopefully with only a couple of lines added to your page editor.

I know it's not normalized but it would simplify (and greatly speed up) the query, and sometimes you do have to denormalize for performance.

Solution 3:

Your problem is a particular case of what is described in this question.

The best you can get using standard ANSI SQL seems to be:

SELECT p.id, r.status, r.title
FROM page AS p
INNER JOIN page_revision as r ON r.pageId = p.id 
AND r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId)

Other approaches are available but dependent on what database you're using. I'm not really sure it can be improved much for MySQL.

Solution 4:

In MS SQL 2005+ and Oracle:

SELECT p.id, r.status, r.title
FROM (
  SELECT p.*, r,*,
         ROW_NUMBER() OVER (PARTITIONBY p.pageId ORDERBYCASEWHEN p.status ='active'THEN0ELSE1END, r.id DESC) AS rn
  FROM page AS p, page_revision r
  WHERE r.id = p.pageId
  ) o
WHERE rn =1

In MySQL that can become a problem, as subqueries cannot use the INDEX RANGE SCAN as the expression from the outer query is not considered constant.

You'll need to create two indexes and a function that returns the last page revision to use those indexes:

CREATE INDEX ix_revision_page_status_id ON page_revision (page_id, id, status);

CREATE INDEX ix_revision_page_id (page_id, id);

CREATEFUNCTION `fn_get_last_revision`(input_id INT) RETURNSint(11)
BEGINDECLARE id INT;
  SELECT r_id
  INTO id
  FROM (
    SELECT r.id
    FROM page_revisions
    FORCE INDEX (ix_revision_page_status_id)
    WHERE page_id = input_id
      AND status ='active'ORDERBY id DESC 
    LIMIT 1UNIONALLSELECT r.id
    FROM page_revisions
    FORCE INDEX (ix_revision_page_id)
    WHERE page_id = input_id
    ORDERBY id DESC 
    LIMIT 1
  ) o
  LIMIT 1;
  RETURN id;
END;

SELECT po.id, r.status, r.title
FROM (
  SELECT p.*, fn_get_last_revision(p.page_id) AS rev_id
  FROM page p
) po, page_revision r
WHERE r.id = po.rev_id;

This will efficiently use index to get the last revision of the page.

P. S. If you will use codes for statuses and use 0 for active, you can get rid of the second index and simplify the query.