mariaDB Limit Issue
We I run the below query on mariaDB 5.6 With the "LIMIT 100000" query get results in approximately 0.14s and when we remove the limit results will be around 1 minute but when we run the same query with or without limit on mysql 5.6 results will be in 0.12s, Kindly provide better way around why miaraDB engine has such a issue or may be our query has something to change.
EXPLAIN SELECT DISTINCT am.* FROM asset_make am, ( SELECT DISTINCT amg.asset_make_id FROM asset_model_groups amg, ( SELECT amgf.make_model_group_id FROM asset_model_groups_fp amgf, ( SELECT fp.financial_product_id_pk FROM financial_product fp, ( SELECT fpb.financial_product_id FROM financial_product_bps fpb, ( SELECT usr.relative_user_id FROM user_relationships usr, ( SELECT ud.user_bp_id FROM user_definition ud WHERE ud.user_id = 'd75502' ) AS ud WHERE usr. CODE = '00112' AND usr.userDefinition_user_id = ud.user_bp_id ) AS usr WHERE fpb.business_partner_id = usr.relative_user_id AND fpb.role_id = '00001' ) AS fpb WHERE fp.financial_product_id_pk = fpb.financial_product_id AND DATE( FROM_UNIXTIME(fp.valid_to_date / 1000) ) >= DATE(NOW()) LIMIT 100000 ) AS fp WHERE amgf.financial_product_id = fp.financial_product_id_pk ) AS amgf WHERE amg.make_model_group_id = amgf.make_model_group_id ) AS amg WHERE am.make_id_pk = amg.asset_make_id
Answer Answered by Ian Gilfillan in this comment.
There have been a number of optimizer improvements in the later stable releases, MariaDB 10.0, MariaDB 10.1, MariaDB 10.2 and MariaDB 10.3 - if MySQL 5.6 runs your query faster than 5.5, it's very likely the same improvements were made in a version of MariaDB > 5.5, so you can try upgrading your MariaDB version and see if that helps.
If not, and the same query runs slower on MariaDB, you can report as a bug with more details to help recreate the problem.