CODEWITHSUNDEEP
sqlmysqloptimizationleft-joininner-join
RSilva
RSilva

Reputation: 6943

optimize SQL query

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

Upvotes: 3

Views: 566

Answers (6)

SFA
SFA

Reputation:

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.

Upvotes: 0

Thorsten
Thorsten

Reputation: 13181

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.

Upvotes: 5

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58451

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

Regards
Lieven

Upvotes: 0

AnthonyWJones
AnthonyWJones

Reputation: 189457

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.

Upvotes: 0

theomega
theomega

Reputation: 32041

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.

Upvotes: 9

Mike Woodhouse
Mike Woodhouse

Reputation: 52326

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?

Upvotes: 1

Related Questions