CODEWITHSUNDEEP
mysqlperformance
David542
David542

Reputation: 110257

Improve SORTING performance on long VARCHAR field

I have a composite_title field that is VARCHAR(500) to hold the title of a movie or TV show. Here are a few examples of what it looks like:

- The Office, Season 1, Ep. 4 -- The Alliance
- BSG, Season 2, Ep. 3 -- Fragged
- Terminator

When I do a sort on the field using:

select composite_title from title order by composite_title limit 10

It is painfully slow and does not use an index, it uses a file sort. I tried adding something like:

ALTER TABLE title ADD INDEX composite_title(10)

But it did not improve performance.

What can I do to improve this query?

EXPLAIN

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  title   ALL NULL    NULL    NULL    NULL    341353  Using filesort

UPDATE:

The longest tv show in the database is 285 characters long.

From Bill Karwin's solution, this is what worked for me:

ALTER TABLE `title` ADD `composite_title_for_sorting` VARCHAR(255)  NOT NULL  DEFAULT '';
UPDATE IGNORE title SET composite_title_for_sorting=composite_title;

Now if I sort by the composite_title_for_sorting, the query takes 1ms instead of 500ms.

Upvotes: 2

Views: 1626

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562378

MySQL should use an index for sorting, but it doesn't seem to like to do that if the index is a prefix index.

And it's clear that you must use a prefix index if your varchar length is so long.

So if you want to speed up the ORDER BY, the best way to do this would be to change your column's data type to something that can support a non-prefix index. (Do you really need varchar(500) anyway??)

ALTER TABLE title MODIFY composite_tite VARCHAR(255), ADD KEY (composite_title);

Then you should get this:

EXPLAIN SELECT composite_title FROM title ORDER BY composite_title\G

           id: 1
  select_type: SIMPLE
        table: title
         type: index
possible_keys: NULL
          key: composite_title
      key_len: 768    <-- this is the longest key_len allowed for an index
          ref: NULL
         rows: 100
        Extra: Using index

Another idea is to create a second column to store a shortened version of the string, and then index that column.

If you must do a filesort, then increasing the size of sort_buffer_size can possible reduce the dependence on using disk during sorting, which will help performance.

But if you increase sort_buffer_size globally, be aware that it is allocated for every session that's sorting during a query, so if you set it to 1GB or something huge like that, you could blow out your memory at an unpredictable time.

You can change sort_buffer_size on a session-by-session basis. So you could leave it at its default size globally, but in the session where you run this one type of query, increase it.

Upvotes: 4

majidarif
majidarif

Reputation: 20025

Try doing an explain ...query. See this fiddle.

Then add an index like:

CREATE INDEX `helper` ON `title` (`composite_title`);

or

ALTER TABLE `title` ADD INDEX helper(`composite_title`);

Then retry your query:

SELECT composite_title
FROM title 
USE INDEX(helper)
ORDER BY composite_title ASC
LIMIT 10;

MySQL optimizer most often does the correct choice. But sometimes it makes a bad choice, or not the best choice. So we force it.

But technically you add a key to the columns you would use where on.

Maybe some settings for performance instead?

Set global thread_cache_size        = 4;
Set global query_cache_size         = 1024*1024*1024;
Set global query_cache_limit        = 768*1024;
Set global query_cache_min_res_unit = 2048;
Set long_query_time                 = 5;

Or maybe even use ElasticSearch with mysql.

Upvotes: 0

Related Questions