slow query that doesn't do anything [#1728770]

I've noticed that my slow query log is pumping out a number of these:

# Query_time: 5.853931  Lock_time: 0.000057 Rows_sent: 1  Rows_examined: 1648400
SET timestamp=1344902579;
SELECT c.cid
                FROM comments c
                WHERE c.nid = 70852 AND c.timestamp > 1342310573 AND c.status = 0
                ORDER BY c.cid LIMIT 0, 1;

It's from advanced_forum_first_new_comment(). The ORDER BY makes this a slow query, and the LIMIT means only one result ever gets returned. Dropping the ORDER BY is a no-op that speeds that query up significantly.

Comments

Comment #1

mcdruid

🇬🇧🇪🇺

CreditAttribution: mcdruid commented 14 August 2012 at 23:21

Status:

Active

» Needs work

Thanks for bringing this up funkymoses.

The ORDER BY results in MySQL doing a filesort:

mysql> EXPLAIN EXTENDED SELECT c.cid FROM comments c WHERE c.nid = 500 AND c.timestamp > 1000000 AND c.status = 0 ORDER BY c.cid LIMIT 0, 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: nid,status
          key: nid
      key_len: 4
          ref: const
         rows: 48
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.01 sec)

...and without the ORDER BY:

mysql> EXPLAIN EXTENDED SELECT c.cid FROM comments c WHERE c.nid = 500 AND c.timestamp > 1000000 AND c.status = 0 LIMIT 0, 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: nid,status
          key: nid
      key_len: 4
          ref: const
         rows: 48
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

However, I'm not certain we can rely on the the same result coming back without the ORDER BY.

I think in most cases the result will be the same, but as far as I can see the order MySQL returns the results in the absence of an explicit ORDER BY depends on the storage engine in use. I understand that MyISAM will return rows in the order they were inserted, whereas InnoDB will sort according to primary key. As it happens in this case, either would probably be okay - but it seems risky to rely on opaque implementation details like this.

I've seen PostgreSQL return rows in apparently arbitrary order, and the docs confirm this.

So, I'm not dismissing the suggestion that this could be improved - I'm very interested in improving the performance of AF - but I am not convinced that dropping the ORDER BY from this query is risk free.