我可以使用MySQL临时表来存储搜索结果吗？

Question

I have a search page written in PHP, and it needs to search in the MySQL database, and the result need to be sortable. This search page will be accessed by many users (>1000 at any time).

However, it is not feasible to sort the search result in MySQL, as it would be very slow.

I'm thinking of storing each search result into a temporary table (not MySQL temporary table), and the table name is stored inside another table for reference like this:

| id | table_name | timeout |
-----------------------------
| 1  | result_1   | 10000   |
| 2  | result_2   | 10000   |

Then I can use the temporary tables to sort any search results whenever needed without the need to reconstruct (with some modification) the query.

Each table will be dropped, according to the specified timeout.

Assuming I cannot modify the structure of existing tables that are used in the query, would this be a good solution or are there better ways? Please advice.

Thanks

Answer 1

There's no need to go to the trouble of storing the results in a persistent database when you just want to cache search results in memory. Do you need indexed access to relational data? If the answer is no, don't store it in a MySQL database.

I know that phpbb (an open source web forum which supports MySQL backends) uses a key-value store to back its search results. If the forum is configured to give you a link to the specific results page (with the search id hash in the URL's query string) then that link will be valid for awhile but eventually be flushed out of the cache, just like you want. It may be overkill to implement a full database abstraction layer if you're set on MySQL though. Anyway:

http://wiki.phpbb.com/Cache

You should just use memcached or something to store the results data, and you can easily retrieve the data and sort it in PHP. Also there are some PHP-specific cache frameworks that minimize the cost of loading and offloading data from the interpreter:

https://en.wikipedia.org/wiki/List_of_PHP_accelerators