使用pdo_sqlite时，SQL查询中的'LIKE'运算符非常慢

Question

I found out that the 'LIKE' operator in a 'SELECT' SQL query is very slow with pdo_sqlite (PHP 5.3 or PHP 5.4).
The same query entered in the sqlite3 binary is way faster.

Sample code :

<?php
    $bdd = new PDO('sqlite:./chaines_centre.db');
    $reponse = $bdd->prepare("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = ? and NomChaine like 'DCLC257__' order by DateMonteeAuPlan DESC limit 20;");
    $reponse->execute($_GET['job']);
    while ($donnees = $reponse->fetch())
    {
        // whatever...
    }
    $reponse->closeCursor();
?>

---

Here is the quick "benchmark" I made with :

1. XDebug Trace for pdo_sqlite measure
2. SQLite binary with '.timer on'

NomChaine like 'DCLC257__' :
● pdo_sqlite : 1.4521s ✘
● sqlite3 binary : 0.084s ✔

NomChaine like 'DCLC257%' :
● pdo_sqlite : 1.4881s ✘
● sqlite3 binary : 0.086s ✔

NomChaine = 'DCLC25736' :
● pdo_sqlite : 0.002s ✔ (it's a bit longer i think, but very fast)
● sqlite3 binary : 0.054s ✔

---

How can I improve this situation ?

---

---

EDIT : Maybe I focused too much on the 'LIKE' operator.

<?php
$bdd = new PDO('sqlite:./chaines_centre.db');


$time_start = microtime(true);
$reponse = $bdd->query("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20;");
$time_end = microtime(true);

$time = $time_end - $time_start;
echo "Situation 1 : $time second(s)<br><br>";
// Output : 1.3900790214539 second(s)


$time_start = microtime(true);
$reponse = $bdd->query("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20;");
$time_end = microtime(true);

$time = $time_end - $time_start;
echo "Situation 2 : $time second(s)<br><br>";
// Output : 0.0030009746551514 seconde(s)


$time_start = microtime(true);
$reponse = $bdd->query("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by DateMonteeAuPlan DESC limit 20;");
$time_end = microtime(true);

$time = $time_end - $time_start;
echo "Situation 3 : $time second(s)<br><br>";
// Output : 0 seconde(s)
?>

By removing the LIKE operator or order by DateMonteeAuPlan, the query is executed in an expected time...
It's so strange. o_O

Answer 1

Did you by any chance run the PDO vs binary in the same script (one after other)? If you did, then it would be normal to get better results with binary because PDO runs when cache is empty (so it hits the disc) while binary gets the data from RAM.

For your second script, that's certainly the case: first query gets 1.3+ seconds because it also reads the data, while the rest get the data from RAM.

See http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragma-cache_size for details.