gistfile1.sql

SELECT COUNT( DISTINCT t.id ) AS num_results
FROM tickets t
LEFT JOIN companies c ON t.company_id = c.id
WHERE t.parent_id =0
AND t.company_id =6
AND (
  t.ticket_status NOT
  IN ( 3, 4, 7 )
)
GROUP BY t.id

gistfile2.txt

+----+-------------+-------+-------+-----------------------------------------------+-------------+---------+-------------+------+-----------------------------+
| id | select_type | table | type  | possible_keys                                 | key         | key_len | ref         | rows | Extra                       |
+----+-------------+-------+-------+-----------------------------------------------+-------------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | t     | ref   | statusIDX,backlog_idx,cid_id_status,parentIDX | backlog_idx | 8       | const,const | 3756 | Using where; Using filesort | 
|  1 | SIMPLE      | c     | const | PRIMARY                                       | PRIMARY     | 4       | const       |    1 | Using index                 | 
+----+-------------+-------+-------+-----------------------------------------------+-------------+---------+-------------+------+-----------------------------+
2 rows in set (0.00 sec)

gistfile3.txt

mysql> show index from tickets;
+---------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+
| tickets |          0 | PRIMARY         |            1 | id                      | A         |      130359 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | ticket_FKIndex1 |            1 | resolved_by_id          | A         |          38 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | ticket_FKIndex2 |            1 | owner_id                | A         |        1515 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | ticket_FKIndex3 |            1 | problemtype_id          | A         |          82 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | ticket_FKIndex4 |            1 | problemtype_detailed_id | A         |           4 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | ticket_FKIndex5 |            1 | department_id           | A         |        2287 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | createdIDX      |            1 | created                 | A         |      130359 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | resolvedIDX     |            1 | resolved                | A         |      130359 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | acreatedIDX     |            1 | acreated                | A         |        2896 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | aresolvedIDX    |            1 | aresolved               | A         |        2607 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | statusIDX       |            1 | ticket_status           | A         |          32 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | shop_id         |            1 | shop_id                 | A         |         147 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | shop_id         |            2 | ticket_status           | A         |         240 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | backlog_idx     |            1 | company_id              | A         |           8 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | backlog_idx     |            2 | parent_id               | A         |           8 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | backlog_idx     |            3 | created                 | A         |      130359 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | cid_id_status   |            1 | company_id              | A         |           4 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | cid_id_status   |            2 | id                      | A         |      130359 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | cid_id_status   |            3 | ticket_status           | A         |      130359 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | parentIDX       |            1 | parent_id               | A         |           4 |     NULL | NULL   |      | BTREE      |         | 
| tickets |          1 | parentIDX       |            2 | id                      | A         |      130359 |     NULL | NULL   |      | BTREE      |         | 
+---------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+
21 rows in set (0.48 sec)