PHP/MySQL Loading Image

Question

Hey guys,

Hopefully a simple one for you, i don't have a clue how to do this and i've tried so many ways and none are successful:

I have a website which runs an SQL statement over several Database tables using a simple SQL statement "SELECT * FROM x WHERE field = posteddata" however this can sometimes take a while and i want to show a simple animated gif of a faux status bar while running the script as a kind of pre-loader until the results have fully loaded?

Regards

Answer 1

This is definitely not a straightforward one.

First of all, make sure you've done everything to make sure your database is optimally set up. Especially, create indexes on the tables you are querying. Indexes can dramatically improve the performance of database queries. Make sure you have indexes on all foreign key columns, and all columns you are putting in the where query. Use composite indexes (indexes consisting of multiple columns) where appropriate. Use EXPLAIN to see how your query is run.

If this still doesn't solve your problem, what you need to do is spawn a process in the background which does the querying. This process would, for example, output it's status to a temporary file somewhere as it's querying.  The simplest way to show the user what's going on is to have a php page with an autorefresh of say, 5 seconds. This page can then read the temporary file created by the spawned process, and show what's going on. When the temporary file indicates that the operation is done, you can then show the results. Alternatively, you can store the status in your database.

An alternative is to use AJAX. Either way, you will need a background process.

Good luck

Answer 2

Have you tried tuning the database ?

Are there indexes on the database fields that you select by ?

and by the way, you can not run a query over several tables using a simple SQL statement, you must use a join or a union.