Mysql VS MariaDb Same query performance

Question

I have the same database on 2 servers. One is with Cpanel running mysql other one its CWP with mariadb. Both databases are identical, tables have the same index, mysql variables are identical but the performance its way different:

SELECT 
    DISTINCT item_id
FROM                                                        
    site_plugin_products_cache_filters
WHERE
    value_id IN (32)

On MySql I get 1,939 rows (1.284 s) On MaridDb I get 1,939 rows (0.097 s)

Any idea why this happens ?

When i get to sub-queries the differences are way bigger ( 0.01s vs 100+ seconds )

SELECT 
    DISTINCT item_id
FROM                                                        
    site_plugin_products_cache_filters
WHERE
    value_id IN (32) AND
    item_id IN (
        SELECT item_id FROM 
            site_plugin_products_cache_cats
        WHERE
            cat_id=1
    )

Or

SELECT  DISTINCT item_id  FROM site_plugin_products_cache_cats
WHERE cat_id IN (362)  AND item_id != 2519 ORDER BY rand()
LIMIT 4    

4 rows (0.053 s) vs 4 rows (0.103 s)

Answer 1

Seems WHM / CPanel comes by default with query_cache_size=0 and this was killing the performance. After set to an optimal value all its normal :)

Answer 2

Most of the timings above do not look like the Query cache was involved. The only one that might have used the QC is the one taking 0.01s. To avoid the QC for one SELECT, do SELECT SQL_NO_CACHE ...

MariaDB and MySQL have diverged significantly in the handling of IN ( SELECT ... ) This may explain one of the differences.

Do both machines have the same type of disk?

There is other caching that goes on. On a spinning disk (not SSD), a typical disk hit takes 0.01s. 0.05s may that 5 blocks had to be fetched from disk; 0.10s may mean that 10 were fetched. This difference may be due to whatever was going on before running the test. To avoid this issue, run each query twice, and take the second timing.

The first query would benefit from INDEX(value_id, item_id).

2nd and 3rd queries: (cat_id, item_id)