The task consists into investigating, reporting, and creating a prototype about the possibility to create a cache layer of a particular set of tables f our website RDBMS.
Our table has currently 300k+ records and is growing very fast, so soon the problem of optimized querying will arise.
The table has several fields and it must be possible to quickly filter and sort for each one of its filters. The task consists in investigating a NoSQL solution, we were thinking to, but not limiting to, a CouchDB solution.
Attached there is the relevant tables SQL structure, it is a vertical partitioning.
attributes required (each flag must be present)
attributes excluded (each flag must NOT be present)
with photo (yes/no/irrelevant)
seller id (integer exact match)
location (string exact match)
currency (string exact match)
minprice (integer >= match)
maxprice (integer <= match)
minstate (integer >= match)
category (string exact match)
card id (integer exact match)
set id (integer exact match)
language (string exact match)
query (free text match with relevance against a preconstructed string)
Ordering possible required (cascading), all of them both ASC and DESC:
The prototype must consists in a fully working and integrable solution with the following components:
1) a procedure that selects the whole data and populates the CouchDB (cache full rebuild)
2) a procedure that selects one record and updates the relative entry (cache update/invalidation)
3) Example procedure that queries the cache returning the full records, paginated (offset,limit), and with filter and sorting applied
4) a concise documentation about the solution, how to extend it with new fields, maintenance and related information
Code must be written in PHP.
Contact: Giovanni G