Same database, different mysql: ENORMOUS DIFFERENCE in query time execution

Question

I have two MySQL installations, one is on Windows with Xampp, the other one is on Mac. Machines are similar in hardware.
The databases are the same, imported in each MySQL from a DUMP file.

I publish here the two EXPLAINS EXTENDED:

EXPLAIN EXTENDED (ON MAC)
+----+--------------------+--------------+------------+--------+--------------------------+----------+---------+---------------------------------------+------+----------+---------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------------+------------+--------+--------------------------+----------+---------+---------------------------------------+------+----------+---------------------------------------------------------------+
| 1 | PRIMARY | little_tasks | NULL | ref | post_id,meta_key | meta_key | 767 | const | 620 | 0.01 | Using where; Using temporary; Using filesort; Start temporary |
| 1 | PRIMARY | exp_baby | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 0.39 | Using where |
| 1 | PRIMARY | certs | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 0.39 | Using where |
| 1 | PRIMARY | radius | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 3.00 | Using where |
| 1 | PRIMARY | exp_years | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 1.00 | Using where |
| 1 | PRIMARY | remun_max | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 1.00 | Using where |
| 1 | PRIMARY | edu | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 1.00 | Using where |
| 1 | PRIMARY | start_date | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 3.04 | Using where |
| 1 | PRIMARY | moment | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 3.04 | Using where |
| 1 | PRIMARY | lat | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 3.53 | Using where |
| 1 | PRIMARY | lon | NULL | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.little_tasks.post_id | 12 | 3.53 | Using where |
| 1 | PRIMARY | p | NULL | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | helpeeit_helpee2.little_tasks.post_id | 1 | 24.26 | Using where; End temporary |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
| 2 | SUBQUERY | spoken_lang | NULL | ref | post_id,meta_key | meta_key | 767 | const | 620 | 0.02 | Using where |
+----+--------------------+--------------+------------+--------+--------------------------+----------+---------+---------------------------------------+------+----------+---------------------------------------------------------------+
14 rows in set, 2 warnings (4 min 18,58 sec)

EXPLAIN EXTENDED (ON WINDOWS/XAMPP)
+----+--------------------+--------------+----------------+--------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------------+----------------+--------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | radius | ref | post_id,meta_key | meta_key | 767 | const | 619 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | p | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | helpeeit_helpee2.radius.post_id | 1 | 100.00 | Using where |
| 1 | PRIMARY | exp_years | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.p.ID | 5 | 100.00 | Using where |
| 1 | PRIMARY | start_date | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | 100.00 | Using where |
| 1 | PRIMARY | moment | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.start_date.post_id | 5 | 100.00 | Using where |
| 1 | PRIMARY | lat | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.radius.post_id | 5 | 100.00 | Using where |
| 1 | PRIMARY | lon | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.lat.post_id | 5 | 100.00 | Using where |
| 8 | DEPENDENT SUBQUERY | edu | index_subquery | post_id,meta_key | post_id | 8 | func | 5 | 100.00 | Using where |
| 7 | DEPENDENT SUBQUERY | certs | index_subquery | post_id,meta_key | post_id | 8 | func | 5 | 100.00 | Using where |
| 6 | DEPENDENT SUBQUERY | little_tasks | index_subquery | post_id,meta_key | post_id | 8 | func | 5 | 100.00 | Using where |
| 5 | DEPENDENT SUBQUERY | exp_baby | index_subquery | post_id,meta_key | post_id | 8 | func | 5 | 100.00 | Using where |
| 4 | DEPENDENT SUBQUERY | remun_max | index_subquery | post_id,meta_key | post_id | 8 | func | 5 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
| 2 | DEPENDENT SUBQUERY | spoken_lang | index_subquery | post_id,meta_key | post_id | 8 | func | 5 | 100.00 | Using where |
+----+--------------------+--------------+----------------+--------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
14 rows in set, 1 warning (0.03 sec) 

You can see the execution time by yourself 4 minutes on MAC while only 0.03 seconds on WINDOWS/Xampp

And here is the query:

SELECT DISTINCT p.ID,p.post_author, lat.meta_value AS lat, lon.meta_value AS lon, radius.meta_value AS radius, start_date.meta_value AS date_interval, moment.meta_value AS day_moment, exp_years.meta_value AS exp_years
FROM `hlp_posts` p
INNER JOIN hlp_postmeta AS lat ON (p.ID = lat.post_id AND lat.meta_key = "hlp_latitude")
INNER JOIN hlp_postmeta AS lon ON (p.ID = lon.post_id AND lon.meta_key = "hlp_longitude")
INNER JOIN hlp_postmeta AS radius ON (p.ID = radius.post_id AND radius.meta_key = "hlp_resume_range_distance")
INNER JOIN hlp_postmeta AS start_date ON (p.ID = start_date.post_id AND start_date.meta_key = "hlp_resume_date")
INNER JOIN hlp_postmeta AS moment ON (p.ID = moment.post_id AND moment.meta_key = "hlp_resume_availability")
INNER JOIN hlp_postmeta AS exp_years ON (p.ID = exp_years.post_id AND exp_years.meta_key = 'resume_years_of_exp' AND (exp_years.meta_value >= 4))
WHERE
p.post_type = 'babysitter'
AND p.post_status = 'publish'
AND (
p.ID IN (SELECT spoken_lang.post_id FROM hlp_postmeta AS spoken_lang WHERE spoken_lang.meta_key = 'wpjobus_resume_languages' AND (spoken_lang.meta_value LIKE '%Inglese%' AND spoken_lang.meta_value LIKE '%Francese%' AND spoken_lang.meta_value LIKE '%Spagnolo%' AND spoken_lang.meta_value LIKE '%Tedesco%'))
OR
p.ID IN (SELECT native_lang.post_id FROM hlp_postmeta AS native_lang WHERE native_lang.meta_key = 'wpjobus_resume_native_language' AND native_lang.meta_key = 'wpjobus_resume_native_language' AND (native_lang.meta_value = 'Inglese' AND native_lang.meta_value = 'Francese' AND native_lang.meta_value = 'Spagnolo' AND native_lang.meta_value = 'Tedesco'))
)
AND p.ID IN (SELECT remun_max.post_id FROM hlp_postmeta AS remun_max WHERE remun_max.meta_key = 'wpjobus_resume_remuneration' AND remun_max.meta_value <= 5)
AND p.ID IN (SELECT exp_baby.post_id FROM hlp_postmeta AS exp_baby WHERE exp_baby.meta_key = 'wpjobus_resume_skills' AND (exp_baby.meta_value LIKE '%i:0%' AND exp_baby.meta_value LIKE '%i:1%' AND exp_baby.meta_value LIKE '%i:2%' AND exp_baby.meta_value LIKE '%i:3%'))
AND p.ID IN (SELECT little_tasks.post_id FROM hlp_postmeta AS little_tasks WHERE little_tasks.meta_key = 'hlp_resume_little_tasks' AND (little_tasks.meta_value LIKE '%i:0%' AND little_tasks.meta_value LIKE '%i:1%' AND little_tasks.meta_value LIKE '%i:2%' AND little_tasks.meta_value LIKE '%i:3%' AND little_tasks.meta_value LIKE '%i:4%' AND little_tasks.meta_value LIKE '%i:5%' AND little_tasks.meta_value LIKE '%i:6%'))
AND p.ID IN (SELECT certs.post_id FROM hlp_postmeta AS certs WHERE certs.meta_key = 'hlp_resume_prof_edu' AND (certs.meta_value LIKE '%i:0%' AND certs.meta_value LIKE '%i:2%' AND certs.meta_value LIKE '%i:3%' AND certs.meta_value LIKE '%i:7%' AND certs.meta_value LIKE '%i:11%'))
AND p.ID IN (SELECT edu.post_id FROM hlp_postmeta AS edu WHERE edu.meta_key = 'hlp_resume_edu' AND (edu.meta_value >= 3))
ORDER BY `p`.`ID` DESC 

I really can't explain how this is possible.

UPDATE 2017年09月06日
Since the difference in performance turned out to be more related to mysql version I installed MySQL 5.5.16 on Mac which now has two versions installed on it, the 5.5.16 (installed by me) and the one brought in by Mamp, which is version 5.6.35
So the following tests are done on the same machine (no virtualizations) and on the same database imported from an .sql file.

I did the test with two queries giving the same results:
with JOINS ONLY as advised by @Rick and with JOINS AND SUBQUERIES

—————————— TEST QUERY (ONLY JOINS) ——————————
SELECT DISTINCT p.ID, p.post_author, lat.meta_value AS lat, lon.meta_value AS lon, radius.meta_value AS radius, start_date.meta_value AS date_interval, moment.meta_value AS day_moment, exp_years.meta_value AS exp_years
FROM hlp_posts p
INNER JOIN hlp_postmeta AS exp_years ON (p.ID = exp_years.post_id AND exp_years.meta_key = 'resume_years_of_exp' AND (exp_years.meta_value <= 1))
INNER JOIN hlp_postmeta AS lat ON (p.ID = lat.post_id AND lat.meta_key = 'hlp_latitude')
INNER JOIN hlp_postmeta AS lon ON (p.ID = lon.post_id AND lon.meta_key = 'hlp_longitude')
INNER JOIN hlp_postmeta AS radius ON (p.ID = radius.post_id AND radius.meta_key = 'hlp_resume_range_distance')
INNER JOIN hlp_postmeta AS start_date ON (p.ID = start_date.post_id AND start_date.meta_key = 'hlp_resume_date')
INNER JOIN hlp_postmeta AS moment ON (p.ID = moment.post_id AND moment.meta_key = 'hlp_resume_availability')
INNER JOIN hlp_postmeta AS edu ON (p.ID = edu.post_id AND edu.meta_key = 'hlp_resume_edu' AND (edu.meta_value >= 1))
INNER JOIN hlp_postmeta AS exp_baby ON (p.ID = exp_baby.post_id AND exp_baby.meta_key = 'wpjobus_resume_skills' AND (exp_baby.meta_value LIKE '%i:0%'))
INNER JOIN hlp_postmeta AS little_tasks ON (p.ID = little_tasks.post_id AND little_tasks.meta_key = 'hlp_resume_little_tasks' AND (little_tasks.meta_value LIKE '%i:0%'))
INNER JOIN hlp_postmeta AS remun_max ON (p.ID = remun_max.post_id AND remun_max.meta_key = 'wpjobus_resume_remuneration' AND remun_max.meta_value <= 4)
WHERE p.post_type = 'babysitter'
AND p.post_status = 'publish'
ORDER BY `p`.`ID` DESC; 

—————————— TEST QUERY (JOINS & SUBQUERIES) ——————————
SELECT DISTINCT p.ID, p.post_author, lat.meta_value AS lat, lon.meta_value AS lon, radius.meta_value AS radius, start_date.meta_value AS date_interval, moment.meta_value AS day_moment, exp_years.meta_value AS exp_years
FROM hlp_posts p
INNER JOIN hlp_postmeta AS exp_years ON (p.ID = exp_years.post_id AND exp_years.meta_key = 'resume_years_of_exp' AND (exp_years.meta_value <= 1))
INNER JOIN hlp_postmeta AS lat ON (p.ID = lat.post_id AND lat.meta_key = 'hlp_latitude')
INNER JOIN hlp_postmeta AS lon ON (p.ID = lon.post_id AND lon.meta_key = 'hlp_longitude')
INNER JOIN hlp_postmeta AS radius ON (p.ID = radius.post_id AND radius.meta_key = 'hlp_resume_range_distance') 
INNER JOIN hlp_postmeta AS start_date ON (p.ID = start_date.post_id AND start_date.meta_key = 'hlp_resume_date')
INNER JOIN hlp_postmeta AS moment ON (p.ID = moment.post_id AND moment.meta_key = 'hlp_resume_availability')
WHERE p.post_type = 'babysitter'
AND p.post_status = 'publish'
AND p.ID IN (SELECT edu.post_id FROM hlp_postmeta AS edu WHERE edu.meta_key = 'hlp_resume_edu' AND (edu.meta_value >= 1))
AND p.ID IN (SELECT exp_baby.post_id FROM hlp_postmeta AS exp_baby WHERE exp_baby.meta_key = 'wpjobus_resume_skills' AND (exp_baby.meta_value LIKE '%i:0%'))
AND p.ID IN (SELECT little_tasks.post_id FROM hlp_postmeta AS little_tasks WHERE little_tasks.meta_key = 'hlp_resume_little_tasks' AND (little_tasks.meta_value LIKE '%i:0%'))
AND p.ID IN (SELECT remun_max.post_id FROM hlp_postmeta AS remun_max WHERE remun_max.meta_key = 'wpjobus_resume_remuneration' AND remun_max.meta_value <= 4)
ORDER BY `p`.`ID` DESC;

I publish here the EXPLAINS which are quite surprising:

—————————— EXPLAIN (ONLY JOINS) 5.5.16 ——————————
+----+-------------+--------------+--------+--------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+--------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | exp_years | ref | post_id,meta_key | meta_key | 767 | const | 619 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | helpeeit_helpee2.exp_years.post_id | 1 | Using where |
| 1 | SIMPLE | radius | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.p.ID | 6 | Using where |
| 1 | SIMPLE | edu | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.p.ID | 6 | Using where |
| 1 | SIMPLE | exp_baby | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.p.ID | 6 | Using where |
| 1 | SIMPLE | little_tasks | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.edu.post_id | 6 | Using where |
| 1 | SIMPLE | remun_max | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 6 | Using where |
| 1 | SIMPLE | start_date | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 6 | Using where |
| 1 | SIMPLE | moment | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.edu.post_id | 6 | Using where |
| 1 | SIMPLE | lat | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.p.ID | 6 | Using where |
| 1 | SIMPLE | lon | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.edu.post_id | 6 | Using where |
+----+-------------+--------------+--------+--------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
11 rows in set (30.95 sec) 

—————————— EXPLAIN (ONLY JOINS) 5.6.35 ——————————
+----+-------------+--------------+--------+--------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+--------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | exp_years | ref | post_id,meta_key | meta_key | 767 | const | 619 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | helpeeit_helpee2.exp_years.post_id | 1 | Using where |
| 1 | SIMPLE | radius | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | edu | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | exp_baby | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | little_tasks | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | remun_max | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | start_date | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | moment | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | lat | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | lon | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
+----+-------------+--------------+--------+--------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
11 rows in set (41,12 sec) 

—————————— EXPLAIN (JOINS & SUBQUERIES) 5.5.16 ——————————
+----+--------------------+--------------+----------------+--------------------------+----------+---------+------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------------+----------------+--------------------------+----------+---------+------------------------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | exp_years | ref | post_id,meta_key | meta_key | 767 | const | 619 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | p | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | helpeeit_helpee2.exp_years.post_id | 1 | 100.00 | Using where |
| 1 | PRIMARY | radius | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 6 | 100.00 | Using where |
| 1 | PRIMARY | start_date | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.radius.post_id | 6 | 100.00 | Using where |
| 1 | PRIMARY | moment | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.p.ID | 6 | 100.00 | Using where |
| 1 | PRIMARY | lat | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 6 | 100.00 | Using where |
| 1 | PRIMARY | lon | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.radius.post_id | 6 | 100.00 | Using where |
| 5 | DEPENDENT SUBQUERY | remun_max | index_subquery | post_id,meta_key | post_id | 8 | func | 6 | 100.00 | Using where |
| 4 | DEPENDENT SUBQUERY | little_tasks | index_subquery | post_id,meta_key | post_id | 8 | func | 6 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | exp_baby | index_subquery | post_id,meta_key | post_id | 8 | func | 6 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | edu | index_subquery | post_id,meta_key | post_id | 8 | func | 6 | 100.00 | Using where |
+----+--------------------+--------------+----------------+--------------------------+----------+---------+------------------------------------+------+----------+----------------------------------------------+
11 rows in set, 1 warning (0.06 sec) 

—————————— EXPLAIN (JOINS & SUBQUERIES) 5.6.35 ——————————
+----+--------------+--------------+--------+--------------------------+------------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------+--------+--------------------------+------------+---------+------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | exp_years | ref | post_id,meta_key | meta_key | 767 | const | 619 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | helpeeit_helpee2.exp_years.post_id | 1 | Using where |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 8 | helpeeit_helpee2.exp_years.post_id | 1 | NULL |
| 1 | SIMPLE | <subquery3> | eq_ref | <auto_key> | <auto_key> | 8 | helpeeit_helpee2.exp_years.post_id | 1 | NULL |
| 1 | SIMPLE | <subquery4> | eq_ref | <auto_key> | <auto_key> | 8 | helpeeit_helpee2.exp_years.post_id | 1 | NULL |
| 1 | SIMPLE | <subquery5> | eq_ref | <auto_key> | <auto_key> | 8 | helpeeit_helpee2.exp_years.post_id | 1 | NULL |
| 1 | SIMPLE | radius | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | start_date | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | moment | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | lat | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 1 | SIMPLE | lon | ref | post_id,meta_key | post_id | 8 | helpeeit_helpee2.exp_years.post_id | 5 | Using where |
| 2 | MATERIALIZED | edu | ref | post_id,meta_key | meta_key | 767 | const | 619 | Using where; Distinct |
| 3 | MATERIALIZED | exp_baby | ref | post_id,meta_key | meta_key | 767 | const | 619 | Using where; Distinct |
| 4 | MATERIALIZED | little_tasks | ref | post_id,meta_key | meta_key | 767 | const | 619 | Using where; Distinct |
| 5 | MATERIALIZED | remun_max | ref | post_id,meta_key | meta_key | 767 | const | 619 | Using where; Distinct |
+----+--------------+--------------+--------+--------------------------+------------+---------+------------------------------------+------+----------------------------------------------+
15 rows in set (44,88 sec) 

Substantially the only query giving satisfactory performance is the one with join & subqueries on MySQL 5.5.16

• Lennart - Slava Ukraini: The plans are different, among other things the query on MAC uses a file sort. Since the data is the same and the hardware is comparable, I would compare the config for the MAC DBMS and the Windows one and see if there is some memory area that differs in size. Also is the version of MySQL the same on both systems?
• Solarflare: As mentioned in your question on stackoverflow: spoken_lang.meta_value LIKE '%Francese%' AND spoken_lang.meta_value LIKE '%Spagnolo%' will very likely not be true for any (single) row. For the native_lang-condition it was clear at once (as you use = and not like) and thus marked it as Impossible WHERE. Different versions of MySQL will execute the different parts of your query in a different order. In one execution order, the fact that this is also an impossible where was just revealed earlier than the other. Fix your query to actually get a (possible) result and compare again.
• Vérace: @Solarflare - nice one - put that as an answer and I for one will upvote it! I'm also wondering if there isn't some underlying OS/swap space issue - what does a df -h give on the Mac (df -h is Linux - Mac equivalent)? Are you running at the limit of any of the filesystems? Also, why would a language be LIKE something? i.e. %Inglese%' - should it not be = 'Inglese'? How about a lookup table of languages or a CHECK constraint - oh, wait, that's right, MySQL doesn't have those :-(
• Rick James: Immediately after running EXPLAIN EXTENDED ..., run SHOW WARNINGS. It will provide the re-formulated query. We may see the differences there.
• Ferex: I installed Xampp on my Mac on a virtualized (with VMWare) Windows installation. So, very slow machine, but the result of the query is returned in a matter of milliseconds. Here in Italy it's early morning, by this evening I will do some of the tests you asked, and publish the results. Many thanks.

Answer 1

What versions of MySQL are involved? There have been optimization changes in recent versions in the type of constructs you are using.

Try to avoid IN ( SELECT ... ). In older versions, it optimized very poorly. Even in newer versions it may or may not be well optimized. Often it can be turned into a JOIN.

You probably have the out-of-box schema for wp_postmeta. That is inefficient. See this for how to improve it.

This is necessarily FALSE. Perhaps you meant OR instead of AND?:

 AND native_lang.meta_value = 'Inglese'
 AND native_lang.meta_value = 'Francese'

And check the rest of your ANDs.

If this

 AND (exp_baby.meta_value LIKE '%i:0%'
 AND exp_baby.meta_value LIKE '%i:1%'
 AND exp_baby.meta_value LIKE '%i:2%'
 AND exp_baby.meta_value LIKE '%i:3%')

is changed to OR, then this would be a little faster:

 AND exp_baby.meta_value REGEXP 'i:[0-3]'

But you cannot do a similar trick with 0,2,3,7,11. It could be done:

 AND certs.meta_value REGEXP 'i:(0|2|3|7|11)'

For consistency, you might prefer to use this pattern.

These suggestions will get you about half way to a clean query. (WP's use of EAV schema is the pits.)

• Ferex: Versions are 5.5 (Xampp), 5.6 (Mac), so the older one is the fastest. Many thanks for the advices you gave me, I really appreciate them and are very useful, do you think that a cleaner query will bring to a more consistent performance on both systems?
• Rick James: The REGEXP is unlikely to change performance much. Avoiding IN ( SELECT ... ) is very likely to lead to consistent timing. You could write a bug report ( bugs.mysql.com ) pointing out that you found a significant performance regression in going from 5.5 and 5.6. (They don't like to have regressions.) If possible, try on 5.7 (since 5.6 is unlikely to be fixed at this late date).
• Ferex: I updated my question. I also did the test on mysql 5.7 which performs as 5.6 on the query @Rick