optimize SQL query

Question

What more can I do to optimize this query?

SELECT * FROM
    (SELECT `item`.itemID, COUNT(`votes`.itemID)  AS `votes`,
        `item`.title, `item`.itemTypeID, `item`.
        submitDate, `item`.deleted, `item`.ItemCat,
        `item`.counter, `item`.userID, `users`.name,
        TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' ,
        `myItems`.userID as userIDFav, `myItems`.deleted as myDeleted
      FROM    (votes `votes` RIGHT OUTER JOIN item `item`
         ON (`votes`.itemID = `item`.itemID))
        INNER JOIN
        users `users`
        ON (`users`.userID = `item`.userID)
    LEFT OUTER JOIN
        myItems `myItems`
        ON (`myItems`.itemID = `item`.itemID)
     WHERE (`item`.deleted = 0)
     GROUP BY `item`.itemID,
        `votes`.itemID,
        `item`.title,
        `item`.itemTypeID,
        `item`.submitDate,
        `item`.deleted,
        `item`.ItemCat,
        `item`.counter,
        `item`.userID,
        `users`.name,
        `myItems`.deleted,
        `myItems`.userID
    ORDER BY `item`.itemID DESC) as myTable
where myTable.userIDFav = 3 or myTable.userIDFav is null
      limit 0, 20

I'm using MySQL

Thanks

Answer 1

What does the analyzer say for this query? Without knowledge about how many rows there are in the table you cant tell any optimization. So run the analyzer and you'll see what parts costs what.

Answer 2

Some quick semi-random thoughts:

Are your itemID and userID columns indexed?

What happens if you add "EXPLAIN " to the start of the query and run it? Does it use indexes? Are they sensible?

DO you need to run the whole inner query and filter on it, or could you put move the where myTable.userIDFav = 3 or myTable.userIDFav is null part into the inner query?

Answer 3

You do seem to have too many fields in the Group By list, since one of them is itemID, I suspect that you could use an inner SELECT to preform the grouping and an outer SELECT to return the set of fields desired.

Answer 4

Can't you add the where clause myTable.userIDFav = 3 or myTable.userIDFav is null to WHERE (item.deleted = 0)?

Regards
Lieven

Answer 5

Of course, as @theomega said, look at the execution plan.

But I'd also suggest to try and "clean up" your statement. (I don't know which one is faster - that depends on your table sizes.) Usually, I'd try to start with a clean statement and start optimizing from there. But typically, a clean statement makes it easier for the optimizer to come up with a good execution plan.

So here are some observations about your statement that might make things slow:

• a couple of outer joins (makes it hard for the optimzer to figure out an index to use)
• a group by
• a lot of columns to group by

As far as I understand your SQL, this statement should do most of what yours is doing:

SELECT `item`.itemID, `item`.title, `item`.itemTypeID, `item`.
       submitDate, `item`.deleted, `item`.ItemCat,
       `item`.counter, `item`.userID, `users`.name,
       TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' 
  FROM    (item `item` INNER JOIN users `users`
       ON (`users`.userID = `item`.userID)

WHERE

Of course, this misses the info from the tables you outer joined, I'd suggest to try to add the required columns via a subselect:

SELECT `item`.itemID, 
       (SELECT count (itemID)
        FROM votes v
       WHERE v.itemID = 'item'.itemID) as 'votes', <etc.>

This way, you can get rid of one outer join and the group by. The outer join is replaced by the subselect, so there is a trade-off which may be bad for the "cleaner" statement.

Depending on the cardinality between item and myItems, you can do the same or you'd have to stick with the outer join (but no need to reintroduce the group by).

Hope this helps.

Answer 6

Look at the way your query is built. You join a lot of stuff, then limit the output to 20 rows. You should have the outer join on items and myitems, since your conditions only apply to these two tables, limit the output to the first 20 rows, then join and aggregate. Here you are performing a lot of work that is going to be discarded.