Riscrivere mysql seleziona per ridurre il tempo e scrivere tmp su disco

Ho una query mysql che richiede diversi minuti, il che non è molto buono dato che è usato per creare una pagina web.

Sono utilizzate tre tabelle: poster_data contiene informazioni sui singoli poster. poster_categories elenca tutte le categorie (film, arte, ecc.) mentre poster_prodcat elenca il numero posterid e le categorie in cui può essere, ad esempio un poster avrebbe più righe per dire, film, indiana jones, harrison ford, film d’avventura, ecc.

questa è la query lenta:

select * from poster_prodcat, poster_data, poster_categories where poster_data.apnumber = poster_prodcat.apnumber and poster_categories.apcatnum = poster_prodcat.apcatnum and poster_prodcat.apcatnum='623' ORDER BY aptitle ASC LIMIT 0, 32 

Secondo lo spiegare:

spiegare

Ci sono voluti alcuni minuti. Poster_data ha poco più di 800.000 file, mentre poster_prodcat ha poco più di 17 milioni. Altre query di categoria con questa selezione sono appena evidenti, mentre poster_prodcat.apcatnum = ‘623’ ha circa 400.000 risultati e sta scrivendo sul disco

spero che tu trovi questo utile – http://pastie.org/1105206

 drop table if exists poster; create table poster ( poster_id int unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists category; create table category ( cat_id mediumint unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists poster_category; create table poster_category ( cat_id mediumint unsigned not null, poster_id int unsigned not null, primary key (cat_id, poster_id) -- note the clustered composite index !! ) engine = innodb; -- FYI http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html select count(*) from category count(*) ======== 500,000 select count(*) from poster count(*) ======== 1,000,000 select count(*) from poster_category count(*) ======== 125,675,688 select count(*) from poster_category where cat_id = 623 count(*) ======== 342,820 explain select p.*, c.* from poster_category pc inner join category c on pc.cat_id = c.cat_id inner join poster p on pc.poster_id = p.poster_id where pc.cat_id = 623 order by p.name limit 32; id select_type table type possible_keys key key_len ref rows == =========== ===== ==== ============= === ======= === ==== 1 SIMPLE c const PRIMARY PRIMARY 3 const 1 1 SIMPLE p index PRIMARY name 257 null 32 1 SIMPLE pc eq_ref PRIMARY PRIMARY 7 const,foo_db.p.poster_id 1 select p.*, c.* from poster_category pc inner join category c on pc.cat_id = c.cat_id inner join poster p on pc.poster_id = p.poster_id where pc.cat_id = 623 order by p.name limit 32; Statement:21/08/2010 0:00:00.021: Query OK 

La query che hai elencato è come sarà la query finale? (Quindi hanno l’apcatnum = / ID /?)

dove poster_data.apnumber = poster_prodcat.apnumber e poster_categories.apcatnum = poster_prodcat.apcatnum e poster_prodcat.apcatnum = ‘623’

poster_prodcat.apcatnum = ‘623’ diminuirà enormemente il set di dati su cui mysql deve lavorare, quindi questa dovrebbe essere la prima parte analizzata della query.

Quindi passare a scambiare i confronti in modo che quelli che minimizzano il set di dati verranno analizzati per primi.

Potresti anche voler provare le sottoquery. Non sono sicuro che ciò sarà d’aiuto, ma probabilmente mysql non prenderà prima tutte e 3 le tabelle, ma prima eseguirà la sotto-query e poi l’altra. Questo dovrebbe minimizzare il consumo di memoria durante l’interrogazione. Anche se questa non è un’opzione se vuoi davvero selezionare tutte le colonne (dato che stai usando un * lì).

Devi avere un indice su apnumber in POSTER_DATA. La scansione di 841.152 record sta uccidendo la performance.

Sembra che la query stia utilizzando l’indice apptitle per ottenere l’ordine ma sta eseguendo una scansione completa per filtrare i risultati. Penso che potrebbe essere d’aiuto se si ha un indice composito su entrambi gli apptitle e apnumber su poster_data. MySQL potrebbe quindi essere in grado di usarlo per fare sia l’ordinamento che il filtro.

 create index data_title_anum_idx on poster_data(aptitle,apnumber);