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 | > | > | > +---+--- > +---+ > + > 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
Re: Avoiding table scans...
> > > Try this > > SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE > ArtNumber=(SELECT MIN(ArtNumber) FROM 78168ea0a9b3b513a1f2d39b559b406e > WHERE ArtNumber>2118806) > > +---+---+ | ArtNumber | MessageID | +---+---+ | 2118807 | | +---+---+ 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...
Il 24/07/14 11:57, Chris Knipe ha scritto: > On Thu, Jul 24, 2014 at 11:47 AM, Johan De Meersman > wrote: >> - Original Message - >>> From: "Chris Knipe" >>> 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 | | > +---+---+ > 1 row in set (19.37 sec) > > mysql> SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM > 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber > '2118806'; > +---+---+ > | ArtNumber | MessageID | > +---+---+ > | 2118807 | | > +---+---+ > 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 ArtNumber>2118806) -- 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...
On Thu, Jul 24, 2014 at 11:47 AM, Johan De Meersman wrote: > - Original Message - >> From: "Chris Knipe" >> 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 | | +---+---+ 1 row in set (19.37 sec) mysql> SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber > '2118806'; +---+---+ | ArtNumber | MessageID | +---+---+ | 2118807 | | +---+---+ 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...
- Original Message - > From: "Chris Knipe" > 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