I have a database with one table which contains a few varchar columns which can contain data from a fixed set (testdata1 can contain three different values in about 10,000 rows).
I thought about to use two relational tables for the values, is it worth?
My actual database with one table:
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| description | text | YES | | NULL | |
| testdata1 | varchar(100) | NO | | NULL | |
| testdata2 | varchar(100) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
and I thought to replace the columns testdata1 and testdata2 with a relational tables, like this:
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| description | text | YES | | NULL | |
| testdata1 | int(10) | NO | | NULL | |
| testdata2 | int(10) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
and the tables, they are just simple id and name (this table exist two times, for testdata1 and testdata2)
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
I want the best performance and the smallest size, because I have to query the database often via a PHP Api.
Edit:
The most used query:
`SELECT * FROM `table` WHERE `column` LIKE '%$query_input%' ORDER BY `column` ASC LIMIT $start_input, $count_input`
Replace column with name, testdata1 or testdata2 (the table have 5 more columns which are like testdata).
Sometimes there are UPDATEs or INSERTs, but they're mostly executed by myself.
