January 24, 2008

Enum Fields VS Varchar VS Int + Joined table: What is Faster?

Posted by Alexey Kovyrin

Really often in customers' application we can see a huge tables with varchar/char fields, with small sets of possible values. These are "state", "gender", "status", "weapon_type", etc, etc. Frequently we suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint)? In this post I'd like to present a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns.

In practice you can also often use 4th variant which is not comparable directly, which is using integer value and having value mapping done on application level.

So, first of all, a few words about our data set we've used for this benchmark. We have 4 tables:
1) Table with ENUM:

PLAIN TEXT
SQL:
  1. CREATE TABLE cities_enum (
  2.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.   state enum('Alabama','Alaska','Arizona','Arkansas','California','Colorado','Connecticut','Delaware','District of Columbia','Florida','Georgia','Hawaii','Idaho','Illinois','Indiana','Iowa','Kansas','Kentucky','Louisiana','Maine','Maryland','Massachusetts','Michigan','Minnesota','Mississippi','Missouri') NOT NULL,
  4.   city varchar(255) NOT NULL,
  5.   PRIMARY KEY  (id),
  6.   KEY state (state)
  7. ) ENGINE=MyISAM;

2) Table with VARCHAR:

PLAIN TEXT
SQL:
  1. CREATE TABLE cities_varchar (
  2.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.   state varchar(50) NOT NULL,
  4.   city varchar(255) NOT NULL,
  5.   PRIMARY KEY  (id),
  6.   KEY state (state)
  7. ) ENGINE=MyISAM;

3) Table with INT:

PLAIN TEXT
SQL:
  1. CREATE TABLE cities_join (
  2.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.   state_id tinyint(3) UNSIGNED NOT NULL,
  4.   city varchar(255) NOT NULL,
  5.   PRIMARY KEY  (id),
  6.   KEY state_id (state_id)
  7. ) ENGINE=MyISAM;

4) Dictionary table for cities_join:

PLAIN TEXT
SQL:
  1. CREATE TABLE IF NOT EXISTS `states` (
  2.   `id` tinyint(3) NOT NULL AUTO_INCREMENT,
  3.   `name` char(40) NOT NULL,
  4.   PRIMARY KEY  (`id`),
  5.   UNIQUE KEY `name` (`name`)
  6. ) ENGINE=MyISAM;

All cities_* tables have 1,5M records each and records are distributed among 29 different states (just happens to be data we had available for tests)

Two important notes about this table before we get to results - this is rather small table which fits in memory in all cases (and dictionary table does too). Second - the rows are relatively short in this table so changing state from VARCHAR to ENUM or TINYINT affects row size significantly. In many cases size difference will be significantly less.

All tests are runned 1000 times and the result time is average from those 1000 runs.

So, our first benchmark is simple: we need to get 5 names of cities, located in Minnesota and, to make things slower, we'll take those records starting from record #10000 making MySQL to discard first 10000 records.

1) Results for ENUM:

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE city FROM cities_enum WHERE state='Minnesota' LIMIT 10000,5;
  2. Result time(mean): 0.082196

2) Results for VARCHAR:

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE city FROM cities_varchar WHERE state='Minnesota' LIMIT 10000,5;
  2. Result time(mean): 0.085637

3) Results for INT + join:

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE c.city FROM cities_join c JOIN states s ON (s.id = c.state_id) WHERE s.name='Minnesota' LIMIT 10000,5;
  2. Result time(mean): 0.083277

So, as you can see, all three approaches are close with ENUM being fastest and VARCHAR few percent slower.

This may look counterintuitive because table is significantly smaller with ENUM or TINYINT but in fact it is quite expected - This is MyISAM table which is accessed via index, which means to retrieve each row MySQL will have to perform OS system call to read the row, at this point there is not much difference if 20 or 30 bytes are being read. For Full Table Scan operation difference often would be larger.

It is also interesting to note performance of Innodb tables in this case: for VARCHAR it takes about 0.022 per query which makes it about 4 times faster than for MyISAM. This is great example of the case when Innodb is much faster than MyISAM for Read load.

The other surprise could be almost zero cost of the join, which we always claimed to be quite expensive. Indeed there is no cost of the join in this case because there is really no join:

PLAIN TEXT
SQL:
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE c.city FROM cities_join c JOIN states s ON (s.id = c.state_id) WHERE s.name='Minnesota' LIMIT 10000,5 \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         TABLE: s
  6.          type: const
  7. possible_keys: PRIMARY,name
  8.           KEY: name
  9.       key_len: 40
  10.           ref: const
  11.          rows: 1
  12.         Extra:
  13. *************************** 2. row ***************************
  14.            id: 1
  15.   select_type: SIMPLE
  16.         TABLE: c
  17.          type: ref
  18. possible_keys: state
  19.           KEY: state
  20.       key_len: 1
  21.           ref: const
  22.          rows: 225690
  23.         Extra:
  24. 2 rows IN SET (0.10 sec)

Because we refer state by name, which is unique,it is pre-read and query executed basically on single table querying state by ID.

Next test was a result of my curiosity. I've tried to order results by states.

1) Results for ENUM:

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE city FROM cities_enum ORDER BY state LIMIT 10000, 5;
  2. Result time(mean): 0.077549

2) Results for VARCHAR:

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE city FROM cities_varchar ORDER BY state LIMIT 10000, 5;
  2. Result time(mean): 0.0854793

3)

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE c.city FROM cities_join c JOIN states s ON (s.id = c.state_id) ORDER BY s.name LIMIT 10000,5;
  2. Result time(mean): 26.0854793

As you can see, ENUM and VARCHAR show close performance, while join performance degraded dramatically.

Here is why:

PLAIN TEXT
SQL:
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE c.city FROM cities_join c JOIN states s ON (s.id = c.state_id) ORDER BY s.name LIMIT 10000,5\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         TABLE: c
  6.          type: ALL
  7. possible_keys: state
  8.           KEY: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: 1439943
  12.         Extra: USING TEMPORARY; USING filesort
  13. *************************** 2. row ***************************
  14.            id: 1
  15.   select_type: SIMPLE
  16.         TABLE: s
  17.          type: eq_ref
  18. possible_keys: PRIMARY
  19.           KEY: PRIMARY
  20.       key_len: 1
  21.           ref: test.c.state_id
  22.          rows: 1
  23.         Extra:
  24. 2 rows IN SET (0.00 sec)

Because we're sorting by name we have to perform the join for each row to retrieve it. This also means sort can't be done by index and extra sort pass (filesort) is required, which also makes MySQL to store Join result in temporary table to do the sort, all together makes things quite miserable. Note this might not be best execution plan to pick in this case but this is other story.

To avoid part of this problem we of course arrange state ids in the alphabetical order and do sort by state_id, though join cost still could be significant.

And the last test - selecting city and name in arbitrary order, skipping first 10000 rows to make query times longer.

1) Results for ENUM:

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE city, state FROM cities_enum LIMIT 10000, 5;
  2. Result time(mean): 0.003125

2) Results for VARCHAR:

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE city, state FROM cities_varchar LIMIT 10000, 5;
  2. Result time(mean): 0.003283

3)

PLAIN TEXT
SQL:
  1. SELECT SQL_NO_CACHE c.city, s.name FROM cities_join c JOIN states s ON (s.id = c.state_id) LIMIT 10000,5;
  2. Result time(mean): 0.004170

As you can see, ENUM and VARCHAR results are almost the same, but join query performance is 30% lower.
Also note the times themselves - traversing about same amount of rows full table scan performs about 25 times better than accessing rows via index (for the case when data fits in memory!)

So, if you have an application and you need to have some table field with a small set of possible values, I'd still suggest you to use ENUM, but now we can see that performance hit may not be as large as you expect. Though again a lot depends on your data and queries.

Posted by Alexey Kovyrin @ 11:29 am :: Innodb, benchmarks, ideas, myisam, tips  
Print This Post Print This Post book mark Enum Fields VS Varchar VS Int + Joined table: What is Faster? in del.icio.usdel.icio.us :: submit Enum Fields VS Varchar VS Int + Joined table: What is Faster? to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :Picking datatype for STATUS fields::Redundant index is not always bad::Using delayed JOIN to optimize count(*) and LIMIT queries:
 

19 Comments »

  1. 1. Roland Bouman

    Thanks! I did expect the enum to be fastest for all results, but it is amazing to see how little difference there sometimes is between the varchar and enum. Now I’m wondering, what if we’d be using CHARs instead of VARCHAR? For example for a set of language or country codes…I’d expect CHAR to be faster than VARCHAR for these small strings, but can you say whether it would be even noticable?

    Comment :: January 24, 2008 @ 1:46 pm

  2. 2. Jeremy Cole

    Hi Vadim,

    I would like to see these tests run with a real load generation system, e.g. rather than just a loop of 1000 queries. Maybe I will run them through our test system. :) I could come up with some of my own data for the test, but to make things totally fair and comparable, could you post your data sets?

    Regards,

    Jeremy

    Comment :: January 24, 2008 @ 1:51 pm

  3. 3. Jeremy Cole

    Sorry, I meant Alexey, not Vadim. Sorry Alexey. :)

    Comment :: January 24, 2008 @ 1:51 pm

  4. 4. Alexey Kovyrin

    Jeremy,

    I would love to see real system’s result! I’ve tried to create as “real” dataset as possible, but of course, on real tables with more columns, etc it would be even more interesting.

    Comment :: January 24, 2008 @ 2:00 pm

  5. 5. Alexey Kovyrin

    Jeremy,

    I’ll dump my data now and upload it somewhere soon.

    Comment :: January 24, 2008 @ 2:07 pm

  6. 6. Jay Pipes

    Nice results. One thing to keep in mind that the criteria for using ENUM should be small and *static* lookups. Trying to alter the ENUM values on large tables requires a table rebuild, versus a lookup table needs just an INSERT into the lookup table…

    Comment :: January 24, 2008 @ 2:08 pm

  7. 7. Alexey Kovyrin

    2Jeremy: http://blog.kovyrin.net/files/mpb/test.sql.bz2

    Comment :: January 24, 2008 @ 2:29 pm

  8. 8. peter

    Jeremy,

    In this case the benchmark was in memory anyway and this is micro benchmark to evaluate particular case. In real life there could be a lot of extra implications, for example getting VARCHAR updated may fragment a row causing quite nasty effects for IO bound workload.

    Jay,

    Thanks you’re right if you need to change number of values in ENUM it is nightmare. Sometimes you can hack around by avoiding costly ALTER TABLE (just replacing frm) but this is dangerous and not supported :)

    Comment :: January 24, 2008 @ 3:01 pm

  9. 9. Jurriaan Persyn

    Great to see some discussion and test results on this matter! In our application, we’re mostly using ENUM’s for these kind of problems (mostly because table size really is an issue).
    In this comment I want to illustrate why we’re sometimes using the 4th solution you briefly mention; i.e. using a TINYINT and doing the mapping on an application level.
    The problem we had with ENUM-fields; When we wanted to add a ‘type’ to an ENUM-field (or change the definition of the field in any way), MySQL often did a complete rewrite of that table. For some of our bigger tables (think tens of millions of records), this could take up to a few hours sometimes, which meant downtime. So for a few cases we decided to switch from ENUM’s to TINYINT’s because this eliminates the need for a change in table definition.

    Comment :: January 24, 2008 @ 5:51 pm

  10. 10. mostafa

    but if we make mistake in updating(include add,change,del) a field of enum type may be we lost data and it is awful

    Comment :: January 27, 2008 @ 7:11 am

  11. 11. peter

    Mostafa - only if you screw up badly.

    1) you need to have backups of course.
    2) you better to preserve old .frm file and if you change is not successful.
    3) you better test it outside of production to make sure such change works.

    But you’re right it is a bit dangerous :)

    Comment :: January 28, 2008 @ 10:02 am

  12. 12. Greg Knapp

    Not sure if this will have any impact on queries performance but your column definitions for state_id differ slightly between your dictionary table and cities_join table; cities_join.state_id is UNSIGNED and states.id isn’t.

    Comment :: January 29, 2008 @ 3:48 am

  13. 13. Kipp

    Does anyone know if/how these vary using sets. Maybe comparing set v. varchar list v. int used bitwise?

    Comment :: February 12, 2008 @ 7:21 pm

  14. 14. Оптимизация работы с MySQL | Записки маленького юниксоида

    [...] Хотя ENUM и противоречит нормальным формам, но, как показывают тесты, он быстрее других [...]

    Pingback :: February 14, 2008 @ 5:21 am

  15. 15. Оптимизация работы с MySQL at Всякие интересные штучки для WEB-разработчика

    [...] Хотя ENUM и противоречит нормальным формам, но, как показывают тесты, он быстрее других [...]

    Pingback :: February 14, 2008 @ 7:11 pm

  16. 16. *confused*

    16 bit fixed length integers vs random length strings (32 to 400 bits)

    Shouldn’t the enum be several orders of magnitude faster, especially on sorts? I don’t understand.
    Please explain to me why the benchmark shows only negligible differences.

    Comment :: April 20, 2008 @ 12:18 am

  17. 17. Raymond

    Thank you for these tests, it helped us find a solution to several issues before implementing.

    Comment :: May 8, 2008 @ 11:52 am

  18. 18. Hakan

    Thanks for the test.

    I think using the 4th way depends on the application. On our case, we store this kind of information in TINYINT and do the work in application, as we only select 25 of them at once. But when selecting thousands of row, and making change in all of them may not be better than storing in ENUM or varchar. I’ll do some tests about this when I have time and post here.

    Comment :: June 11, 2008 @ 7:26 am

  19. 19. somebody

    have you guys tried indexing state_id on the cities_join table.
    though I read something about frequent inserts with indexes being slow.
    but would indexing speed things up?

    and also

    SELECT SQL_NO_CACHE c.city FROM states s LEFT JOIN cities_join c ON (s.id = c.state_id) WHERE s.name=’Minnesota’ LIMIT 10000,5;

    since your searching by state maybe if you place it at the left mysql would search for that first and then search for the cities with similar state_id, and it will be ordered automatically.

    ENUM is what got me to this blog, but since you guys said its dangerous, it’s no longer in my options.

    Comment :: July 7, 2008 @ 8:59 am

 

Subscribe without commenting


This page was found by: varchar + add intige... mysql enum type mysql int field grea... performance int vs v... varchar