Re: Need help diagnosing a slow query

[Rusty K]

A bit more info that may be helpful:



mysql> SHOW TABLE STATUS LIKE 'names'\G
*************************** 1. row ***************************
Name: names
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 42110172
Avg_row_length: 140
Data_length: 5901969832
Max_data_length: 281474976710655
Index_length: 4713729024
Data_free: 0
Auto_increment: NULL
Create_time: 2011-03-20 11:24:13
Update_time: 2011-03-31 20:58:46
Check_time: 2011-03-20 13:32:23
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'purchases'\G
*************************** 1. row ***************************
Name: purchases
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 50139402
Avg_row_length: 28
Data_length: 1423985464
Max_data_length: 281474976710655
Index_length: 3411631104
Data_free: 0
Auto_increment: NULL
Create_time: 2011-02-16 17:25:36
Update_time: 2011-03-31 21:01:09
Check_time: 2011-02-16 17:25:43
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> 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 | 536870912 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 8388608 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
12 rows in set (0.11 sec)


mysql> EXPLAIN SELECT names.*, purchases.purchase_info FROM names LEFT JOIN purchases ON names.name_id = purchases.name_id WHERE lastname like 'BRO%' AND firstname like 'JOH%' LIMIT 501;

+----+-------------+------------+-------+--------------------+----------+---------+------------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------+----------+---------+------------------------------------+--------+-------------+
| 1 | SIMPLE | names | range | firstname,lastname | lastname | 31 | NULL | 311554 | Using where |
| 1 | SIMPLE | purchases | ref | PRIMARY,name_id | name_id | 18 | mydatabase.names.name_id | 1 | |
+----+-------------+------------+-------+--------------------+----------+---------+------------------------------------+--------+-------------+
2 rows in set (0.12 sec)