2. PHPRunner General questions
  3. [SOLVED]  View Query Optimization
This topic is locked
[SOLVED]

 View Query Optimization

 		5/24/2012 3:42:41 PM
PHPRunner General questions
S
 Stucco author

Hello,
I have a situation where page views are performing very slowly. Especially the item view query. I turned on mysql logging and captured the query to view an individual item, and was surprised to see it not selected by id!
The query sent by PHPRunner is this:

SELECT

`id`, `name`, `visible`

FROM `customers` WHERE (id<4 or id IS NULL)

ORDER BY 1 DESC

limit 1


Why not this?

SELECT

`id`, `name`, `visible`

FROM `customers` WHERE (id=4)


The problem is when I have 45k records in a table it must sort them all before just taking the limit 1. This makes what should be a very simple query take about 20 seconds. This increases the tmp filesize drastically, and ends up causing a MySQL error "Incorrect key file for mapping."
Is there some way to change this behavior? It is even more complicated if I add my own ORDER BYs.
Thank you so much for any help!
Sergey Kornilov admin 5/24/2012

Remove Prev/Next buttons from the View page.
Another option is to index this table by ID field.
S
 		Stucco author 5/24/2012

I should have revealed that I am using PHPRunner 5.3.
I am not sure how to remove the next and previous buttons besides simply deleting them from the edit page. Is there a preferred way?
I would prefer to index this table by ID than to remove next and previous, however, the table's primary key is ID already, so there is an inherent index in the database already. Is there a corresponding action necessary in PHPRunner? On the pages tab, the ID field is the only field selected as the key column.



Remove Prev/Next buttons from the View page.
Another option is to index this table by ID field.
Sergey Kornilov admin 5/24/2012

Proceed to Miscellaneous screen and turn of Next/Prev buttons option for the table in question.
S
 		Stucco author 5/24/2012

Thank you. After unchecking "Move Next Option" on the Misc page my queries changed to be like

SELECT `id`, `name`, `visible` FROM customers where id=4