Mysql is it faster to use numbers in where clause compared to strings?

Question

Lets say you have 4 types of assessments, Test, Quiz, MiniQuiz and FinalExam

and we store records in the database like so

studentid ----- assesType
1                test
2                quiz
3                quiz
4                quiz
5                miniquiz
6                miniquiz
7                final
8                final

is it faster and a better approach to assign numbers to each type lets say:

test = 1 quiz = 2 miniquiz = 3 final = 4

AND use this instead for record keeping.

studentid ----- assesType
1                1
2                2
3                2
4                2
5                3
6                3
7                4
8                5

What I really want to ask, is it worth it to do this? advantages etc ? since it becomes a bit harder to write code in the server-side language of choice when doing this.

Thank you =)

Answer 1

Yes, numeric comparisons are faster than string comparisons. Strings also take a lot more space, and the data duplication means that if you'll have to rename "miniquiz" to "microquiz" you'd have to update all the rows. Finally, and probably the most important of all, your database wouldn't be able to reject unacceptable strings: You said that there are four types of assessments, but your database will happily accept any string you pass.

In general, you would want to create another table, maybe calling it assesTypes, with just id and name fields, and keep in it all the acceptable types. Then in your main table, make the assesType field a foreign key that references the id attribute of the new assesTypes table. Example:

CREATE TABLE assesTypes (
   id    int,
   name  varchar(15),
   PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE assessments (
   student_id    int,
   assesType     int,
   mark          int,
   PRIMARY KEY (student_id, assesType),
   FOREIGN KEY (assesType) REFERENCES assesTypes (id)
) ENGINE=INNODB;

Now we can populate our assesTypes table:

INSERT INTO assesTypes VALUES (1, 'Test');
INSERT INTO assesTypes VALUES (2, 'Quiz');
INSERT INTO assesTypes VALUES (3, 'MiniQuiz');
INSERT INTO assesTypes VALUES (4, 'FinalExam');

And now let's insert some assessment data into the assessments table:

INSERT INTO assessments VALUES (1, 1, 55);
INSERT INTO assessments VALUES (1, 2, 65);
INSERT INTO assessments VALUES (1, 3, 75);

That's all good. We can now INNER JOIN the assessments table with the assessTypes table like this:

SELECT a.student_id, at.name, a.mark
FROM   assessments a
JOIN   assesTypes at ON (at.id = a.assesType);

For this result:

+------------+----------+------+
| student_id | name     | mark |
+------------+----------+------+
|          1 | Test     |   55 |
|          1 | Quiz     |   65 |
|          1 | MiniQuiz |   75 |
+------------+----------+------+
3 rows in set (0.00 sec)

Now let's try to insert an invalid assesType in the assessments table:

INSERT INTO assessments VALUES (1, 5, 75);

We can't. MySQL will report:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Foreign keys aren't required to have a working relational database, but they are essential to avoid broken relationships and orphan rows (ie. referential integrity). The ability to enforce referential integrity at the database level is required for the C in ACID to stand.