> `price` double(12,4)
> `original_price` double
Don't use DOUBLE or FLOAT for monetary values; use DECIMAL.
> CONSTRAINT `spd_unit_ibfk_2_test` FOREIGN KEY (`fk_id_feed_xml`) REFERENCES `spd_feed_xml` (`id_feed_xml`) ON DELETE NO ACTION ON UPDATE NO ACTION,
> CONSTRAINT `spd_unit_ibfk_2` FOREIGN KEY (`fk_id_feed_xml`) REFERENCES `spd_feed_xml` (`id_feed_xml`) ON DELETE NO ACTION ON UPDATE NO ACTION,
Redundant FKs?? DROP the dups.
> But the table spd_unit, have more them 2.000.000 rows
> ) ENGINE=InnoDB AUTO_INCREMENT=32574 DEFAULT CHARSET=latin1
Those two do not agree. How many rows in spd_unit??
Is this the query that is still "too slow"?:
select fk_id_ad_format
from spd_unit
join spd_ad on fk_id_ad=id_ad
where fk_id_campaign in(...)
group by fk_id_ad_format;
Please provide the output of
EXPLAIN
select fk_id_ad_format
from spd_unit
join spd_ad on fk_id_ad=id_ad
where fk_id_campaign in(...)
group by fk_id_ad_format;
(with a suitable list of values in the IN() list).
In spd_unit, changing
KEY `t_anuncio_t_pecas_fk` (`fk_id_ad`),
to
KEY `t_anuncio_t_pecas_fk` (`fk_id_ad`, `fk_id_ad_format`),
would help the query that Peter presented.