2. PHPRunner General questions
  3. Slow queries on large databases
This topic is locked

Slow queries on large databases

 		12/30/2009 1:35:25 PM
PHPRunner General questions
B
 Bert author

Queries get quite slow on large (text) databases using contains (LIKE).

I think the problem occurs because by default the query runs through the whole database and displays the number of matches and pages found. I think it would be a good thing to be able to turn this on and off and let the tool limit the search using the number of record per page.
Bert
Sergey Kornilov admin 12/30/2009

Bert,
no, it doesn't retrieve the whole database. It retrieves as many data as required to display on the page plus the second COUNT() query to get the number of records.
My first suggestion is to print SQL queries on the web page and run them against your database manually. You may want to switch to FULLTEXT search instead of LIKE.
Read this article that explains how to turn on debug mode:

http://www.xlinesoft.com/phprunner/docs/debugging_tips.htm
B
 		Bert author 12/30/2009

Thanks for the tip.
The original query executed is

SELECT id, len, src, tgt FROM `en-nl` where 1=0 or upper(src) like upper('%echt%') or upper(tgt) like upper('%echt%') ORDER BY 1 ASC



If I run this in phpmyadmin it is slow too (10 seconds) also but when I change it to

SELECT id, len, src, tgt FROM `en-nl` where 1=0 or upper(src) like upper('%echt%') or upper(tgt) like upper('%echt%') ORDER BY 1 ASC LIMIT 30



it stops after retrieving 30 matches and is very fast (10ms) but we don't know how many matches there would be (and hence how many pages). But this is not a problem for me and others querying the database.
Hope this helps to clear the issue,
Bert
(I can make the db available to you if needed for testing)
Sergey Kornilov admin 12/30/2009

Something is not right here is PHPRunner adds LIMIT clause automatically.
You can post your application to Demo Account and open a ticket at http://support.xlinesoft.com sending your application URL.