Mysql count rows using filters on high traffic database

Question

Let's say you have a search form, with multiple select fields, let's say a user selects from a dropdown an option, but before he submits the data I need to display the count of the rows in the database .

So let's say the site has at least 300k(300.000) visitors a day, and a user selects options from the form at least 40 times a visit, that would mean 12M ajax requests + 12M count queries on the database, which seems a bit too much .

The question is how can one implement a fast count (using php(Zend Framework) and MySQL) so that the additional 12M queries on the database won't affect the load of the site .

One solution would be to have a table that stores all combinations of select fields and their respective counts (when a product is added or deleted from the products table the table storing the count would be updated). Although this is not such a good idea when for 8 filters (select options) out of 43 there would be +8M rows inserted that need to be managed.

Any other thoughts on how to achieve this?

p.s. I don't need code examples but the idea itself that would work in this scenario.

Answer 1

I would probably have an pre-calculated table - as you suggest yourself. Import is that you have an smart mechanism for 2 things:

1. Easily query which entries are affected by which change.
2. Have an unique lookup field for an entire form request.

The 8M entries wouldn't be very significant if you have solid keys, as you would only require an direct lookup.

I would go trough the trouble to write specific updates for this table on all places it is necessary. Even with the high amount of changes, this is still efficient. If correctly done you will know which rows you need to update or invalidate when inserting/updating/deleting the product.

Sidenote: Based on your comment. If you need to add code on eight places to cover all spots can be deleted - it might be a good time to refactor and centralize some code.