Hi guys! Ok, I'm closer to beat Rushmore (VFP optimizer) now! After some reading about MySQL optimization techniques, here is the summary of what I've done: 1. Add a compound index to the table 2. Use EXPLAIN to check out the query (with GROUP BY on multiple fields) 3. Create the summary table
And here's the detailed instructions:
mysql> alter table traf_oper add index (tel, telefb, rutaentran, rutasalien, minutos);
Query OK, 5067215 rows affected (5 min 22.36 sec)
Records: 5067215 Duplicates: 0 Warnings: 0
mysql> explain select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4;
+----+-------------+-----------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | traf_oper | index | NULL | tel | 45 | NULL | 5067215 | Using index |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.03 sec)
mysql> create table grp_oper select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by
1, 2, 3, 4;
Query OK, 3326541 rows affected (33.81 sec)
Records: 3326541 Duplicates: 0 Warnings: 0
Adding the times together it would take MySQL like 6 minutes (VFP does it in about 4 minutes).
I still haven't tweaked some server variables (read_rnd_buffer_size, sort_buffer_size, max_join_size),
but, as always, I'll keep trying :)
Thanks in advance for your comments and suggestions,
Hector
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
