1

I have an application that I noticed suddenly decreased in speed when copying from the live database to a test database. Both databases are on the same server, and the test database was created from a mysqldump of the live data. So they are identical, on the same instance.

When I explain a slow query on the two databases, one is using indexes and the other is not. This is happening on more than one query type, but I will show one example:

Here is the query I'm running:

SELECT * FROM  product
INNER JOIN product_category pc
ON product.id = pc.product_id
INNER JOIN category c
ON c.id = pc.category_id
WHERE
(c.discount_amount > 0 OR c.discount_percent > 0)
AND (c.`discount_start_date` <= NOW() OR c.`discount_start_date` IS NULL)
AND (c.`discount_end_date` >= NOW() OR c.`discount_end_date` IS NULL)

Here is the EXPLAIN result from the live database:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c index_merge PRIMARY,category_discount_start_date,category_discount_end_date,category_discount_amount,category_discount_percent category_discount_amount,category_discount_percent 8,8 NULL 10 Using sort_union(category_discount_amount,category_discount_percent); Using where
1 SIMPLE pc ref category_id,product_id category_id 4 lollipop_site.c.id 19
1 SIMPLE product eq_ref PRIMARY PRIMARY 4 lollipop_site.pc.product_id 1

and here is the EXPLAIN result from the test database:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE product ALL PRIMARY NULL NULL NULL 1
1 SIMPLE pc ALL category_id,product_id NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE c eq_ref PRIMARY,category_discount_start_date,category_discount_end_date,category_discount_amount,category_discount_percent PRIMARY 4 lollipop_sandbox.pc.category_id 1 Using where

I am using MariaDB inside docker version is 10.7.3-MariaDB-1:10.7.3+maria~focal.

I'm hoping someone can shed some light onto why the server is using a different query plan for the same query on the same data just being in different databases.

Note this query was previously using WHERE id IN (SELECT product_id FROM... style query and I converted it as recommended by other stackoverflow answers. This install has a number of those queries that are also having this problem.

Improve this question

2 Answers 2

Reset to default
1

Having a similar issue with query not using primary keys, in my case on two different mariadb server. Maridb is on exactly same version, also the configuration is the same. Here is my query:

select .....
    from model_number mn
             inner join manufacturer m on (mn.manufacturer_id = m.id)
             inner join product_type pt on (mn.product_type_id = pt.id)
             inner join user cu on cu.id = mn.created_by
             inner join user uu on uu.id = mn.updated_by
             inner join replacement r on (mn.id = r.model_number_by_id)
             inner join mapping ma on r.model_number_by_id = ma.model_number_id and r.physical_item_type_id = ma.physical_item_type_id
    where r.model_number_id = 1355
      and r.physical_item_type_id = 4

indexes (primary keys) are not using for m, pt, cu and uu.

also the order in query plan is different:

server using primary keys:     r,mn,uu,pt,m,cu,ma
server not using primary keys: r,m,pt,cu,uu,mn,ma

I have no glue what is wrong

Here is query plan from the server where all works as expected:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE r ref PRIMARY,fk_replacement_model_number_by,fk_replacement_physical_item_type fk_replacement_physical_item_type 8 const,const 3 Using index
1 SIMPLE mn eq_ref PRIMARY,model_number_unq_idx2,fk_model_number_user_c,fk_model_number_user_u,fk_model_number_product_type PRIMARY 4 vat_warehouse.r.model_number_by_id 1 Using where
1 SIMPLE uu eq_ref PRIMARY PRIMARY 1 vat_warehouse.mn.updated_by 1
1 SIMPLE pt eq_ref PRIMARY PRIMARY 4 vat_warehouse.mn.product_type_id 1
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 vat_warehouse.mn.manufacturer_id 1
1 SIMPLE cu eq_ref PRIMARY PRIMARY 1 vat_warehouse.mn.created_by 1
1 SIMPLE ma ref old_mapping_uniq_idx,fk_old_mampping_physical_item_type old_mapping_uniq_idx 8 vat_warehouse.r.model_number_by_id,const 1 Using index

and here is a query plan from server where indexes are not used:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE r ref PRIMARY,fk_replacement_model_number_by,fk_replacement_physical_item_type fk_replacement_physical_item_type 8 const,const 1 Using index
1 SIMPLE m ALL PRIMARY NULL NULL NULL 1 Using join buffer (flat, BNL join)
1 SIMPLE pt ALL PRIMARY NULL NULL NULL 1 Using join buffer (incremental, BNL join)
1 SIMPLE cu ALL PRIMARY NULL NULL NULL 1 Using join buffer (incremental, BNL join)
1 SIMPLE uu ALL PRIMARY NULL NULL NULL 1 Using join buffer (incremental, BNL join)
1 SIMPLE mn eq_ref PRIMARY,model_number_unq_idx2,fk_model_number_user_c,fk_model_number_user_u,fk_model_number_product_type PRIMARY 4 vat_warehouse.r.model_number_by_id 1 Using where
1 SIMPLE ma ref old_mapping_uniq_idx,fk_old_mampping_physical_item_type old_mapping_uniq_idx 8 vat_warehouse.r.model_number_by_id,const 1 Using index
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks looks like I'm not the only one with this issue. For a temporary (maybe permanent later) solution I've installed Percona on another server and using that for the second database. It's working as expected for using the indexes.
I had the same issue, at first we thought it was the version discrepancy we had a mariadb 10.6.5 and 10.7 with: - 6.5 generating a query plan leveraging the index, very efficient and fast execution - 7 generating a query plan non leveraging the index, slow execution. we hot swapped to similar version and we kept having this issue randomly even with the same mariadb configuration file. this is pretty much a no go for mariadb in the company i work for and we are already looking for an alternative.
0

Hi everyone who comes across this, I upgraded to 10.11.6 and it is no longer an issue. It could have been fixed in another interim version.

Improve this answer

1 Comment

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.