Estimating query results quantity

[urms]

I have a huge table that is displayed using pagination and certain queries, that is the total results pages/items quantity depends on the queries and always is different. These queries work too slow and maybe anyone can advise a way to optimize this process maybe by making an estimation of the results number. E.g. I saw Google just shows Results 11 - 20 of about 40,100,000 How do they do it?

[Demaestro]

They do it with Python.

Pagination has a lot of extra processes to run so that it can be dynamic for different use cases.

Your best bet is to write a custom page in PHP or Python or something that will "page" your DB results. Maybe look into caching queries as well if the data isn't updated that often. That way many people can use the results of one query to the DB.

Caching queries is built in to many databases so you can check your documentation for that.

Are you able to write a page that outputs the results yourself?

[edited by: Demaestro at 3:44 pm (utc) on Dec. 18, 2007]

[urms]

Yes, sure. That's been done and I enabled query cache for mysql server. Still since people use some complex queries like searching products of certain brand and type (there are many options available) they will get not cached results and it will look slow.

[Demaestro]

You should do some testing to see what is slow.. the query... or the rendering of the page using the output of the query.

If it is the query itself then you may have to optimize what ever SQL is being generated.

If the queries runs fast then I would write my own code and scrap pagination.