Mysql - where clause slower than full scan

Query A

Select id from jobs;

| 55966 |
| 55971 |
+-------+
10705 rows in set (0.00 sec)

Query B

Select id from jobs where status = 0;
| 55966 |
| 55971 |
+-------+
7933 rows in set (**20.22 sec**)

There IS an index on status.

mysql> explain select id from jobs where status = 0;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | status        | NULL | NULL    | NULL | 10705 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)


mysql> show profile for query 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000023 |
| checking query cache for query |  0.000039 |
| checking permissions           |  0.000006 |
| Opening tables                 |  0.000008 |
| System lock                    |  0.000004 |
| Table lock                     |  0.000016 |
| init                           |  0.000021 |
| optimizing                     |  0.000007 |
| statistics                     |  0.000904 |
| preparing                      |  0.000023 |
| executing                      |  0.000003 |
| Sending data                   | 19.751547 |
| end                            |  0.000009 |
| query end                      |  0.000002 |
| freeing items                  |  0.001561 |
| storing result in query cache  |  0.000122 |
| logging slow query             |  0.000002 |
| logging slow query             |  0.000002 |
| cleaning up                    |  0.000003 |
+--------------------------------+-----------+

mysql> show index from jobs;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---开发者_Go百科------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| jobs  |          1 | status   |            1 | status      | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
| jobs  |          1 | date     |            1 | dateinit    | A         |        1784 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.02 sec)

I dont understand why Query B takes 20 seconds while Query A takes 0 . Index on "status". Same result on prod and dev. server.

Try changing the index to both columns (status, id)

As I see it, you don't have any index on id

The ANSI standard (look for "Scalar expressions 125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start.

If COUNT(*) is specified, then the result is the cardinality of T.

This is why COUNT(*) is far quicker then COUNT(id). COUNT(*) can use the status index. COUNT(id) won't use this index and there is no other valid index

What is the clustered index, and what is the PK? You have both (even if the same index), right?

0.00 seconds sounds like the query was probably cached. That being said, a query for the id of a table can be answered straight from the primary key index (i.e, without looking at the table data at all!), while a query with a WHERE clause requires the database to actually read the rows.

There is an index on status, but once MySQL had determined which rows have status = 0, there is a call, for each row, to find its id. If you create index idx_jobs_status_id on jobs (status, id); I predict that 20 seconds will go way down.