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 |
+----+-------------+----------+-------+---------------+------+---------+------+------+------------+-------------+

Answer 1

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)

Answer 2

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