Browse speed in a large table

Browse speed in a large table

Postby miketee » Thu May 05, 2016 1:30 pm

I have a table with a large number of rows and the delay building the unfiltered view in the browse form is significant.
I have attempted to solve this by prefiltering the view before opening the browse form.

I have added a text field next to the browse button, entering a filter string there before pressing the button.
The "Default Filter for Browse" field of the button is set to the name of the text field. When I press the browse button, the browse form opens as expected, and
the records are filtered correctly.

However, if I now want to change the filter string by typing into the browse form search field, I get a filtered view of the filtered table. What I would like is for
the new search to run on the whole table, not the prefiltered view.

Any suggestions about how to do this correctly?
miketee
 
Posts: 9
Joined: Thu May 05, 2016 12:55 pm

Re: Browse speed in a large table

Postby admin » Thu May 05, 2016 3:30 pm

miketee,

Are you joining more than 1 table?

Do you have indexes on any fields?


Steven
admin
Site Admin
 
Posts: 3233
Joined: Mon Jun 15, 2009 9:53 am

Re: Browse speed in a large table

Postby miketee » Thu May 05, 2016 4:07 pm

Steven,

It's a single table, but has 500k records. I have primary key and 3 other fields indexed, individually.
miketee
 
Posts: 9
Joined: Thu May 05, 2016 12:55 pm

Re: Browse speed in a large table

Postby admin » Thu May 05, 2016 4:46 pm

miketee,

You say 'view', do you mean a mySQL 'View' or a single Table?

Have you hard coded an 'order by' in the sql for that browse?

How old is the server you are using?

Steven
admin
Site Admin
 
Posts: 3233
Joined: Mon Jun 15, 2009 9:53 am

Re: Browse speed in a large table

Postby miketee » Fri May 06, 2016 3:06 pm

Steven,

It's a single table, no ORDER BY, brand new server installation.

If we leave the speed issue aside for now, what is the correct way to give the user a pre-filtered browse screen, but then allow the user to change the browse filter so that the pre-filter string is overridden by the browse screen search string? ie the user is in full control of the filtering.
miketee
 
Posts: 9
Joined: Thu May 05, 2016 12:55 pm

Re: Browse speed in a large table

Postby admin » Thu May 12, 2016 3:24 pm

admin
Site Admin
 
Posts: 3233
Joined: Mon Jun 15, 2009 9:53 am


Return to General



cron