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
Jul 24
Johan De Meersman Re: Avoiding table scans...
Jul 24, 2014; 11:47
Johan De Meersman
Re: Avoiding table scans...
Jul 24
Chris Knipe Re: Avoiding table scans...
Jul 24, 2014; 11:57
Chris Knipe
Re: Avoiding table scans...
Jul 24
Corrado Pandiani Re: Avoiding table scans...
Jul 24, 2014; 12:08
Corrado Pandiani
Re: Avoiding table scans...
Jul 24
Chris Knipe Re: Avoiding table scans...
Jul 24, 2014; 12:16
Chris Knipe
Re: Avoiding table scans...
Jul 24
Jesper Wisborg Krogh RE: Avoiding table scans...
Jul 24, 2014; 20:52
Jesper Wisborg Krogh
RE: Avoiding table scans...
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!