0

I have a PHP Application and I use Laravel Framework. When I'm developing the application its response time is not an issue. But, when I deployed it and it gets a lot of data in one of its transaction table (almost 7,000 rows as of this time) in the database it takes of almost 6 seconds to refresh the page. I only get the last 100 data from the database to view as logs. Is there such ways to shorten this response time? BTW my application only run in a local network.

Here is my code:

Route::get('/', function()
{
    $users = DB::table('logs')
        ->join('students', 'logs.id', '=', 'students.id')
        ->orderBy('time_in', 'desc')
        ->take(100)
        ->get();

    return View::make('index')
        ->with('users',$users);
});
raye_penver
  • 57
  • 8

1 Answers1

1

You should always be checking the query health using explain select..

Now from the query that you have in the query builder you will need some indices if not alreay applied

alter table logs add index id_idx(id) ;

If id is a primary key in logs table then you do not need the above index

alter table logs add index time_in_idx(time_in);

The above will be helpful for ordering the data .

If id is not a primary key and not indexed either then you may just add the following covering index

alter table logs add index id_time_idx(id,time_in);
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • i think i'll just make the `time in` column primary :-) How about that `explain select...` where i can test it? – raye_penver Sep 10 '15 at 09:29
  • You need to get the raw query and run it on mysql, the query you can check is `explain select * from logs join students on logs.id = students.id order by logs.time_in desc limit 100` – Abhik Chakraborty Sep 10 '15 at 09:46