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)
The result shows the speed is 4 seconds.

Create Indexes & Foreign Keys

To create the various kinds of indexes and foreign keys:
MySQL: create indexes, foreign keys & constraints



What's Next?

blog comments powered by Disqus