2. PHPRunner General questions
  3. [SOLVED]  search list with over 1 million records
This topic is locked
[SOLVED]

 search list with over 1 million records

 		3/5/2021 11:05:00
PHPRunner General questions
S
 SVblue author

Hello,
I've got a table with only 6 fields but at this time with more then 1.2 million records.

Now I'd like to search within an indexfield, shown on a list page (hide data until search is active).

The result can only be one record.
edit sql query
SELECT



FROM tablex
So, when the user starts searching it tooks over 15 seconds until he gets the result.
If I change the query to, lets say:
SELECT



FROM tablex

WHERE id>800000
(and the id of the searched record is more than 800000 of course)

it is much more faster.
But, this way is not an option because I don't know what the user is looking for of course.
Is there a way to pass the users search entry directly to the sql query?
Sergey Kornilov admin 3/5/2021

The typical approach is to add indexes to all searchable fields and it will make your search much faster. More info here, "SQL Query" section:

https://xlinesoft.com/blog/2011/04/08/improving-perfomance-of-phprunnerasprunnerpro-applications/
S
 		SVblue author 3/5/2021

Index is set to the field.
It is fast when using "WHERE" statement in the query.

e.g.

SELECT



FROM tablex

WHERE indexedField=293743
But, the user will use the search options.

He can chose the field "indexedField" and type "293743"...

When start the search it seems that the statement from

edit sql query



SELECT



FROM tablex;
is executing (get all records [more the 1 million]), and then it is searching for "indexedField=293743".
Sergey Kornilov admin 3/5/2021

You are confusing things here. PHPRunner does not retrieve all records before searching for a specific record.
S
 		SVblue author 3/8/2021

If I use

SELECT



FROM tablex

WHERE indexedField=293743*
on a simple php-site, the result tooks only milliseconds.

Oh, and using the same query within 'BeforeQueryList'... also very fast.
But, using the search of phprunner it tooks at least 15 seconds.
K
 		keithh0427 3/8/2021

When you setup the search options for indexedField, did you select only the "Equals" checkbox? ?Are you using any Filters?
I have a table with over a million records and am not experiencing this issue.
S
 		SVblue author 3/8/2021



When you setup the search options for indexedField, did you select only the "Equals" checkbox? ?Are you using any Filters?
I have a table with over a million records and am not experiencing this issue.


I only use "Equals"... but, you put my on the right way.

"Show Search Suggest"... I disabled this option on the "Search and Filter settings" an phprunner runs. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=94021&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
When thinking about it it makes sense... thirst collect suggest-values... that, of course, takes a lot of time.
Maybe it helps other users or it can be mentioned in the linked blog above!
Thank you, VERY much! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=94021&image=2&table=forumreplies' class='bbc_emoticon' alt=':D' />
Edit: activate "Case-sensitive search" is also a point which make it faster!