How To Improve Wind Data Sql Query Performance

I'm looking for help on how to optimize (if possible) the performance of a SQL query used for reading wind information (see below) by changing the e.g. the database structure, quer

Solution 1:

I strongly agree with the comments so far -- Cleanse the data as you put it into the table.

Once you have done the cleansing, let's avoid the subquery by doing...

SELECTMIN(dt) as'Start of 15 mins',
        FORMAT(AVG(mean), 1) as'Avg wind speed',
        ...
    FROMtableGROUPBYFLOOR(UNIX_TIMESTAMP(dt) /900)
    ORDERBYFLOOR(UNIX_TIMESTAMP(dt) /900);

I don't understand the purpose of the LIMIT. I'll guess that you want to a few days at a time. For that, I recommend you add (after cleansing) between the FROM and the GROUP BY.

WHERE dt >='2015-04-10'AND dt  <'2015-04-10'+INTERVAL7DAY

That would show 7 days, starting '2015-04-10' morning.

In order to handle a table of 800K, you would decidedly need (again, after cleansing):

INDEX(dt)

To cleanse the 800K rows, there are multiple approaches. I suggest creating a new table, copy the data in, test, and eventually swap over. Something like...

CREATETABLEnew (
    dt DATETIME, 
    mean FLOAT,
    ...
    PRIMARY KEY(dt)  -- assuming you have only one row per minute?
) ENGINE=InnoDB;

INSERTINTOnew (dt, mean, ...)
    SELECT str_to_date(...),
           mean, -- I suspect that the CAST is not needed
           ...;

Write the new select and test it.

By now new is missing the newer rows. You can either rebuild it and hope to finish everything in your one minute window, or play some other game. Let us know if you want help there.