MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query that returns indexed fields involves FILESORT, why?
Posted by: Rick James
Date: February 28, 2010 01:36PM

First, some background (InnoDB specific):

1. Any secondary index implicitly contains the field(s) of the PRIMARY KEY.

2. ORDER BY secondary-index (if used), will walk through the secondary index, then reach into the data via the PRIMARY KEY. This is a BTree lookup.

3. "Using index" occurs when all the fields in a SELECT are in a single secondary index. In this case, the index is read, but the data is not.

Items 1 and 3, plus
   SELECT ObjectID, ObjectName FROM objects ORDER BY ObjectName;
   PRIMARY KEY (ObjectID),
   KEY ObjectName (ObjectName),
lead to "Using index". Meanwhile, variants are denied this optimization.

SELECT * FROM objects ORDER BY ObjectName
could be implented via:
* Read through index in ObjectName order, reach (via BTree lookup) for each data row in turn. No filesort would be done, but the data lookups would be random (and potentially costly).
* Punt on the index, do a table scan, then ORDER BY (filesort).

I can't say which of those approaches would be faster. (And I doubt if the optimizer knows for sure.)

Do SHOW TABLE STATUS LIKE 'objects' -- note that there is a large amount of data. Do SHOW VARIABLES LIKE innodb_buffer_pool_size. Is it bigger than the data size? If not, every time you run the query, it will effectively reread the entire dataset. That's a lot of disk I/O for 122K rows with a LONGTEXT.

Increasing innodb_buffer_pool_size to 70% of RAM may make the query run faster (until your data again is too big).

What are you doing with 122K rows in one gulp? Do you really nead all the fields? Bypassing the LONGTEXT column should help some. Moving it to a separate table (vertical partitioning) may help more.

Can you process the rows in chunks, rather than grabbing them all at once? (In a web application, I would strongly recommend this, rather than locking out online users.)

Bottom line: It's not the filesort, per se, that is causing the performance problem; it is the bulk.

Options: ReplyQuote

Written By
Re: Query that returns indexed fields involves FILESORT, why?
February 28, 2010 01:36PM

Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.