Getting Count(*) vs Actual Data has performance implications

Question

I can't make sense of the following two queries. First one only gets the count of the entire resultset.

Second one gets the actual data , but limit the resultset to 10 rows.

Somehow the first one can't use index. I have tried to use USE INDEX (timestamp_index,Fulltext_title,Fulltext_description) with no avail.
The count query does not need an order by , but I just tried to see if it can use index that way.
As far as I can see the WHERE clause is the same, which to my knowledge is the biggest factor in selecting the index.

GET THE COUNT

SELECT count(*) as total FROM table1 
WHERE 1=1
AND type in ('category1','category3','category2') 
AND ( 
    MATCH(title) AGAINST (' +"apple"' IN BOOLEAN MODE)
    OR 
    MATCH(description) AGAINST (' +"apple"' IN BOOLEAN MODE) 
    )
ORDER BY timestamp DESC
;
+-------+
| total |
+-------+
|   798 |
+-------+
1 row in set (3.75 sec)

EXPLAIN EXTENDED

+----+-------------+----------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref      | rows   | filtered | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | table1   | ALL  | NULL          | NULL | NULL    | NULL | 669689 |   100.00 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--------+----------+-------------+

Get the Actual Result

SELECT id, title,desciption,timestamp  FROM table1
WHERE 1=1
AND type in ('category1','category3','category2') 
AND ( 
    MATCH(title) AGAINST (' +"apple"' IN BOOLEAN MODE)
    OR 
    MATCH(description) AGAINST (' +"apple"' IN BOOLEAN MODE) 
    )
ORDER BY timestamp DESC
LIMIT 0, 10 ;

10 rows in set (0.06 sec)

EXPLAIN EXTENDED

+----+-------------+----------+-------+---------------+------+---------+------+------+------------+-------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref      | rows | filtered   | Extra       |
+----+-------------+----------+-------+---------------+------+---------+----   --+------+------------+-------------+
|  1 | SIMPLE      | table1 index | NULL          | timestamp_index   | 21          | NULL |   10 | 6696890.00 | Using where |
+----+-------------+----------+-------+---------------+------+---------+------+------+------------+-------------+

`limit` has very strong influence on optimizers, I'm not sure where you're going with this question - the two queries are completely different. — Mat, Mar 09 '16 at 20:05
The limit will make it run a great deal faster. The Count(*) has to do a full table scan, especially if it's run for the first time. It may increase performance on subsequent calls. — ManoDestra, Mar 09 '16 at 20:07
ok ... let me rephrase .. How can I make the first query use an index .. perhaps like the one using in the second query. — Scalable, Mar 09 '16 at 20:14

score 1 · Answer 1 · answered Mar 09 '16 at 20:21

On second query. You want first 10 elements. So optimizer use timestamp index, sort the table and keep checking rows until found 10 element matching your WHERE

On your first query, the db has to scan the whole db to find what element match your query, so your ORDER BY doesnt help because you want count the total number of rows matching yor where.

Now also depend on how you define your index. Do you have one Index for Type, Title and Description ? Do you have compisite Index?

Check this one MySQL index TIPS

score 0 · Accepted Answer · answered Mar 09 '16 at 20:28

I found the answer ... I combined the two indexes. So we don't have to have a full table scan just because we are doing a count(*)

SELECT count(*) as total FROM table1 WHERE 1=1
    AND type in ('category1','category2','category3') 
    AND MATCH(title, description) AGAINST (' +"apple"' IN BOOLEAN MODE)  
;

+----+-------------+----------+----------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table    | type     | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+----------+----------------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | table1   | fulltext | FT_title_description | FT_title_description | 0       | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+----------+----------------------+----------------------+---------+------+------+----------+-------------+
+-------+
| total |
+-------+
|   798 |
+-------+
1 row in set (0.83 sec)

Getting Count(*) vs Actual Data has performance implications

GET THE COUNT

EXPLAIN EXTENDED

Get the Actual Result

EXPLAIN EXTENDED

2 Answers2