Geek Answers Handbook

Partitioning very large datasets

I have a dataset in MySQL, where using a limit is already an expensive query, and finding the number of results is also expensive. So I would like to avoid another query to find the number of results. I cannot use MYSQL_CALC_FOUND_ROWS because the limit is inside the subquery:

SELECT * FROM items, ( SELECT item_id FROM (etc) WHERE some.field=<parameter> AND (etc) GROUP BY (something) ORDER BY (something_else) DESC LIMIT 15 ) subset WHERE item.id=subset.item_id

I could leave the connection elements and end the subquery and then use MYSQL_CALC_FOUND_ROWS, however this is very, very, slow. I tried index optimization and let me just assume that this is out of the question.

Now this is becoming a design issue ... How can I allow the user to view this data when I do not know the last page ? I only know if they went too far (for example: the query returns no results).

+3
source share
2 answers

Here is a summary of the article by MySQL guru Baron Schwartz:

http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/

Four ways to optimize paged displays

  • In the first query, all results are retrieved and cached.

  • Do not show all results. Even Google does not allow to see the millionth result.

  • Do not show the total score or intermediate links to other pages. Show only the "next" link.

  • Estimate the number of results. Again, Google does this and no one complains.

+6
source

To reinforce this, the concept of "how much" is in any case extremely transient; by the time the response reaches the user, this could easily be mistaken.

0
source

More articles:


All Articles