MySQL Bugs: #1583: Unusable ORDER BY performance

Bug #1583	Unusable ORDER BY performance
Submitted:	17 Oct 2003 3:10	Modified:	24 Jul 2004 9:17
Reporter:	Stewart Witchalls	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.14+	OS:	Windows (WinNT)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Statement:
  SELECT tid, tparent_id, tdate, tblob
  FROM t
  WHERE tparent_id = 2
  AND tdate <= {ts '2003-10-16 16:45:17.890'}
  ORDER BY tdate ASC
  LIMIT 100

Table is being inserted into and deleted from while select takes place (all on
different transactions).

To return an ordered list containing 241 rows has taken 30 seconds? Remove the ORDER BY and it always takes < .5 seconds.

If ORDER BY tparent_id, time < .5 seconds (where all values equal). If you order by tdate or tid (where values differ), performance degrades again.

Using SQL_SMALL_RESULT can actually make things worse!

How to repeat:
Test program adds 250 rows. As each rows is processed, new ones are added and existing ones removed. If you look at the CPU profile, it looks like a nice well spaced mountain range. Take off the ORDER BY and the CPU flat lines at 100%.

Do you have the tdate field indexed?

Table indexes created after table:

  CREATE INDEX t_tid
  ON t( tid );

  CREATE INDEX t_tdate
  ON t( tdate );

If indexing was the issue, would expect select without ORDER BY to also run slow.

Forgot to mention, table contains BLOBS. The select does not retrieve them. Is it possible that the ORDER BY is pulling in the entire row?

Hi!

Yes, I think an ORDER BY really pulls in the whole row. If you have big BLOBs, it will slow down things a lot.

Workaround: put BLOBs into a separate table and refer to it using a 'surrogate id', for example, an auto-inc value.

We have to look into the ORDER BY optimization in the future. MySQL should not retrieve columns it does not really use in the calculation of the query results.

Best regards,

Heikki

Do you a time frame for that? The tables work fine on Oracle and MSSQL. I don't really want to implement a MySQL bodge.

Could you please provide us with the following ingormation:
- EXPLAIN for this query with ORDER BY
- EXPLAIN for query without ORDER BY
- select count(*) from table
- show create table 
- EXPLAIN for query with order by but without limit
Thank you for your feedback.

No feedback was provided on this bug.
Feel free to reopen it if you have new informaiton.