List:General Discussion« Previous MessageNext Message »
From:Jesper Wisborg Krogh Date:July 24 2014 10:52am
Subject:RE: Avoiding table scans...
View as plain text  
Hi Chris,

> -----Original Message-----
> From: Chris Knipe [mailto:savage@stripped]
> Sent: Thursday, 24 July 2014 19:18
> To: mysql@stripped
> Subject: Avoiding table scans...
> 
> mysql> SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM
> 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber > '2118806';
> +-----------+-----------------------------------------------------------
> +-----------+----
> +
> | ArtNumber | MessageID
> |
> +-----------+-----------------------------------------------------------
> +-----------+----
> +
> |   2118807 |
> | <part26of79.GfYzwhqz$ORUpNi3tjsW@stripped>
> |
> +-----------+-----------------------------------------------------------
> +-----------+----
> +
> 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 


Thread
Avoiding table scans...Chris Knipe24 Jul 2014
  • Re: Avoiding table scans...Johan De Meersman24 Jul 2014
    • Re: Avoiding table scans...Chris Knipe24 Jul 2014
      • Re: Avoiding table scans...Corrado Pandiani24 Jul 2014
        • Re: Avoiding table scans...Chris Knipe24 Jul 2014
  • RE: Avoiding table scans...Jesper Wisborg Krogh24 Jul 2014