This topic is locked

slow search after upgrade to phprunner 10.5

6/25/2021 5:25:04 AM
PHPRunner General questions
M
ManniS author

Hi all,
i use PHPrunner with mysql on linux. After an Upgrade to 10.5 , the quick search in the listpage goes very slow. it takes 5 to 10 sec.
with 10.4 is it very fast. I found out the problem comes from a concatenated Field. I concat 3 Fieldvalues from a other table to 1 field.
if i use this field without linking more values ( use only 1 value) the search is on 10.5 ok.
Since I need to link the values, I currently stay on 10.4, but would like to use the new functions of 10.5.
Does anyone know a solution that the search will be faster, or there may be an error in 10.5?

Manni

fhumanes 6/26/2021

I think the product version has included variations in the construction of the SELECT of that page.

From my point of view, you should see the SELECT that runs and analyze it.

I advise you to use the information of this article: https://fhumanes.com/blog/guias-desarrollo/guia-12-phprunner-optimizar-accesos-a-mysql/

Greetings,
fernando

admin 6/30/2021

What Fernando says, you need to see SQL query of both apps to notice the difference.

Most likely in 10.5 you are using a feature that doesn't exist in version 10.4 and some database indexes are missing making this feature slow.

M
ManniS author 7/20/2021

Hi all,
I have now recorded the sql query
they are some difference and 10.5 has a line more on head
on 10.5 the query is longer and i see on the timestampe the slow query.
no changes were made to the project, only created once with 10.4 and 10.5

img alt
thx
Manni

admin 7/20/2021

So, what is the SQL query that runs slow?

M
ManniS author 7/20/2021

its the upper log .
it takes 6 or more seconds on a local system, 10 and more sec ( up to 20-30 sec. or more with load on server). on the Lan. The 10.4 build runs shorter then 1 sec on lan or local server ( also with load) .
I think that it could also be in the php area
I tested again as soon as I no longer join the fields together, so only use one field, it is faster again.
Update!..
only the quicksearch is slow. search in the search window is also fast. If i search customer field ( concated with 2 others fields) in the quicksearch 10 sec, search in customer field 1 sec
img alt

A
acpan 7/20/2021

SQL search on concatenated fields will not use the index and will perform full table scan, try to select individual fields than concatenate fields.

Read this

admin 7/23/2021

What @acpan says. Another option is to add a new index on the concatenated expression.

A
acpan 7/23/2021

Yes, @admin on the concat index. Proper index is most important when comes to speed. You need to ensure that esp with a complex query.

Also you should probably cut out the query that is slow in TEXT form, and place it here to examine or run EXPLAIN SQL to analyze the index efficiency.

img alt

The slow part is highlighted, 6 sec longer on the upper log, in which the sql statement seems not the same with the lower one, it is "thicker" than the lower one, and i can see the left-join statement on the lower log but not the upper log.

As said, no one can tell by looking at screenshots full of characters, need to see how the SQL is constructed and if the two are exactly the same, i.e. only PHPR version different and you did not change anything, sometimes we change something but we may forget. eg. we may just check on one more quick search field, that is not indexed and that alone can cause a full table scan when it forms part of the query statement.

HJB 7/26/2021

img alt