Store common queries on disk with Mysql and windows

Question

I have a Huge person database and do common search with name on it.

SELECT * FROM tbl_person WHERE full_name LIKE 'Sparow%Jack%';
SELECT * FROM tbl_person WHERE full_name LIKE 'Sparow%';

I rarely insert new data in this table.

I want to store common last_name queries on hark disk, queries already stored in ram but I loose it all each time the server reboot.

I have 1.7Billions row in my table and each row (with index) take 1k, yes it's a 1.7Tb database.

It's the main reason why I want to stored common select on disk.

Variable_name,Value
query_alloc_block_size,8192
query_cache_limit,1048576
query_cache_min_res_unit,1024
query_cache_size,4294966272
query_cache_type,ON
query_cache_wlock_invalidate,OFF
query_prealloc_size,8192

Edit :

SELECT * FROM tbl_person WHERE full_name LIKE 'Savard%';

take 1000 sec to execute first time and 2 sec after. If I reboot the system and execute again, the query take 1000 sec again.

I simply want to avoid mysql take another 1000 sec runing the same query I already do before reboot.

Answer 1

Why not consider something like Redis for caching?

It's an in memory data store and it's very popular right now. Sites using Redis: http://blog.togo.io/redisphere/redis-roundup-what-companies-use-redis

Redis also can persist data to disk: http://redis.io/topics/persistence

For caching though, saving to disk shouldn't be absolutely critical. The idea is that if some data is not cached, the worst case is not always loading from disk manually, but going straight through to your database.

Answer 2

If you are performing many such queries on your data, I suggest you index your table using Apache Lucene or Sphinx. Database are fast, but they are not so efficient (especially MySQL) when performing partial matches on millions of rows.

I already answered a similar question about Zend Framework and Lucene, and favor Zend's solution as I believe it is the easiest to setup and use with a PHP environment.

Luckily, Zend Framework can be used by module and you can easily only use the Zend Search Lucene module by itself without the entire class library.

** Edit **

The role of an indexer is not to replace your DB, but to improve it's search functionality by providing a way to perform partial searches. For example, given your table, you may only index a few of your fields (make them "queryable") and have other static (non-indexed) fields to reference your rows in your database.

The advantage in using an indexer is that you can also index pre-computations and directly search them, instead of querying the database.