From: dangerousdave86 at hotmail dot com Operating system: Windows XP SP2 + Apache 2 PHP version: 5.1.4 PHP Bug Type: SQLite related Bug description: SQLite query performing poorly from within PHP
Description: ------------ My simple, expandable query doesn't like being simple in PHP5.1.4, or 5.1.2. "SELECT files.file_location AS file_location, files.file_id AS file_id, count(files.file_id) AS count FROM (SELECT file.file_location AS file_location, file.file_id AS file_id FROM property INNER JOIN file ON (file.file_id = property.file_id) WHERE (property.value LIKE '%sometext%')) AS files GROUP BY files.file_id ORDER BY count DESC" This query takes 7 seconds to execute in PHP, but returns instantly with results from the SQLite executable. SQLite3.exe. I am using the PDO SQLite for access to version 3 databases. when extending the query with an extra sub-query, the query suddenly executes in milliseconds. "SELECT files.file_location AS file_location, files.file_id AS file_id, count(files.file_id) AS count FROM (SELECT file.file_location AS file_location, file.file_id AS file_id FROM property AS property INNER JOIN file AS file ON (file.file_id = property.file_id) WHERE (property.value LIKE '%sometext%') UNION SELECT file.file_location AS file_location, file.file_id AS file_id FROM property AS property INNER JOIN file AS file ON (file.file_id = property.file_id) WHERE (property.value LIKE '%sametext%')) AS files GROUP BY files.file_id ORDER BY count DESC" I can't see how this is happening. The query is generated, so there are no typos or differences between SQL. The derived tables are identical. There are reasons behind why the SQL is laid out as it is and I'm not using ORs. But anyway, this behaviour is most unusual, and like I say, not reproducable from the SQLite command line. The first query executes in roughly 7 seconds, while the second executes in 1/100th of a second. -- Edit bug report at http://bugs.php.net/?id=37912&edit=1 -- Try a CVS snapshot (PHP 4.4): http://bugs.php.net/fix.php?id=37912&r=trysnapshot44 Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=37912&r=trysnapshot52 Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=37912&r=trysnapshot60 Fixed in CVS: http://bugs.php.net/fix.php?id=37912&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=37912&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=37912&r=needtrace Need Reproduce Script: http://bugs.php.net/fix.php?id=37912&r=needscript Try newer version: http://bugs.php.net/fix.php?id=37912&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=37912&r=support Expected behavior: http://bugs.php.net/fix.php?id=37912&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=37912&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=37912&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=37912&r=globals PHP 3 support discontinued: http://bugs.php.net/fix.php?id=37912&r=php3 Daylight Savings: http://bugs.php.net/fix.php?id=37912&r=dst IIS Stability: http://bugs.php.net/fix.php?id=37912&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=37912&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=37912&r=float No Zend Extensions: http://bugs.php.net/fix.php?id=37912&r=nozend MySQL Configuration Error: http://bugs.php.net/fix.php?id=37912&r=mysqlcfg
