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 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.
