gistfile1.txt

mysql> select count(*) from tickets where company_id = 6;
+----------+
| count(*) |
+----------+
|    28524 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from tickets where company_id = 6 AND parent_id = 0;
+----------+
| count(*) |
+----------+
|     7878 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from tickets where company_id = 6 AND parent_id <> 0;
+----------+
| count(*) |
+----------+
|    20646 | 
+----------+
1 row in set (0.02 sec)

mysql> select p.id FROM tickets p LEFT JOIN tickets c ON p.id = c.parent_id 
WHERE p.company_id = 6 AND p.parent_id = 0 AND c.parent_id IS NULL;
+--------+
| id     |
+--------+
| 159208 | 
| 163427 | 
+--------+
2 rows in set (0.00 sec)

gistfile2.txt

mysql> create temporary table parent SELECT * from tickets WHERE company_id = 6 AND parent_id = 0;
Query OK, 7878 rows affected (1.25 sec)
Records: 7878  Duplicates: 0  Warnings: 0

mysql> create temporary table child SELECT * FROM tickets WHERE company_id = 6 AND parent_id <> 0;
Query OK, 20646 rows affected (3.18 sec)
Records: 20646  Duplicates: 0  Warnings: 0

mysql> alter table parent add unique(id);
Query OK, 7878 rows affected (1.28 sec)
Records: 7878  Duplicates: 0  Warnings: 0

mysql> alter table child add index(parent_id);
Query OK, 20646 rows affected (3.90 sec)
Records: 20646  Duplicates: 0  Warnings: 0

mysql> select parent.id FROM parent LEFT JOIN child ON parent.id = child.parent_id 
WHERE child.parent_id IS NULL;
+--------+
| id     |
+--------+
|  55051 | 
|  56990 | 
|  56995 | 
|  72537 | 
|  72543 | 
| 159208 | 
| 163427 | 
+--------+
7 rows in set (0.09 sec)

gistfile3.txt

mysql> select id, parent_id, company_id FROM tickets where id = 55051;
+-------+-----------+------------+
| id    | parent_id | company_id |
+-------+-----------+------------+
| 55051 |         0 |          6 | 
+-------+-----------+------------+
1 row in set (0.00 sec)

mysql> select id, parent_id, company_id from tickets where parent_id = 55051;
+-------+-----------+------------+
| id    | parent_id | company_id |
+-------+-----------+------------+
| 55052 |     55051 |         -1 | 
+-------+-----------+------------+
1 row in set (0.00 sec)