CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
Christoffer
Christoffer

Reputation: 7787

Slow MySQL query in Laravel is fast elsewhere

I'm using Eloquent in a Laravel 4.2 application. One of my controller comes back with a view after about 6000ms, which is way too long. It processes data from a table called responses. It works pretty ok with a couple of thousand rows, but when I'm over 30000 it starts getting really slow.

Here's the weird part. If I check my DB::getQueryLog(); I can see this query:

[1] => Array
        (
            [query] => select * from `response` where `survey_id` = ? and `question_id` = ? and `resp_group` in (?, ?)
            [bindings] => Array
                (
                    [0] => 48
                    [1] => 25
                    [2] => a
                    [3] => b
                )

            [time] => 11
        )

which I assume would result in this actual query:

select * from `response` 
where `survey_id` = 48 
and `question_id` = 25 
and `resp_group` in ('a', 'b');

As you can see the time is pretty high there. And the log tells me I run queries like this about 40 times with time between 7 and 11.

But if I run the same query in HediSQL I get 0,000 sec and 0.0010 in PHPMyAdmin. I have to run 20 of them to get 0,015 seconds.

Explain gives this:

    select_type: SIMPLE
    table: response
    possible_keys: 
      question_id_4, 
      resp_group_survey_id, 
      survey_id, 
      resp_group_question_id_survey_id
    key: question_id_4
    key_len: 8
    ref: const,const
    rows: 949
    Extra: Using where

So there are indexes. How come the same query is so slow when Eloquent performs it and so much faster when I run it directly on the MySQL Server?

(I'm running on my Local WAMP server)

Upvotes: 0

Views: 6920

Answers (1)

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40909

The time in DB::getQueryLog() is reported in milliseconds so it is more or less the same to what PHPMyAdmin reports.

The reason why controller method takes 6 seconds to execute is probably related to the amount of data (30000 rows) that Eloquent needs to process after it fetches the data from the database.

If you want to make that faster use DB class instead.

DB::table('response')
->whereSurveyId($survey_id)
->whereQuestionId($question_id)
->whereIn('resp_group', $groups)
->get();

It might be also helpful to provide a list of required columns to get() so that you fetch less data from the database.

Upvotes: 1

Related Questions