MySQL – Why SELECT * Takes Longer Than SELECT

MySQLperformancequery-performance

I'm having a problem with a query running on a little PHP program.
When I use * to select all the columns, it takes way more to return the result than when I use just one column.

The query that is running is this:

mysql> SELECT cupom_fiscal_id FROM cupom_fiscal WHERE send_date IS NULL AND travado IS 
FALSE AND status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-
05-05' LIMIT 1;

In this case it returned:

1 row in set (0.00 sec)

But when I replace the column with '*':

mysql> SELECT * FROM cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE AND 
status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05' 
LIMIT 1;

1 row in set (1.37 sec)

The funny thing is the result of the query below:

mysql> SELECT * FROM cupom_fiscal WHERE cupom_fiscal_id = (SELECT cupom_fiscal_id FROM 
cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE AND status = 0 AND ong_id = 
1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05' LIMIT 1);

1 row in set (0.00 sec)

What I'm doing wrong?

PS: Sorry for the bad english.

EDIT 1: Posting the 'EXPLAIN' and the 'PROFILING' requested by: @ValerieParham-Thompson

mysql> EXPLAIN SELECT * FROM cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE 
AND status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05' 
LIMIT 1;
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
| id | select_type | table        | type        | possible_keys                                                                  | key                | key_len | ref  | rows | Extra                                            |
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | cupom_fiscal | index_merge | fkey_id_ong,status,ong_id,data_emissao,send_date,ong_id_2,ong_id_3,send_date_2 | status,fkey_id_ong | 4,4     | NULL | 3712 | Using intersect(status,fkey_id_ong); Using where |
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)

Profiling result:

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000121 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000030 |
| init                 | 0.000054 |
| System lock          | 0.000018 |
| optimizing           | 0.000027 |
| statistics           | 0.000282 |
| preparing            | 0.000041 |
| executing            | 0.000014 |
| Sending data         | 0.627346 |
| end                  | 0.000027 |
| query end            | 0.000018 |
| closing tables       | 0.000021 |
| freeing items        | 0.000081 |
| cleaning up          | 0.000012 |
+----------------------+----------+
15 rows in set, 1 warning (0.04 sec)

EDIT 2: I think I got it!
I just created an index with the the same fields and in the same order that they appear in the WHERE.

mysql> ALTER TABLE cupom_fiscal ADD INDEX `process` (`send_date`, `travado`, `status`, `ong_id`, `data_emissao`);

I really don't know if that makes any sense, but it solved my problem. The same query that took more than 1 second to return the results now takes < 7ms.

Best Answer

Your query:

SELECT * 
FROM cupom_fiscal 
WHERE send_date IS NULL 
  AND travado IS FALSE 
  AND status = 0 
  AND ong_id = 1618 
  AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05' 
LIMIT 1 ;

involves only one table and 5 of its columns in the WHERE clause. Four conditions are equality (=) and the 5th is a range condition (BETWEEN .. AND).

(parenthesis)

The query is not very efficient as it uses the index_merge algorithm, i.e. it uses more than one index (actually two: status and fkey_id_ong) and then "merges" the results using the index_merge/intersect algorithm (Read: Using intersect(status,fkey_id_ong);.) After that operation, it has to also check the actual rows of the table (because, I guess, these two indexes do not include all the 5 columns of the WHERE clause.) This also has a negative effect on efficiency.

The best index for this query would be a 5-column index where the 5th column in data_emissao (the column with the range condition.) The order of the first 4 columns in the index would not matter.

It may matter for other queries but not for this one. So, choose an order that would help other queries as well (if for example you have many queries that use ong_id, use that as the 1st column of the index.

Related Question