MySQL Indexing VarChar

Question

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.

Answer 1

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.

Answer 2

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;

Answer 3

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

Answer 4

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."

Hope this answers your question

Answer 5

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.

Answer 6

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.

Answer 7

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.

See also: 10 Tips for Optimizing MySQL Queries (That don't suck).

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:

• Can there be TEXT/BLOB columns in a table without filesort in MySQL?
• Vertical partitioning of tables in MySQL