Indexes and optimization

Question

I'm not brilliant when it comes to going beyond the basics with MySQL, however, I'm trying to optimize a query:

SELECT DATE_FORMAT(t.completed, '%H') AS hour, t.orderId, t.completed as stamp,
       t.deadline as deadline, t.completedBy as user, p.largeFormat as largeFormat 
    FROM tasks t
    JOIN orders o ON o.id=t.orderId
    JOIN products p ON p.id=o.productId
    WHERE DATE(t.completed) = '2013-09-11'
    AND t.type = 7
    AND t.completedBy IN ('user1', 'user2')
    AND t.suspended = '0'
    AND o.shanleys = 0
    LIMIT 0,100

+----+-------------+-------+--------+----------------------------+-----------+---------+-----------------+-------+-------------+
| id | select_type | table | type   | possible_keys              | key       | key_len | ref             | rows  | Extra       |
+----+-------------+-------+--------+----------------------------+-----------+---------+-----------------+-------+-------------+
|  1 | SIMPLE      | o     | ref    | PRIMARY,productId,shanleys | shanleys  | 2       | const           | 54464 | Using where |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY                    | PRIMARY   | 4       | sfp.o.productId |     1 |             |
|  1 | SIMPLE      | t     | ref    | NewIndex1                  | NewIndex1 | 5       | sfp.o.id        |     6 | Using where |
+----+-------------+-------+--------+----------------------------+-----------+---------+-----------------+-------+-------------+

Before some of the indexes were added it was performing full table scans on both the p table and the o table.

Basically, I thought that MySQL would:

limit down the rows from the tasks table with the where clauses (should be 84 rows without the joins)

then go through the orders table to the products table to get a flag (largeFormat).

My questions are why does MySQL look up 50000+ rows when it's only got 84 different ids to look for, and is there a way that I can optimize the query?

I'm not able to add new fields or new tables.

Thank you in advance!

Answer 1

SQL needs to work on available indexes to best qualify the query

I would have a compound index on ( type, suspended, completedby, completed) to match the criteria you have... Your orders and products tables appear ok with their existing indexes.

SELECT 
      DATE_FORMAT(t.completed, '%H') AS hour, 
      t.orderId, 
      t.completed as stamp,
      t.deadline, 
      t.completedBy as user, 
      p.largeFormat as largeFormat 
   FROM 
      tasks t
         JOIN orders o 
            ON t.orderId = o.id
           AND o.shanleys = 0
            JOIN products p 
               ON o.productId = p.id
   WHERE 
          t.type = 7
      AND t.suspended = 0
      AND t.completedBy IN ('user1', 'user2')
      AND t.completed >= '2013-09-11'
      AND t.completed < '2013-09-12'
   LIMIT 
      0,100

I suspect that suspended is a flag and is numeric (int) based, if so, leave the criteria as a numeric and not string by wrapping in '0' quotes.

FOR datetime fields, if you try TO apply functions TO it, it cant utilize the index well... so, if you only care about the one DAY(or range in other queries), notice I have the datetime field >= '2013-09-11' which is implied of 12:00:00 AM, AND the datetime field is LESS THAN '2013-09-12' which allows up to 11:59:59PM on the 2013-09-11 which is the entire day AND the index can take advantage of it.