sql - Optimize tables MySQL -

sql - Optimize tables MySQL -

i have query executed in 35s, waaaaay long.

here 3 tables concerned query (each table approx. 13000 lines long, , should much longer in future) :

table 1 : domains

create table if not exists `domain` ( `id_domain` int(11) not null auto_increment, `domain_domain` varchar(255) not null, `projet_domain` int(11) not null, `date_crea_domain` int(11) not null, `date_expi_domain` int(11) not null, `active_domain` tinyint(1) not null, `remarques_domain` text not null, primary key (`id_domain`) ) engine=innodb default charset=latin1;

table 2 : keywords

create table if not exists `kw` ( `id_kw` int(11) not null auto_increment, `kw_kw` varchar(255) not null, `clics_kw` int(11) not null, `cpc_kw` float(11,3) not null, `date_kw` int(11) not null, primary key (`id_kw`) ) engine=innodb default charset=latin1;

table 3 : linking between domain , keyword

create table if not exists `kw_domain` ( `id_kd` int(11) not null auto_increment, `kw_kd` int(11) not null, `domain_kd` int(11) not null, `selected_kd` tinyint(1) not null, primary key (`id_kd`), key `kw_to_domain` (`kw_kd`,`domain_kd`) ) engine=innodb default charset=latin1;

the query follows :

select ng.*, kd.*, kg.* domain ng left bring together kw_domain kd on kd.domain_kd = ng.id_domain left bring together kw kg on kg.id_kw = kd.kw_kd grouping ng.id_domain order kd.selected_kd desc, kd.id_kd desc

basically, selects domains, with, each 1 of these domains, lastly associated keyword.

does have thought on how optimize tables or query ?

the next lastly keyword, according logic:

select ng.*, (select kw_kd kw_domain kd kd.domain_kd = ng.id_domain , kd.selected_kd = 1 order kd.id_kd desc limit 1 ) kw_kd domain ng;

for performance, want index on kw_domain(domain_kd, selected_kd, kw_kd). in case, order of fields matters.

you can utilize subquery more info keyword:

select ng.*, kg.* (select ng.*, (select kw_kd kw_domain kd kd.domain_kd = ng.id_domain , kd.selected_kd = 1 order kd.id_kd desc limit 1 ) kw_kd domain ng ) ng left bring together kw kg on kg.id_kw = ng.kw_kd;

in mysql, group by can have poor performance, might work better, particularly right indexes.

mysql sql