Hi All, I have a couple of *huge* tables, they're still busy populating, but once done I suspect it will hold well over 3 billion records (and that's more than likely the start of the problem).
The mysql server is a highly optimized, powerful server with some 128GB ram, data + binlogs on RAID10 SSDs and is performing incredibly well with some 3K inserts/second whilst still doing random selects/updates in between. How can I go about not having the following: mysql> SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber > '2118806'; +-----------+--------------------------------------------------------------- + | ArtNumber | MessageID | +-----------+--------------------------------------------------------------- + | 2118807 | <part26of79.GfYzwhqz$ORUpNi3tjsW@camelsystem-powerpost.local> | +-----------+--------------------------------------------------------------- + 1 row in set (22.78 sec) mysql> EXPLAIN SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber > '2118806'; +----+-------------+----------------------------------+-------+------------- --+---------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------------------+-------+------------- --+---------+---------+------+----------+-------------+ | 1 | SIMPLE | 78168ea0a9b3b513a1f2d39b559b406e | range | PRIMARY | PRIMARY | 8 | NULL | 31515172 | Using where | +----+-------------+----------------------------------+-------+------------- --+---------+---------+------+----------+-------------+ 1 row in set (0.19 sec) mysql> explain SELECT MAX(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber < '2118809'; +----+-------------+----------------------------------+-------+------------- --+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------------------+-------+------------- --+---------+---------+------+------+-------------+ | 1 | SIMPLE | 78168ea0a9b3b513a1f2d39b559b406e | range | PRIMARY | PRIMARY | 8 | NULL | 3 | Using where | +----+-------------+----------------------------------+-------+------------- --+---------+---------+------+------+-------------+ 1 row in set (0.17 sec) mysql> SHOW CREATE TABLE 78168ea0a9b3b513a1f2d39b559b406e; +----------------------------------+---------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------+ | Table | Create Table | +----------------------------------+---------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------+ | 78168ea0a9b3b513a1f2d39b559b406e | CREATE TABLE `78168ea0a9b3b513a1f2d39b559b406e` ( `ArtNumber` bigint(20) unsigned NOT NULL, `MessageID` varchar(255) NOT NULL, `Date` int(10) unsigned NOT NULL, `Bytes` bigint(20) unsigned NOT NULL, `Lines` bigint(20) unsigned NOT NULL, `From` varchar(255) NOT NULL, `Subject` tinytext NOT NULL, PRIMARY KEY (`ArtNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='' | +----------------------------------+---------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------+ 1 row in set (0.70 sec) I realise that this is an enormous amount of data - especially once fully populated and we reach the over 3 billion records in the table. Is the only course of action here to re-look at how the data is stored? I suppose it can't get any better than querying directly against the PRIMARY key, can it? Many thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
