Searching very slow with large DB table

Applications Search
#1

I created a Grid application and set it to Start By Search and selected the fields to display from the mySQL database table. When I run the Grid application using a table that contains over 1 1/2 million records, it take a very long time to return the search results. If I execute the SQL query directly on the database using phpmyadmin or other 3rd party db tool, it returns the results in second instead of more than 10 minutes. I was wondering if I may be doing something wrong when I created the Grid application. I think the mySQL server should be doing the search work instead of the Grid application. I currently have a View stored in the database that I use to manually find records. I wanted to create a Front-End application that can be run using a browser to find records. Would a Stored Procedure be a recommended way to pass the search arguments to the SP?

Thanks in advance for any help that anyone can provide.

Paul

#2

In principle there’s a sql statment generated which is then executed. However, the resultset is stored in a temporary table, which is different then if you do a select in a query tool which only needs to dump the record. This resultset is indexed on the order-by and it takes time to create this temporary table. It is needed because you can scrolll forward and backwards in the grid, by using paging.
It might not be the full story, because it depends on the size of the resultset. But at least it’s a part of the answer.