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
