Speeding up table with about 120000 records

lyleholc · Post by **lyleholc** » Sat Jul 27, 2013 12:26 pm

Hi All,

I have a table of about 120,000 records which are mostly chemical products all with 100 byte varchar for the 'name'. I've indexed the 'name' and looked at a lot of ideas on the web,but I can't really find a solution. It's taking about 20 seconds to retrieve a record that's an exact match, and longer if I use 'between' or something get a broader result set. If I run the query in phpmyadmin, the query finishes in less than a tenth of a second, so I don't think it's database or computer related. When I request the same record using the application I built in PHPMaker, it takes 20 or more seconds before the record is displayed in the grid once the Search is started. I'm trying to figure out what I could do to speed things up.

Any suggestions on what I could look into?

Thanks greatly,

Lyle

mpol_ch · Post by **mpol_ch** » Sat Jul 27, 2013 3:11 pm

Hello

I will suggest to check the lookup connection in your tabel fields with phpmaker. This helped me to speed up the loading time in PhpMaker.

mpol_ch

mobhar · Post by **mobhar** » Sun Jul 28, 2013 11:15 pm

Did you use Quick Search instead of Advanced Search?

I suggest you to include the field(s) which has/have only been indexed either for Quick or Advanced Search. In other words, try to not using Quick Search since it will search the data in all fields (including the ones that have not indexed, yet). This will need longer time to return the search result.

So, try to add the "name" field into the Extended Search and disable other fields for Quick Search.

lyleholc · Post by **lyleholc** » Mon Jul 29, 2013 1:34 am

Hi All,

Thank you. I tried both with/without Quick Search. Going to play with it some more.

Much appreciated.

Lyle