[MDEV-17150] Bind variables using PHP with named placeholders Created: 2018-09-06  Updated: 2018-10-23  Resolved: 2018-10-23

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.1.35, 10.2.16
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Henrik Juul-Nyholm Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: innodb, need_feedback
Environment:

PHP 5.6 on Linux



 Description   

(UPD: psergey edited a bit to make more readable)
I have this PHP:

	$ts1 = microtime(true);
 
	foreach($iParamsArr as $key => $value)
	{							
		$stmt -> bindValue(':' . $key, $value['value'], $value['PDO']); 
	}
 
	$ts2 = microtime(true);
	echo 'Prepare: ' . intval(($ts2 - $ts1) * 1000) . "<br>";
						
	$stmt->execute();
 
	$ts3 = microtime(true);
	echo 'Execute: ' . intval(($ts3 - $ts2) * 1000) . "<br>";

No problem when sending this:

SELECT * FROM `V_T2` WHERE `D_T2-V7-1` = :F1 GROUP BY `S_T2` ORDER BY `S_T2` DESC

Performance fluctuating from 0.01 up to 160 s when sending this:

SELECT * FROM `V_T2` WHERE `S_T2` = :id AND `D_T2-V7-1` = :F1 GROUP BY `S_T2` ORDER BY `S_T2` DESC

It is the execute() taking time.

There is an index on S_T2, but it doesn't make any difference removing it. ANALYSE TABLE doesnt make any difference. FLUSG QUERY CASHE neither.

The issue is hard to reproduce as repeated queries with the same parameters values run fast. Only when you are change :id parameter repeatedly the problem comes after several iterations, and then persists.



 Comments   
Comment by Sergei Petrunia [ 2018-09-07 ]

Difference between OK and problem queries:

SELECT * FROM V_T2 WHERE                D_T2-V7-1 = :F1 GROUP BY S_T2 ORDER BY S_T2 DESC
SELECT * FROM V_T2 WHERE S_T2 = :id AND D_T2-V7-1 = :F1 GROUP BY S_T2 ORDER BY S_T2 DESC

Comment by Sergei Petrunia [ 2018-09-07 ]

Is V_T2 a View? (If yes, can you provide its definition?)

Does the server have Slow Query Log enabled? If not, is it possible to turn it on, with log_slow_verbosity=query_plan,explain ? Then, the slow query should be caught into the log, and we are interested to see what gets printed there.

Generated at Thu Feb 08 08:34:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.