[TYPO3-performance] Improving crawler performance

[Bjoern Pedersen]

Hi,

Crawler produced lots of slow queries like:
 select count(*) from tx_crawler_queue where exec_time > 0 AND
process_id_completed = '4877e1aac4';


Adding the following key to tx_crawler_queue

	key key2 (exec_time,process_id_completed);

improves that.

before:

explain select count(*) from tx_crawler_queue where exec_time > 0 AND
process_id_completed = '4877e1aac4';
+----+-------------+------------------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys  | key  |
key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tx_crawler_queue | ALL  | exec_time,key1 | NULL |
NULL    | NULL |  435 | Using where |
+----+-------------+------------------+------+----------------+------+---------+------+------+-------------+

after:
explain select count(*) from tx_crawler_queue where exec_time > 0 AND
process_id_completed = '4877e1aac4';
+----+-------------+------------------+-------+---------------------+------+---------+------+------+--------------------------+
| id | select_type | table            | type  | possible_keys       |
key  | key_len | ref  | rows | Extra                    |
+----+-------------+------------------+-------+---------------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tx_crawler_queue | range | exec_time,key1,key2 |
key2 | 4       | NULL |  155 | Using where; Using index |
+----+-------------+------------------+-------+---------------------+------+---------+------+------+--------------------------+


Björn