Re: INDEX DESC

Michael Stassen Thu, 24 Jun 2004 09:09:14 -0700

Jochem van Dieten wrote:

Michael Stassen wrote:
SELECT init FROM inits GROUP BY init ORDER BY init;
+------+
| init |
+------+
| A    |
| B    |
| C    |
...
| X    |
| Y    |
| Z    |
+------+
26 rows in set (0.39 sec)
SELECT init FROM inits GROUP BY init ORDER BY init DESC;
+------+
| init |
+------+
| Z    |
| Y    |
| X    |
...
| C    |
| B    |
| A    |
+------+
26 rows in set (4.09 sec)
This is with mysql 4.0.20. As you can see, SHOW INDEX says the index on init is not packed, but DESC ordering takes 10 times as long. Explain, in case you are wondering, indicates the index on init is being used in both cases.
Does sorting really take 10 times as long? I find it hard to believe that sorting 26 rows takes 4 seconds no matter how random and unindexed the input is. Might it be that grouping take 10 times as long? (An EXPLAIN on par with the EXPLAIN ANALYZE from PostgreSQL would be nice to have here.)

Jochem

I should probably have noted that this was with default settings on a 5 year old iMac G3 running Mac OS X 10.3.4, just to put the times in perspective, but the point is the relative difference, rather than the absolute time. And I agree, sorting 26 rows just shouldn't take that long. If I CREATE...SELECT into a temp table with the ASC query and then select from the temp table in DESC order, the latter returns in no time. Nor should grouping take that long, I think, if we truly can traverse the index backwards as efficiently as forwards.

I played with this a little more. First, for completeness, the EXPLAIN results for the 2 queries posted previously:

mysql> EXPLAIN SELECT init FROM inits GROUP BY init ORDER BY init ASC; +-------+-------+---------------+----------+---------+------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+----------+---------+------+-------+-------------+ | inits | index | NULL | init_idx | 2 | NULL | 50000 | Using index | +-------+-------+---------------+----------+---------+------+-------+-------------+ 1 row in set (0.01 sec)

mysql> EXPLAIN SELECT init FROM inits GROUP BY init ORDER BY init DESC; +-------+-------+---------------+----------+---------+------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+----------+---------+------+-------+-------------+ | inits | index | NULL | init_idx | 2 | NULL | 50000 | Using index | +-------+-------+---------------+----------+---------+------+-------+-------------+ 1 row in set (0.00 sec)

Here's what I get without the GROUP BY:

SELECT init FROM inits ORDER BY init ASC;
+------+
| A    |
...
| Z    |
+------+
50000 rows in set (1.51 sec)

SELECT init FROM inits ORDER BY init DESC;
+------+
| Z    |
...
| A    |
+------+
50000 rows in set (5.19 sec)

Now notice what happpens when I drop the index:

mysql> ALTER TABLE inits DROP INDEX init_idx;
Query OK, 50000 rows affected (1.20 sec)
Records: 50000  Duplicates: 0  Warnings: 0

mysql> SELECT init FROM inits GROUP BY init ORDER BY init ASC;
+------+
| init |
+------+
| A    |
| B    |
| C    |
...
| X    |
| Y    |
| Z    |
+------+
26 rows in set (0.33 sec)

mysql> SELECT init FROM inits GROUP BY init ORDER BY init DESC;
+------+
| init |
+------+
| Z    |
| Y    |
| X    |
...
| C    |
| B    |
| A    |
+------+
26 rows in set (0.32 sec)

Clearly, the index seems to be getting in the way in the DESC case. Here it is without the GROUP BY:

SELECT init FROM inits ORDER BY init ASC;
+------+
| A    |
...
| Z    |
+------+
50000 rows in set (2.44 sec)

SELECT init FROM inits ORDER BY init DESC;
+------+
| Z    |
...
| A    |
+------+
50000 rows in set (2.45 sec)

As expected, it takes longer without the index, but the same time for ASC and DESC. Again, the index seems to be getting in the way in the DESC case.

Of course, this is only one case. But it does seem to me that the picture must be a little more complicated than simply ASC and DESC are the same unless your index is packed. It would be nice to have a complete picture.

Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: INDEX DESC

Reply via email to