Geek asks and answers

How to get SQLite to cache select command results

I am running a web application with an SQLite database that performs read operations exclusively. Users connect to the database, look for entries through the select command, and view the results in a browser. But the choice is quite time-consuming, since it involves matching character patterns across several million rows of a table. (The size of the results table is quite small).

Different users tend to do an exact search, so if I can cache the selection for the first time, the next user to search the database (at the same time or most likely in a few days) can quickly return the results.

How can I do this in SQLite? Is there a pragma I need to use? I heard that SQLite has an automatic caching feature, but that doesn't seem to help. Please note that I am using a hosting service, so I can not rebuild SQLite.

Any help would be greatly appreciated.

+3
source share
4 answers

After posting the above question, I found one simple solution that seems effective, which does not require any changes to the CPanel hosting service I use.

Instead of caching SQL results, I am simple in the cache of the entire web page generated by the PHP script. Users receiving an exact search then get a cached page, completely bypassing the database.

I got the main idea here .

, , , . , PHP, - .

, , .

0

,

memcached

APC/Zend ( PHP)

( , , )...

+1

SQLite DB? PRAGMA .

PRAGMA cache_size; 
PRAGMA cache_size = Number-of-pages;

, SQLite .

: http://www.sqlite.org/pragma.html#pragma_cache_size

+1

, / CACHE. gotchas - , :

SELECT * FROM myTable WHERE (col1 BETWEEN 1000 AND 2000) AND (col2='StackOverflow');
SELECT * FROM myTable WHERE (col2='StackOverflow') AND (col1 BETWEEN 1000 AND 2000);

, MD5' . :

SELECT col1,col2 FROM myTable;
SELECT col2,col1 FROM myTable;

, ( SELECTables, WHERE-, .. - PHP/JavaScript), --, , . JSON, , OP, , JSON gzipped ( AJAXing ).

, CACHE Hash, UNIQUE, .

Node.js, id , CACHE , , , , allHashes.has(hashedSortedQuery). , . true, SELECT CACHE, Hash = 'hashedSortedQuery', else, ( ).

One final note - from my experience doing the same thing, updating my version of SQLite had a HUGE speed improvement, and I only mention this because often on servers with shared hosting the latest version is not always installed. Here is the comparison I made today between the global version of SQLite installed on my server and the latest version of SQLite compiled from the source code: http://pastebin.com/hpWu3UCk

0
source

More articles:


All Articles