Quando os registros gerados pelo mysar na base mysql ultrapassam 1.000,000 registros e expira as queries alguns comandos úteis para gerar indices para as tabelas:
Database changed
mysql> EXPLAIN EXTENDED SELECT COUNT(DISTINCTROW ip) AS users FROM traffic WHERE date='2011-12-15' AND time>'09:17:08';
+----+-------------+---------+-------+--------------------------------------+--------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+-------+--------------------------------------+--------------+---------+------+---------+----------+--------------------------+
| 1 | SIMPLE | traffic | range | date_ip_sitesID_usersID,traffic_idx1 | traffic_idx1 | 6 | NULL | 1881673 | 100.00 | Using where; Using index |
+----+-------------+---------+-------+--------------------------------------+--------------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> CREATE INDEX traffic_idx1 ON traffic (date, time, ip);
Nenhum comentário:
Postar um comentário