Performance of mysql equi-join observed in HDD and SSD I experimented performance of queries having equi-joins. I have simplified version of EXPLAINs of the queries.

Mysql version : 5.7.18

Engine : Innodb

Cache state : Mysql restarted before experiment so no buffer pool cache. Since, physical machine is restarted before every experiment there won't be OS cache.

Note : Primary key is nothing but an increasing INT.

CPUs: SSD machine is a Mac machine with 2.5 GHz Intel Core i7.
      HDD machine is a Mac machine with 2.3 GHz Intel Core i7

Case 1:


| id | select_type | table | partitions | type   | possible_keys | key     |  ref             | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+--------+----------+----+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY |  const,const     | 190864 |   100.00 | Using index |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY |  c.id.           |      1 |    10.00 | Using where |


Case 2:


+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+-------
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                         | rows   |Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+------+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY | 771     | const,const                 | 190864 |Using index 
|  1 | SIMPLE      | g     | NULL       | eq_ref | PRIMARY       | PRIMARY | 775     | const,const,c.id            |      1 |Using index 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+


I both cases it can be noted that:


the first table having 190864 rows ready to be joined to second table
the second table is about to join in 'eq_ref' order.


Interpretation:
 Each row from first table is going to find its pair in second table. Since there are 100k rows in first table , there will be 100k i/o to disk in random fashion (i.e., simply random i/o correct me if I were wrong). I have run this experiment with HDD and SSD.

Results (time taken):

Case 1 : 3.409s (SSD) and 26.651s (HDD)

Case 2 : 0.248s (SSD) and 1.364s (HDD)

It can be seen that there is a huge difference in performance. Is this because of that SSD are better at random I/Os? or simply SSDs are better at I/O itself?
I am excited to hear reliable comparisons between HDD and SSD, but first...

Here are some of the variables you have not accounted for.  (Or maybe you did, but failed to state.)


InnoDB?
One says "Using index", meaning that the processing happened only in the index's BTree ("Covering" index); the other apparently had to bounce between the secondary index and the data.  This could make a significant difference.
Table sizes?
Was the cache (buffer_pool) cold?  Please restart mysqld before each test.
Same CPU, since you want to focus on the I/O, not the CPU time.
If it was the same server, which drive was c on?
SHOW CREATE TABLE -- there could be subtle issues.
MySQL version.
Are the indexes aligned with the data?  For example:  AUTO_INCREMENT and TIMESTAMP might be virtually in lock-step.  But UUID is very random.


8x and 5.5x seem to be reasonable.