I have 4 million rows in my database which I need to filter with time, as you all can guess the problem is about speed.
I am currently using the remember function to cache my queries. But I have some exact queries which my user are allowed so select. What i mean by this is:
Which means I have 4 queries to cache... All of them take about 30 seconds to be taken if you run the query without cache.
The thing I want is do is to filter that data after I receive all data once and cache it.
So that I can use the all data to filter how I want.
A bit more clearly with some code:
<?php
if (Cache::has('alldata')) {
$alldata = Cache::get('alldata');
} else {
$alldata = Data::all();
}
// Filters
$alldata->where('activity', '=', 1)->get();
// The code i currently use for every filter
$lastYearData = Data::where('column', '=', 'filter')->get();
// The line above "4 times" for each filter
?>
The where() method will call SQL where function. You cannot use it after get result. So you must filter with PHP :
$lastYearData = array_filter($alldata, function ($value) {
return $value['date']->diffInDays() <= 365;
});
Your 'cache' on PHP will not work since it will be cleared once PHP is done handling the request.
With proper table indexes, your query should not take that much time. Also, MySQL does caching so you don't really have to implement caching on PHP side unless your query's WHERE condition change a lot.
Try to run your 30 second query on MySQL commandline twice in a row. You'll notice that the second run will return faster than the first run.