Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17150

Bind variables using PHP with named placeholders

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            Unassigned Unassigned
            hjn1966 Henrik Juul-Nyholm
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.