what is the best way to get an approximate number of search results from a query?

Question

to describe it some more, if i have an image map of a region that when clicked, a query is performed to get more information about that region.

my client wants me to display an approximate number of search results while hovering over that region image map.

my problem is how do i cache? or get that number without heavily exhausting my server's memory resources?

btw im using php and mysql if that's a necessary info.

Answer 1

You could periodically execute the query and then store the results (e.g., in a different table).

The results would not be absolutely up-to-date, but would be a good approximation and would reduce the load on the server.

Answer 2

You could create another table with the id's of the regions, and then create a script that runs over all the regions at a slow time (night for example) and populates this extra table with the data.

Then when you hover you get the ID from this new table, and its at most a day old.

The issue with that could be that you do not have a slow time or that the run-through that is done at night takes a long time and is very process heavy.

EDIT to your comment.

Take a large region or country if you can, do a query of that region within your SQL browser of choice and check out the time it would take.

If it is to much you could distribute it so certain countries will execute at certain hours, small countries together and large countries alone at some period.

Answer 3

MySQL can give you the approximate number of rows that would be returned by your query, without actually running the query. This is what EXPLAIN syntax is for.

You run the query with 'EXPLAIN' before the 'SELECT', and then multiply all the results in the rows column.

The accuracy is highly variable. It may work with some types of queries, but be useless on others. It makes use of statistics about your data that MySQL's optimizer keeps.

Note: using ANALYZE TABLE on the table periodically (ie once a month) may help improve the accuracy of these estimates.

Answer 4

Is your concern that you don't want to go to the database for this information at all when the roll-ever event occurs, or is it that you think the query will be too slow and you want the information from the database, but faster?

If you have a slow query, you can tune it, or use some of the other suggestions already given.

Alternatively, and to avoid a database hit altogether, it seems that you have a finite number of regions on this map, and you can run a query periodically for all regions and keep the numbers in memory.