I have this table that contains about 80 million rows.
CREATE TABLE `mytable` (
`date` date NOT NULL,
`parameters` mediumint(8) unsigned NOT NULL,
`num` tinyint(3) unsigned NOT NULL,
`val1` int(11) NOT NULL,
`val2` int(10) NOT NULL,
`active` tinyint(3) unsigned NOT NULL,
`ref` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ref`) USING BTREE,
KEY `parameters` (`parameters`)
) ENGINE=MyISAM AUTO_INCREMENT=79092001 DEFAULT CHARSET=latin1
it is indicated around two main columns: “parameters” and “date”. there are about 67,000 possible values for the "parameters", for each "parameter" there are about 1200 lines, each with a different date. therefore, there are 67,000 rows for each date. 1200 * 67,000 = 80,400,000.
the table size is displayed as 1.5 GB, the index size is 1.4 GB.
now, I want to query a table to retrieve all the rows from one “parameter” (actually I want to do this for each parameter, but this is a good start)
SELECT val1 FROM mytable WHERE parameters=1;
the first run gives me results in 8 seconds; subsequent runs for different but close parameter values (2, 3, 4 ...) are instant
"" ( = 1000) 8 .
20 , , , , EXPLAIN, :
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | mytable | ref | parameters | parameters | 3 | const | 1097 | |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------+
( ).
2- 2- 2,6 , Ubuntu, 4G .
key_buffer 1G mysql, .
? -, ? , .