1

have an issue with MariaDB 10.2.12 running in aws rds (single master, no slave)

when I run prepared statement using a variable substitution

PREPARE stmt1 FROM 
"select ...quite complex select from tenth of tables..
 and `subscribers`.`id` = ?
 group by `subscribers`.`id`
 limit 1";
SET @a = 77586744;
EXECUTE stmt1 USING @a;  
DEALLOCATE PREPARE stmt1;

it takes 13 minutes to process (those tables have millions rows)

however, if I run the very same query without variableS:

SET @sql = CONCAT("select ...quite complex select from tenth of tables..
 and `subscribers`.`id` = ",77586744,"
 group by `subscribers`.`id`
 limit 1");
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;  
DEALLOCATE PREPARE stmt1;

it only needs 0.05s to run. The same time it takes if I not use PREPARE at all.

According to profiler output the long run involves several "converting HEAP to Aria" with reindexing and sorting it, which do not take place if no variable involved.

Why do this converting happen? it seems there is no lack in tmp/heap since the query runs in 0.05s, thus it's "USING variable" changes something I don't know.

btw, we've faced the issue running application on PHP 7.1.20 with Laravel Framework 5.6.39, so I cannot just rewrite a query to get rid of "USING variable" as it something that happens under the hood of PDO.

0.05s 769.20s

UPD: EXPLAIN for slow query enter image description here

EXPLAIN for fast one enter image description here

OK, explain differs, but it's kind of expected from profilier output. The thing I still don't get is why it differs while the query stays the same.

  • This is not the first time I have seen @variables take longer than plain numbers. Alas, I have not gotten to the bottom of the problem. Meanwhile, notice how useless the profile is -- 99.9% of the time is in "Sending data" -- very common; very uninformative. – Rick James Dec 14 '18 at 19:12
  • It may be tricky to get, but try to get the EXPLAIN from each case. The slow one should be easy with MariaDB's way of EXPLAINing a currently running query. – Rick James Dec 14 '18 at 19:13
  • 2
    moreover, it's unclear "Sending data" where? besides, the query has "limit 1". I suspect it has smth to do with converting to Aria on-disk cache . – Dmytro Kh. Dec 14 '18 at 19:40
0

Is subscriber_tag a many:many mapping table? Ditto for segmentation_tag? If so, there may be several ways to speed up the use of such on both versions. See here.

  • After all, only reformatting the query helped. still have no answer on why it chooses different plans for the same query depending on variables in execute. – Dmytro Kh. Dec 17 '18 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.