Mysql VS MariaDb Same query performance

mariadbMySQL

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)

Best Answer

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 :)

Related Question