Public feed
#725336
mysql> explain SELECT SUM(bytes/1000000) AS bytes FROM user_data WHERE uid='1740
3' AND timestamp BETWEEN '1189348200' AND '1191940199';
+----+-------------+-----------+-------+---------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | user_data | range | uidtime,idx_uid,idx_uiddaymonth | uidtime | 8 | NULL | 2328 | Using where |
+----+-------------+-----------+-------+---------------------------------+---------+---------+------+------+-------------+
1 row in set (4.81 sec)
mysql> SELECT SUM(bytes/1000000) AS bytes FROM user_data WHERE uid='17403' AND timestamp BETWEEN '1189348200' AND '1191940199';
+---------+
| bytes |
+---------+
| 4404.01 |
+---------+
1 row in set (16.92 sec)
mysql> show create table user_data;
CREATE TABLE `user_data` (
`id` int(255) unsigned NOT NULL auto_increment,
`uid` int(255) unsigned NOT NULL default '0',
`ip` varchar(16) NOT NULL default '',
`DAY` int(2) unsigned NOT NULL default '0',
`MONTH` int(2) unsigned NOT NULL default '0',
`year` int(4) unsigned NOT NULL default '0',
`bytes` bigint(255) unsigned NOT NULL default '0',
`bytes_local` bigint(20) unsigned NOT NULL default '0',
`timestamp` int(255) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `uidtime` (`uid`,`timestamp`),
KEY `idx_uid` (`uid`),
KEY `idx_uiddaymonth` (`uid`,`DAY`,`MONTH`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=300000000
whilst running the select
data# iostat 2
tty aacd0 fd0 md0 cpu
tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy in id
0 44 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 7 2 5 0 87
0 77 16.00 125 1.95 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100
0 77 16.00 128 2.00 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100
0 77 16.00 138 2.15 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100
0 77 16.00 126 1.96 0.00 0 0.00 0.00 0 0.00 0 0 0 1 99
0 76 16.00 132 2.06 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100
0 77 16.00 120 1.87 0.00 0 0.00 0.00 0 0.00 0 0 1 0 99
0 176 16.00 130 2.03 0.00 0 0.00 0.00 0 0.00 1 0 2 0 98
0 76 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100
0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100
0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100
^C
mysql> show table status like 'user_data'\G
*************************** 1. row ***************************
Name: user_data
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 85393210
Avg_row_length: 51
Data_length: 4411811788
Max_data_length: 1099511627775
Index_length: 4213731328
Data_free: 14313116
Auto_increment: 293588813
Create_time: 2007-09-23 12:28:00
Update_time: 2007-10-04 19:11:18
Check_time: 2007-10-04 15:52:57
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=300000000
Comment:
1 row in set (0.02 sec)