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)     NO      MUL     NULL    
ip          varchar(50) NO              Unknown 
fechahora   datetime    NO              NULL    


describe CONTRATADO

Field       Type        Null    Key     Default     Extra
idkey       int(11)     NO      PRI     NULL        auto_increment
idusuario   int(11)     NO      MUL     NULL    
idproducto  int(11)     NO      MUL     NULL    
key         varchar(64) NO      MUL     NULL    
descripcion varchar(50) YES             "API KEY"   
peticiones  int(11)     YES             NULL    
caducidad   datetime    YES             NULL

And a view (that returns the last 30 days from NOW()):

CREATE VIEW `last_30_days` AS
    SELECT 
        DATE_FORMAT((NOW() - INTERVAL ((`units`.`i` + (`tens`.`i` * 10)) + (`hundreds`.`i` * 100)) DAY),
                '%M %e') AS `fecha`,
        DATE_FORMAT((NOW() - INTERVAL ((`units`.`i` + (`tens`.`i` * 10)) + (`hundreds`.`i` * 100)) DAY),
                '%Y%m%d') AS `fecha_order`
    FROM
        ((`0_9_range` `units`
        JOIN `0_9_range` `tens`)
        JOIN `0_9_range` `hundreds`)
    WHERE
        ((NOW() - INTERVAL ((`units`.`i` + (`tens`.`i` * 10)) + (`hundreds`.`i` * 100)) DAY) BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW())

Whenever I run a query, as for example:

SELECT sub1.fecha, COUNT(idconsumo) as consumo
FROM `last_30_days` `sub1`
LEFT JOIN `CONSUMO` ON sub1.fecha = DATE_FORMAT(fechahora, "%M %e")
LEFT JOIN `CONTRATADO` ON CONSUMO.idkey = CONTRATADO.idkey
WHERE idusuario IS NULL OR idusuario = 1
GROUP BY `sub1`.`fecha`
ORDER BY `sub1`.`fecha_order`

it takes quite some time (~20 seconds).

Here is an explain of the query:

id      select_type     table                                   type    possible_keys   key     key_len ref                     rows    Extra
1       PRIMARY         <derived3>                              ALL     NULL            NULL    NULL    NULL                    10      "Using temporary; Using filesort"
1       PRIMARY         <derived13>                             ALL     NULL            NULL    NULL    NULL                    10      "Using join buffer"
1       PRIMARY         <derived23>                             ALL     NULL            NULL    NULL    NULL                    10      "Using where; Using join buffer"
1       PRIMARY         CONSUMO                                 ALL     NULL            NULL    NULL    NULL                    908553  
1       PRIMARY         CONTRATADO                              eq_ref  PRIMARY         PRIMARY 4       c1geoapi.CONSUMO.idkey  1       "Using where"
23      DERIVED         NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
24      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
25      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
26      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
27      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
28      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
29      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
30      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
31      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
32      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
NULL    "UNION RESULT"  <union23,24,25,26,27,28,29,30,31,32>    ALL     NULL            NULL    NULL    NULL                    NULL    
13      DERIVED         NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
14      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
15      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
16      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
17      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
18      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
19      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
20      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
21      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
22      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
NULL    "UNION RESULT"  <union13,14,15,16,17,18,19,20,21,22>    ALL     NULL            NULL    NULL    NULL                    NULL    
3       DERIVED         NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
4       UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
5       UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
6       UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
7       UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
8       UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
9       UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
10      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
11      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
12      UNION           NULL                                    NULL    NULL            NULL    NULL    NULL                    NULL    "No tables used"
NULL    "UNION RESULT"  <union3,4,5,6,7,8,9,10,11,12>           ALL     NULL            NULL    NULL    NULL                    NULL

What can I do in order to improve my query? Why is it taking so long?

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.