CODEWITHSUNDEEP
phpmysqlleft-join
dan87
dan87

Reputation: 333

LEFT JOIN very slow when executed in PHP

I've been facing this problem for a few days and I don't know what to do, when I execute this query in mysql takes about 8 seconds:

CREATE TEMPORARY TABLE temp_forecast AS 
   (SELECT gfs.spot, wind, winddir, clouds, temperature, gust,
           precipitation, pressure, humidity, wavedir, waveperiod,
           waveheight, gfs.hour, gfs.UTCdate, localdate, modelcicle, 
           tide, sst  
     FROM table1 AS gfs 
       left outer join table2 AS waves 
         on gfs.spot=waves.spot AND gfs.utcdate=waves.utcdate)

I executed explain on this query and the indexes are ok, it says it only needs to check one row in waves table foreach row of gfs, but when I execute it in PHP it can take up to 20 minutes overloading the server, I have no idea what is going on

I also tried RESET QUERY CACHE; before executing the query in mysql to see if it was cached but still it takes 8 seconds, it seems like the query is not using the index when executed in PHP, the query is executed right after the insertion, maybe the index is not ready yet?

This is the code executing the query on PHP:

public function dumpData(Logger $log){
        $q['sql'] = sprintf("CREATE TEMPORARY TABLE temp_forecast AS (SELECT gfs.spot, wind, winddir, clouds, temperature, gust, precipitation, pressure, humidity, wavedir, waveperiod,
        waveheight, gfs.hour, gfs.UTCdate, localdate, modelcicle, tide, sst  FROM %s AS gfs left outer join %s AS waves on 
        gfs.spot=waves.spot AND gfs.utcdate=waves.utcdate", 
                    $this->GFSforecast,
                    $this->wavesForecast);
        $q['ret'] = 'row_count'; 
        $this->pdo->smartQuery($q, __FUNCTION__);
        $q['sql'] = sprintf("TRUNCATE TABLE %s", $this->forecast);
        $q['ret'] = 'row_count'; 
        $this->pdo->smartQuery($q, __FUNCTION__);
        $q['sql'] = sprintf("INSERT INTO %s (SELECT * FROM temp_forecast)", $this->forecast);
        $q['ret'] = 'row_count';            
        return $this->pdo->smartQuery($q, __FUNCTION__);
}

Upvotes: 2

Views: 138

Answers (1)

Rick James
Rick James

Reputation: 142296

On waves, have the compound INDEX(spot, utcdate)

If that does not solve it, please show us SHOW CREATE TABLE and tell us how big each table is.

A better way to repopulate a table is

  1. CREATE TABLE new LIKE forecast;
  2. INSERT INTO new SELECT ...;
  3. RENAME TABLE forecast TO old, new TO forecast;
  4. DROP TABLE old;

You are never without the table forecast; the RENAME is atomic and 'instantaneous'. So, even if the SELECT (step 2) is slow, it does not hurt (other than delaying the update).

Upvotes: 1

Related Questions