Hi Rick, first of all, thanks for your ellaborated answer. I'll try to answer to your requirements
1) What are the indexes?
Dest: is the smallest of the tables. At this moment it has an index on the primary key, id. I tried creating an index on (min,max) and several other combinations, but MySQL never used them so I decided to remove them.
Dest_c: is the biggest of the tables. It has several indexes, but none of them are used. The index that I think is more relevant is (destId_A, value, points). There are two range conditions on this table, thus with the current query no index can be applied.
Act_r: has ~24K rows. The existing index is (actId, value, destId).
Act_t: has ~6K rows. The existing index is (actId, value_average, destId). This is the index used by MySQL in the query.
2) Which Engine are you using?
I'm using MyIsam. As I said, insertions on these tables are rare.
3) Is this an EAV schema?
I don't understand what you asking, sorry. There is an Entity Model we created before the schema, of course it has been modified over time. I don't think this answers your question.
We do have some big columns with text in them.
4) Show create table
sorry, but I'd rather not show this information. As I said, all the tables use MyISAM and utf8. The destId columns are smallint, which fulfills our needs.
5) Show table status
SHOW TABLE STATUS LIKE 'dest'\G;
*************************** 1. row ***************************
Name: dest
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1066
Avg_row_length: 1446
Data_length: 1541892
Max_data_length: 281474976710655
Index_length: 54272
Data_free: 0
Auto_increment: 1130
Create_time: 2011-03-26 01:47:36
Update_time: 2011-03-28 12:15:45
Check_time: 2011-03-26 01:47:37
Collation: utf8_unicode_ci
Checksum: NULL
SHOW TABLE STATUS LIKE 'dest_c'\G;
*************************** 1. row ***************************
Name: dest_c
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 1141618
Avg_row_length: 8
Data_length: 9132944
Max_data_length: 2251799813685247
Index_length: 120415232
Data_free: 0
Auto_increment: NULL
Create_time: 2011-02-14 17:20:47
Update_time: 2011-03-24 13:30:51
Check_time: 2011-03-11 11:08:34
Collation: utf8_general_ci
Checksum: NULL
SHOW TABLE STATUS LIKE 'act_r'\G;
*************************** 1. row ***************************
Name: act_r
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 24574
Avg_row_length: 22
Data_length: 540628
Max_data_length: 6192449487634431
Index_length: 721920
Data_free: 0
Auto_increment: NULL
Create_time: 2011-03-02 17:52:37
Update_time: 2011-03-28 12:15:47
Check_time: 2011-03-11 11:08:00
Collation: utf8_unicode_ci
Checksum: NULL
SHOW TABLE STATUS LIKE 'act_t'\G;
*************************** 1. row ***************************
Name: act_t
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 6432
Avg_row_length: 35
Data_length: 225120
Max_data_length: 9851624184872959
Index_length: 225280
Data_free: 0
Auto_increment: NULL
Create_time: 2011-03-02 17:52:35
Update_time: 2011-03-25 16:55:23
Check_time: 2011-03-11 11:08:00
Collation: utf8_unicode_ci
Checksum: NULL
5) EXPLAIN
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+
| 1 | SIMPLE | t_1 | range | PRIMARY,id_act_type | id_act_type | 5 | NULL | 11 | 81.82 | Using where; Using index |
| 1 | SIMPLE | t_2 | eq_ref | PRIMARY,other....... | PRIMARY | 5 | const,db.t_1.destId | 1 | 100.00 | Using where |
| 1 | SIMPLE | a_0 | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_scores_2.destId,const | 1 | 100.00 | Using where |
| 1 | SIMPLE | a_1 | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_scores_2.destId,const | 1 | 100.00 | Using where |
| 1 | SIMPLE | dest | eq_ref | PRIMARY,other....... | PRIMARY | 2 | db.a_1.destId | 1 | 100.00 | Using where |
| 1 | SIMPLE | t_0 | eq_ref | PRIMARY,other....... | PRIMARY | 5 | const,db.dest.id | 1 | 100.00 | Using where |
| 1 | SIMPLE | dc | eq_ref | PRIMARY,other....... | PRIMARY | 4 | const,db.dest.id | 1 | 100.00 | Using where |
| 1 | SIMPLE | act_r | eq_ref | PRIMARY,other....... | PRIMARY | 3 | db.t_0.destId,const | 1 | 100.00 | Using where |
+----+-------------+--------------+--------+-----------------------------------------------------------------------------+---------------+---------+--------------------------------------------------+------+----------+-------------------------------------------+
6) SHOW VARIABLES LIKE '%buffer%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 134217728 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 4194304 |
| sort_buffer_size | 33554432 |
| sql_buffer_result | OFF |
+-------------------------+-----------+