i am more interested in seeing code examples of how people have implemented a multi table search.
I have it working, but the query is generating around 2mb of data (info from the benchmark CI function <?=$this->benchmark->memory_usage();?>
The problem is that it is using too much memory and i have all the search results in a single query calling 7 tables to get all the results. Therefore i am not convinced I have a very optimised script and want to try something new.
My plan is to just get the product id’s into an array for the search and output that list. Some of the tables are small, so i may as well make these into arrays for the site to use in general to reduce the sql overhead.
I was contemplating making a new table for the search engine with all the data in a single feed, updated hourly with a cron job or something like that.
My question is if anyone has any advice on doing this, especially about the idea of sorting optimising the sql to run efficiently.