Optimize SQL query

Question

What else 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 am using MySQL

thanks

Answer 1

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

But I also suggest trying to “clear” your expression. (I don’t know which one is faster - it depends on the size of your tables.) Usually I try to start with a clean statement and start optimizing from there. But, as a rule, a clean report makes it easier for the optimizer to complete a good execution plan.

So, here are some notes about your application that may slow you down:

• ( )

SQL, :

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)

, , , :

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

, . , , "".

myItems ( ).

, .

Answer 2

? , - . , , .

Answer 3

:

ItemID userID?

, "EXPLAIN" ? ? ?

, where myTable.userIDFav = 3 or myTable.userIDFav is null ?

Answer 4

, Group By, - ItemID, , SELECT SELECT, .

Answer 5

where myTable.userIDFav = 3 myTable.userIDFav null (item.deleted = 0)?

Answer 6

, . , 20 . ​​myitems, , 20 , . , .