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
