 |
 |
MySQL: create indexes with sample query speed test
This page shows how we can use MySQL to create tables with random data, then run speed tests of various kinds of indexes, unique indexes, foreign keys, and contraints.For our typical applications, we want to use InnoDB foreign keys based on unique indexes. This gives us the safety of foreign key constraints, for just a bit of extra time.
Details on how we set up these tests are below, after the table of results.
Indexes using InnoDB Engine vs. MyISAM Engine
Speed test results of various kinds of indexes using the InnoDB engine vs. MyISAM
| Command | InnoDB | MyISAM |
|---|---|---|
| join without index | 2.34 sec | 4.00 sec |
| join with default index | 4.55 sec | 27.84 sec |
| join with unique index | 3.96 sec | 3.97 sec |
| join with foreign key on default index | 4.65 sec | na |
| join with foreign key on unique index | 2.62 sec | na |
Next we describe how we created the tables, queries, indexes, foreign keys, and constraints.
Create tables
First create some sample tables and data like this:MySQL: create tables for joins with random rows
Sample query
To run the sample query: mysql> select count(foos.id) from foos join bars on foos.bar_id=bars.id;
+----------------+
| count(foos.id) |
+----------------+
| 1000000 |
+----------------+
1 row in set (4.00 sec)
Create Indexes & Foreign Keys
To create the various kinds of indexes and foreign keys:MySQL: create indexes, foreign keys & constraints
What's Next?
- Index Of Articles: Web Design & Development
- MySQL: database table schema via describe, show, mysqldump
- MySQL: create random data text strings
- MySQL: create tables for joins with random rows
- MySQL: create indexes, foreign keys & constraints