Tukukkk

I'm building an app with Laravel 5.6 and VueJS requesting data from an API on the Laravel backend. In my DEV environment the queries take no time at all, but in the Production environment the response on the API takes 85.7 seconds opposed to 0.1 seconds in the DEV environment. Data is in both environments the same.

Using the follwoing commands in my Controller I was able to get the exact queries that run and I can test them one by one:

DB::enableQueryLog();



    // then my eloquent stuff

    $query = Sportevent::whereHas('photos')->with('photos');

    if($request->input('year')){

        $year = $request->input('year');

        $query = $query->where('date','like', $year.'%');

    }

    if($request->input('country')){

        $country = $request->input('country');

        $query = $query->where('country',$country);

     }

    $sportevents = $query->orderBy('date',"DESC")->paginate(10);



// then I display the queries:

dd(DB::getQueryLog());

Here are the queries that are produced, not having selected a year or a country, incl performance in both environments with time:

// --------------------------------------------------------------

// DEV: 0.0208 seconds | PROD: 73 seconds (had to use stopwatch)

// --------------------------------------------------------------

select count(*) as aggregate from `events` where exists 

   (select * from `photos` where `events`.`id` = `photos`.`eventID` 

   and `active` = 1)



// ------------------------------------------

// DEV: 0.025 seconds | PROD: 38.9721 seconds

// ------------------------------------------

select * from `events` where exists (select * from `photos` where

  `events`.`id` = `photos`.`eventID` and `active` = 1) 

   order by `date` desc limit 10 offset 0



// ------------------------------------------

// DEV: 0.0112 seconds | PROD: 0.0141 seconds

// ------------------------------------------

select * from `photos` where `active` = 1 and `photos`.`eventID` in 

    (11194, 11087, 10506, 10797, 9910, 10118, 10212, 9655, 10047, 10049)

The table events contains about 6000 entries and photos less than 50000 entries. If you need further details when it comes to table structure, let me know before down voting in the comments section :-)

On the production server are many other applications running with Laravel or Wordpress using databases on the same MySQL installation and none of them have similar issues.

Although the first two queries don't output much they have to go through the most data and therefore seem to be very memory and/or CPU intense queries.

It appears that my DEV environment has more power than my production environment and therefore I had this huge differences.

The solution at the end was simple: I added an INDEX of type INDEX to the column photos.eventID which would have been a good idea anyway.

This little change made my API respond in 0.4 seconds instead of 85.7