# MySQL Indexing VarChar

I am trying to index my blogentries database for better performance but found an issue.

Here is the structure:

CREATE TABLE IF NOT EXISTS blogentries (
id_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
title_id varchar(100) COLLATE latin1_german2_ci NOT NULL,
entry_id varchar(5000) COLLATE latin1_german2_ci NOT NULL,
date_id int(11) NOT NULL,
PRIMARY KEY (id_id)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1
COLLATE=latin1_german2_ci
AUTO_INCREMENT=271;


A query like the following uses the index properly:

EXPLAIN SELECT id_id,title_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type |    table    | type  | possible_keys |   key   | key_len | ref  | rows |  Extra      |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | blogentries | index | NULL          | PRIMARY |     114 | NULL |  126 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+


However, when I add the entry_id into the SELECT query it uses the filesort

EXPLAIN SELECT id_id,title_id,entry_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| id | select_type |    table    | type | possible_keys | key  | key_len | ref  | rows |   Extra        |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | blogentries | ALL  | NULL          | NULL | NULL    | NULL |  126 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+


I was wondering why this is happening and how I can avoid it? Is it due to the VarChar, and that should be changed to something else?

I am trying to have all my queries use the index as I'm running into high Handler_read_rnd and Handler_read_rnd_next values.

If you need any other info I can post it too.

## migrated from stackoverflow.comOct 28 '12 at 9:13

This question came from our site for professional and enthusiast programmers.

• filesort means that it's performing the sort on the disk. – Kermit Oct 12 '12 at 18:55
• Try adding WHERE 1=1 to your second query. – Kermit Oct 12 '12 at 19:01
• Which version of MySQL is this? What is your sort buffer size (SELECT @@sort_buffer_size)? – james_t Oct 17 '12 at 20:26
• @njk filesort is a result of the 'ORDER BY' part of the query – Tash Pemhiwa Oct 23 '12 at 9:58
• @TashPemhiwa Not necessarily, see the first statement. – Kermit Oct 23 '12 at 14:45

Since you don't have a WHERE clause in either query, you're returning all rows in both cases, so I'd think the use or non-use of the index would have very little impact on performance in these examples.

• Surely MySQL should use the index for the ORDER BY? – eggyal Oct 12 '12 at 18:56
• @eggyal Not if it's too large for memory. – Kermit Oct 12 '12 at 18:57
• @njk: That doesn't make sense... it can traverse the index, in order, without needing to load the whole thing into memory. Results would be sorted without needing to perform filesort. – eggyal Oct 12 '12 at 18:58
• @eggyal I would question the size of varchar(5000). – Kermit Oct 12 '12 at 19:00
• @njk: But that column is neither in the index nor being used in the sort. – eggyal Oct 12 '12 at 19:01

As documented under ORDER BY Optimization:

For slow queries for which filesort is not used, try lowering max_length_for_sort_data to a value that is appropriate to trigger a filesort.

In his blog article What exactly is read_rnd_buffer_size, Peter Zaitsev explains:

For me this means since MySQL 4.1 this option is used in narrow range of cases – if you retrieve few fields (less than max_length_for_sort_data) data should be stored in sort buffer and sort file so there would be no need for read_rnd_buffer, if the selected columns are long so they are longer than max_length_for_sort_data it would frequently mean there are some TEXT/BLOB columns among them. It would be used however if there is large number of columns or there are long VARCHAR columns used – it takes only couple of UTF8 VARCHAR(255) to create a row which is longer than max_length_for_sort_data in its static presentation.

This suggests that max_length_for_sort_data is a limit on the total size of the columns that one is selecting, above which a filesort will be used instead of an index-based sort.

In your case, selecting entry_id (5002 bytes) takes the total size over this variable's 1KiB default value and therefore filesort is used. To raise the limit to 8KiB, you could do:

SET SESSION max_length_for_sort_data = 8192;

• I have a table with a very similar setup to this one, and this setting doesn't appear to trigger any changes in the use of filesort. – muffinista Oct 25 '12 at 13:25
• @muffinista: That's interesting. I suppose it might be related to some of the other buffer settings, per @RolandoMySQLDBA's answer? – eggyal Oct 25 '12 at 13:46

You've gotten a lot of interesting responses here, but no one has exactly answered the question -- why is this happening? As I understand it, when a SELECT query contains variable length data in MySQL, and there's no index which matches ALL of the requested columns, it will always use a filesort. The size of the data is not terribly relevant here. It's hard to find a direct answer to this question in the MySQL documentation, but here is a good blog post where someone is experiencing a very similar problem to yours.

So, if its viable to have an index on entry_id, then you could add it and be all set. But I doubt that it is an option, so what to do?

Whether you should do anything about this is a separate question. It's important to know that 'filesort' is poorly named in MySQL -- it's really just the name of the algorithm used to sort this particular query, and in many cases, the sort will actually happen in memory. If you don't expect this table to grow much, it's probably not a big deal.

On the other hand, if this table is going to have a million rows in it, you might have a problem. If you need to support pagination of queries on this table, then you might have a really serious performance issue here. In that case, partitioning off your variable-length data into a new table, and doing a JOIN to retrieve it is a valid optimization to consider.

Here's a couple other answers on SO that talk around this question:

• The OP's first query "contains variable length data in MySQL, and there's no index which matches ALL of the requested columns", yet filesort was apparently not used in that case. I also think that even sorting a small table in memory alone could prove to be an unacceptable performance hit: e.g. if the query is performed a lot (and the table changes so that caches cannot be used). – eggyal Oct 25 '12 at 13:49
• I don't have time to test it, but I'm wondering if this is triggered by having a VARCHAR which requires 2 bytes for storing the length as specified in dev.mysql.com/doc/refman/5.1/en/char.html -- so the first query fits within that limit but the second does not. – muffinista Oct 25 '12 at 13:59

Try adding a WHERE clause into your queries.

The index can be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause.

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

• But in this case the ORDER BY does match the index exactly, so there is no need to have a WHERE clause. – eggyal Oct 12 '12 at 19:05
• I have a "where" clause in the actual query on the site, so I know thats not the cause of the file sort. I'm wondering if its the use of varchar? – Derek B Oct 13 '12 at 2:39

To the extent of my knowledge varchar can only hold a maximum of 8000 bytes which is roughly 4000 characters. Thus, 5000 would seem to be exceding the limit of storage, and in this case probably the reason why the sorting is getting messed up.

"varchar [ ( n | max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying."

• As documented under The CHAR and VARCHAR Types: "Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used." – eggyal Oct 17 '12 at 22:53

You only have 126 rows in your table. Even if every row is sized to the max of about 5KB, that would mean that total size to read from disk is only about 600KB - this is not a whole lot. To be frank, it is very small amount, probably less than cache size of most modern disk drives.

Now, if server needs to retrieve your data to fulfill your query, most expensive operation is to read it from disk. But, reading it according to the index order is NOT always fastest way to do it, especially when amount of data is so small.

In your case, it is MUCH more efficient to read whole table data from disk as single block into memory (probably in just one disk read operation or seek), and then sort it in RAM to satisfy ORDER BY, which is instant compared to disk read operation. If server read your data according to the index, it would have to issue up to 126 (oops!) read operations, seeking back and forth within same data file many times.

In other words, sequential scan is NOT always a bad thing, and mysql is not necessarily stupid. If you try to force mysql to use that index, it will most likely work slower than sequential scan you currently have.

And the reason why it WAS using index when 5KB field was not included is because then retrieved data did not constitute 99% of data in table. When you included your 5KB field, now query has to read 99% of data, and it is cheaper to read the whole thing and sort it in memory afterwards.

• It sounds like you're muddling a number of things from How to Avoid Full Table Scans, which are to do with index use in satisfying JOIN conditions and WHERE clauses, not ORDER BY clauses. – eggyal Oct 24 '12 at 6:04
• Exactly the opposite. In this particular case full table scan is GOOD thing simply because it is FASTER than reading by index order. – mvp Oct 24 '12 at 6:09

What version of MySQL are you using?

IN 5.1, I tried to setup your scenario and populated some dummy data. Using the SQLs you provided, I only get a table scan each time according to the EXPLAIN. By Default when you use order by MYSQL resorts to filesort even if the primary index is used in the order by.