CODEWITHSUNDEEP
mysqlperformanceenums
Tech4Wilco
Tech4Wilco

Reputation: 6808

MySQL query performance dilemma: enum vs tables

I currently have this schema:

CREATE TABLE `users` (
  `users_id` int(11) NOT NULL AUTO_INCREMENT,
  `users_name` varchar(50),
  `users_lastname` varchar(50),
  `users_dob` date,
  `users_type` int(11) NOT NULL default 0,
  `users_access` int(11) NOT NULL default 0,
  `users_level` int(11) NOT NULL default 0,
  /* etc...*/
  PRIMARY KEY (`users_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `users_types` (
  `types_id` int(11) NOT NULL AUTO_INCREMENT,
  `types_name` varchar(50),
  PRIMARY KEY (`types_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/* etc..*/

Query:

SELECT
    types_name AS user_type, 
    /* all other fields*/
    users.*
    FROM users
    INNER JOIN users_types ON (users.users_type=types_id);
    /* INNER JOIN for all other tables*/
/* Rest of query */

My new solution:

CREATE TABLE `users` (
  `users_id` int(11) NOT NULL AUTO_INCREMENT,
  `users_name` varchar(50),
  `users_lastname` varchar(50),
  `users_dob` date,
  `users_type` ENUM('type1', 'type2', 'type3'),
  `users_access` ENUM('access1', 'access2', 'access3'),
  `users_level` ENUM('level1', 'level2', 'level3'),
  /* etc...*/
  PRIMARY KEY (`users_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Query:

SELECT
    *
    FROM users

From what I see is using ENUM is very simple and can be very fast to execute.

  1. Am I right? Would it be faster for the MySQL Engine to process an ENUM type field rather than having LEFT JOINs?
  2. Is using ENUM a good practice?

Thanks

Upvotes: 16

Views: 1831

Answers (2)

Robert Martin
Robert Martin

Reputation: 17157

Personally, I think the int data type should be used and ENUM-ification of that data should be done in another layer.

The table definition is a poor place to store your gamut of enum values. It's hard to get at easily, and giving your application the power to modify table definitions is a security problem (possibly).

Instead, I would recommend using an INT type and then in your software, creating a model that interacts with the database below to give the appearance of an ENUM.

With this design choice, switching database software is trivial, you don't need to grant "ALTER TABLE" privileges to your production app, and extending your enum is easy. Plus, then you are reducing the number of times that the program needs to do the translation from ENUM -> integer -- it can be done at compile-time instead of with every database SQL request.

Upvotes: 19

WWW
WWW

Reputation: 9860

1) Yes, it would be faster, as the ENUM datatype is indexed in the table itself (i.e. no need to read another table for each record)

2) Yes, as long as you don't want to use those fields in any other table. As soon as you want to use a given field in more than one table, you should create a separate lookup table for that field. Also, if you want the field to have user-definable values (and not require them to modify the database directly to change them), you should use a separate table.

Upvotes: 1

Related Questions