php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #37912 SQLite query performing poorly from within PHP
Submitted: 2006-06-26 05:48 UTC Modified: 2006-06-26 16:36 UTC
From: dangerousdave86 at hotmail dot com Assigned:
Status: Not a bug Package: SQLite related
PHP Version: 5.1.4 OS: Windows XP SP2 + Apache 2
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: dangerousdave86 at hotmail dot com
New email:
PHP Version: OS:

 

 [2006-06-26 05:48 UTC] dangerousdave86 at hotmail dot com
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.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-06-26 07:18 UTC] tony2001@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc.

If possible, make the script source available online and provide
an URL to it here. Try to avoid embedding huge scripts into the report.


 [2006-06-26 15:58 UTC] dangerousdave86 at hotmail dot com
I cant really give example code as it would require a database or a large amount of code to create one.
I've traced the problem to the 'GROUP BY' clause in the query. Without it the slow query executes equally as fast as the first.

I think the problem probably lies within SQLite and not PHP. An updated SQLite client library would probably do for seeing if this is the case.
 [2006-06-26 16:08 UTC] tony2001@php.net
>I've traced the problem to the 'GROUP BY' clause in the query.
Doesn't look like something related to PHP, but likely to be SQLite problem.
 [2006-06-26 16:11 UTC] dangerousdave86 at hotmail dot com
SQLite is currently on version 3.3.6, while PHP appears to be using 3.2.5, being 11 releases old this could well be the real issue.
 [2006-06-26 16:19 UTC] tony2001@php.net
Actually, it's 3.2.8 (which is 6 months old) in 5_2 branch and you can always use external SQLite library with PHP (though, not on windows apparently).
I'm sure we'll update SQLite soon to the latest version, but I don't think anybody can help you until then.
Anyway, it's not PHP problem -> "bogus".
 [2006-06-26 16:36 UTC] dangerousdave86 at hotmail dot com
Is there anyway to request an update, or is it definatly wait and see.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Nov 14 11:01:32 2024 UTC