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

[Aftab Khan]

>>NON-EXISTENT keys
>SELECT first.num
>FROM first
>LEFT JOIN second AS second_1 ON second_1.num = -1 # non-existent key
1000 rows in set (0.01 sec)

I would expect from the query that actually returns result set should be little slower than the one doesn't return anything.

>>EXISTENT keys
>mysql> SELECT first.num
-> FROM first
-> LEFT JOIN second AS second_1 ON second_1.num = 1 # existent key
Empty set (0.00 sec)

Lets benchmark using the query that use NON-EXISTENT keys
# Your SQL is placed inside function 'select_test()'

mysql> select benchmark(10000,select_test());
+--------------------------------+
| benchmark(10000,select_test()) |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (7.04 sec)

As you can see the benchmarks performed using single threads - difference in query response time is visible, if you increase concurrency 'SELECTs' won't scale.


Lets benchmark using the query that use EXISTENT keys
# Your SQL is placed inside function 'select_test_2()'

mysql> select benchmark(10000,select_test_2());
+----------------------------------+
| benchmark(10000,select_test_2()) |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (3.39 sec)



The function is called 10k times and we know this query return 0 rows.


Did I answer you question?



Edited 1 time(s). Last edit at 06/28/2012 02:35PM by Aftab Khan.