Re: Slow simple query in 4000000 records table

[Matias Burak]

"mysql> SHOW CREATE TABLE tipete_home\G;
*************************** 1. row ***************************
Table: tipete_home
Create Table: CREATE TABLE `tipete_home` (
`node_id` int(11) NOT NULL,
`tid` int(11) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`titulo` varchar(255) NOT NULL,
`url_alias` varchar(255) NOT NULL,
`url_img_tax` varchar(255) DEFAULT NULL,
`url_path_tax` varchar(255) DEFAULT NULL,
`datetime` int(11) DEFAULT NULL,
`sticky` tinyint(4) DEFAULT NULL,
`private` tinyint(4) DEFAULT NULL,
`promoted` tinyint(4) DEFAULT NULL,
`status` tinyint(4) DEFAULT NULL,
`datetime_reverse` int(11) DEFAULT NULL,
PRIMARY KEY (`node_id`),
KEY `datetime_id` (`datetime`),
KEY `datetime_reverse` (`datetime_reverse`),
KEY `datetime_r_nid` (`datetime_reverse`,`node_id`),
KEY `tid_datetime_r_nid` (`tid`,`datetime_reverse`,`node_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)"

and

"mysql> SHOW TABLE STATUS LIKE 'tipete_home'\G
*************************** 1. row ***************************
Name: tipete_home
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 4208482
Avg_row_length: 233
Data_length: 984083920
Max_data_length: 281474976710655
Index_length: 422362112
Data_free: 0
Auto_increment: NULL
Create_time: 2010-07-15 16:32:59
Update_time: 2010-11-29 10:05:55
Check_time: 2010-07-15 16:33:21
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)"

and

"mysql> EXPLAIN SELECT * FROM tipete_home LIMIT 3450000,50 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tipete_home
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4208497
Extra:
1 row in set (0.01 sec)"

and

"mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 650117120 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 314572800 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 16777216 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
12 rows in set (0.00 sec)"


I'll send the aditional data soon.