Slow Query Performance Left Joining A View

I have 2 tables: describe CONSUMO Field Type Null Key Default Extra idconsumo int(11) NO PRI NULL auto_increment idkey int(11)

Solution 1:

Most of the problems are here:

LEFT JOIN `CONSUMO` ON sub1.fecha = DATE_FORMAT(fechahora, "%M %e")

In particular:

• Don't use LEFT; you want all the rows, and no extra ones, correct? So use a plain JOIN.

• Do index fechahora.

• Don't use DESCRIBE; it is less descriptive than SHOW CREATE TABLE.

• Rather than recomputing the last 30 days over and over; have a long table with several year's worth of dates, and use a WHERE clause to limit the desired rows.

• Don't hide fechahora inside a function. Rearrange the query so it looks like

  ON fechahora >= ... sub1.fecha ... AND fechahora < ... sub1.fecha + INTERVAL 1 DAY ...

The ... needs to be whatever it takes to do the inverse of "%M %e". You would probably be better off changing last_30_days to compute a plain DATE datatype. If/when you need a particular formatting in the output, do it in the SELECT.