Avoiding table scans...
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:
Re: Avoiding table scans...
- Original Message - From: Chris Knipe sav...@savage.za.org To: mysql@lists.mysql.com Sent: Thursday, 24 July, 2014 11:17:50 AM Subject: Avoiding table scans... mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; You're putting quotes around ArtNumber in your where clause, where it really is a bigint. Thus, you're forcing implicity conversion in the parser, instead of simply doing an index lookup. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Avoiding table scans...
On Thu, Jul 24, 2014 at 11:47 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Chris Knipe sav...@savage.za.org To: mysql@lists.mysql.com Sent: Thursday, 24 July, 2014 11:17:50 AM Subject: Avoiding table scans... mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; You're putting quotes around ArtNumber in your where clause, where it really is a bigint. Thus, you're forcing implicity conversion in the parser, instead of simply doing an index lookup. 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 (19.37 sec) 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 (19.43 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 | 31868953 | Using where | ++-+--+---+---+-+-+--+--+-+ 1 row in set (0.18 sec) Partitioning should help from what I am reading currently... Just not sure about a few things just yet. -- Regards, Chris Knipe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Avoiding table scans...
Il 24/07/14 11:57, Chris Knipe ha scritto: On Thu, Jul 24, 2014 at 11:47 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Chris Knipe sav...@savage.za.org To: mysql@lists.mysql.com Sent: Thursday, 24 July, 2014 11:17:50 AM Subject: Avoiding table scans... mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; You're putting quotes around ArtNumber in your where clause, where it really is a bigint. Thus, you're forcing implicity conversion in the parser, instead of simply doing an index lookup. 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 (19.37 sec) 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 (19.43 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 | 31868953 | Using where | ++-+--+---+---+-+-+--+--+-+ 1 row in set (0.18 sec) Partitioning should help from what I am reading currently... Just not sure about a few things just yet. Try this SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber=(SELECT MIN(ArtNumber) FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber2118806) -- INTER CORRADO PANDIANI Web Project Manager / DBA F.C. Internazionale Milano S.p.A. C.so Vittorio Emanuele II, 9 20122 Milano, Italy TEL +39 027715 551 www.inter.it IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.
Re: Avoiding table scans...
Try this SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber=(SELECT MIN(ArtNumber) FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber2118806) +---+---+ | ArtNumber | MessageID | +---+---+ | 2118807 | part26of79.GfYzwhqz$ORUpNi3tjsW@camelsystem-powerpost.local | +---+---+ 1 row in set (0.81 sec) If this wasn't a public mailing list I would have said something very strange now containing the word love! Can you perhaps just elaborate a bit as to *why* this is working so much better? The reason I ask is that I've only given one query as a example. There's quite a few slightly different queries like this, and I would just like to understand the mythology behind in order to be able to change the other queries as well... MANY thanks for the assistance :-) -- Chris.
RE: Avoiding table scans...
Hi Chris, -Original Message- From: Chris Knipe [mailto:sav...@savage.za.org] Sent: Thursday, 24 July 2014 19:18 To: mysql@lists.mysql.com Subject: Avoiding table scans... 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) In addition to being slow, the query is probably not what you want. What the query does is finding the minimum ArtNumber greater than 2118806, then is free to choose any MessageID among those rows matching the WHERE clause. This is also why the query has to examine so many rows. If you look at the query with MAX(ArtNumber) ... ArtNumber ..., then it is more likely that you'll see an unexpected result. Using your table definition and inserting random rows: mysql SELECT MAX(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806; +---+---+ | ArtNumber | MessageID | +---+---+ | 28805 | sutlers | +---+---+ 1 row in set (0.12 sec) mysql SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber = 28805; +---++ | ArtNumber | MessageID | +---++ | 28805 | pearl-bordered | +---++ 1 row in set (0.00 sec) So the MessageID returned for your original query is not the one corresponding to the ArtNumber. If you set sql_mode to include ONLY_FULL_GROUP_BY, you can also see that the query is invalid: mysql SET sql_mode = 'ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause One rewrite is the one suggested by Corrado - first find the ArtNumber, then retrieve the corresponding row. An alternative is to use ORDER BY ArtNumber ASC|DESC LIMIT 1, i.e.: mysql SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806 ORDER BY ArtNumber ASC LIMIT 1; +---+-+ | ArtNumber | MessageID | +---+-+ | 28807 | groundworks | +---+-+ 1 row in set (0.00 sec) mysql SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806 ORDER BY ArtNumber DESC LIMIT 1; +---++ | ArtNumber | MessageID | +---++ | 28805 | pearl-bordered | +---++ 1 row in set (0.00 sec) Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
