Re: Why does MySQL's performance decrease when queries are executed in parallel?

[Aftab Khan]

I am able to reproduce this issue, profiling information:


-- slow query (composite key)
-- time to run 1000 searches using concurrency 20
0m9.191s

-- Reason, most of the time spent on status: Sending data | 0.0078860 |
-- Since you don't have a primary key in the table, therefore Innodb would use UNIQUE key as PK and thus when using composite (double) index, it reads more data (i.e. both num and num2 columns)

mysql> show profile for query 1;
+--------------------+-----------+
| Status | Duration |
+--------------------+-----------+
| (initialization) | 0.000116 |
| Opening tables | 0.000025 |
| System lock | 0.000006 |
| Table lock | 0.000009 |
| init | 0.000031 |
| optimizing | 0.000018 |
| statistics | 0.000118 |
| preparing | 0.000027 |
| executing | 0.000006 |
| Sending data | 0.0078860 |
| end | 0.000006 |
| query end | 0.000005 |
| freeing items | 0.000008 |
| closing tables | 0.000005 |
| logging slow query | 0.000003 |
+--------------------+-----------+
15 rows in set (0.00 sec)


-- fast query (single index)
-- time to run 1000 searches using concurrency 20
0m0.571s

+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| (initialization) | 0.000003 |
| Opening tables | 0.000034 |
| System lock | 0.00001 |
| Table lock | 0.000006 |
| init | 0.000025 |
| optimizing | 0.000016 |
| statistics | 0.000063 |
| preparing | 0.000022 |
| executing | 0.000004 |
| Sending data | 0.000942 |
| end | 0.000004 |
| query end | 0.000004 |
| freeing items | 0.000008 |
| closing tables | 0.000005 |
| logging slow query | 0.000003 |
+--------------------+----------+
15 rows in set (0.00 sec)


Solution- keep your existing unique (composite/double) key, and add primary key to table 'second', i.e.

alter table second add primary key (num);



Edited 3 time(s). Last edit at 06/28/2012 12:07PM by Aftab Khan.