Usando l’indice, usando temporaneamente, usando filesort – come risolvere questo?

Sto lavorando a un sistema di tracciamento degli eventi che utilizza una manciata di tabelle di ricerca e la tabella di registrazione principale. In una relazione che sto scrivendo, è ansible selezionare un object per visualizzare le statistiche. L’interfaccia mostra tutti gli oggetti in ordine di importanza decrescente (es. Colpi).

Lo schema per le due tabelle (leggermente ridimensionato, ma ottieni l’essenza):

CREATE TABLE IF NOT EXISTS `event_log` ( `event_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(5) DEFAULT NULL, `object_id` int(5) DEFAULT NULL, `event_date` datetime DEFAULT NULL, PRIMARY KEY (`event_id`), KEY `user_id` (`user_id`), KEY `object_id` (`object_id`) ); CREATE TABLE IF NOT EXISTS `lookup_event_objects` ( `object_id` int(11) NOT NULL AUTO_INCREMENT, `object_desc` varchar(255) NOT NULL, PRIMARY KEY (`object_id`) ); 

La query con cui ho problemi è qui sotto. Funziona perfettamente con la mia tabella di ~ 100 voci, ma la SPIEGAZIONE mi preoccupa un po ‘.

  explain SELECT el.object_id, leo.object_desc, COUNT(el.object_id) as count_rows FROM event_log el LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id GROUP BY el.object_id ORDER BY count_rows DESC, leo.object_desc ASC 

Resi: Using index; Using temporary; Using filesort Using index; Using temporary; Using filesort

Quindi, cosa c’è di sbagliato con il mio schema e / o query per MySQL di ripiegare su temporary e filesort ? O è ottimizzato in quanto può utilizzare ORDER BY?

Bene, il documento fornisce i motivi esatti in cui apparirà “Uso temporaneo”:

Le tabelle temporanee possono essere create in condizioni come queste:

Se esiste una clausola ORDER BY e una clausola GROUP BY diversa, oppure se ORDER BY o GROUP BY contiene colonne di tabelle diverse dalla prima tabella nella coda di join, viene creata una tabella temporanea.

DISTINCT combinato con ORDER BY può richiedere una tabella temporanea.

Se si utilizza l’opzione SQL_SMALL_RESULT, MySQL utilizza una tabella temporanea in memoria, a meno che la query contenga anche elementi (descritti in seguito) che richiedono l’archiviazione su disco.

Una scansione veloce mostra che si soffre di # 1.

E questo blog del 2009 dice che “using filesort” significa che l’ordinamento non può essere eseguito con un indice. Dato che stai ordinando da un campo calcolato, anche quello sarà vero.

Quindi, questo è “sbagliato”.

Aggiornato per MySQL 5.7 ( src ):

Il server crea tabelle temporanee in condizioni come queste:

  • Valutazione delle dichiarazioni UNION, con alcune eccezioni descritte più avanti.

  • Valutazione di alcune viste, come quelle che utilizzano l’algoritmo TEMPTABLE, UNION o aggregazione.

  • Valutazione delle tabelle derivate (subquery nella clausola FROM).

  • Tabelle create per la materializzazione sottoquery o semi-join (vedere Sezione 8.2.2, “Ottimizzazione delle sottoquery, Tabelle derivate e Visualizza riferimenti”).

  • Valutazione di istruzioni che contengono una clausola ORDER BY e una clausola GROUP BY diversa o per le quali ORDER BY o GROUP BY contiene colonne di tabelle diverse dalla prima tabella nella coda di join.

  • La valutazione di DISTINCT combinata con ORDER BY può richiedere una tabella temporanea.

  • Per le query che utilizzano il modificatore SQL_SMALL_RESULT, MySQL utilizza una tabella temporanea in memoria, a meno che la query contenga anche elementi (descritti in seguito) che richiedono l’archiviazione su disco.

  • Per valutare le istruzioni INSERT … SELECT che selezionano e inseriscono nella stessa tabella, MySQL crea una tabella temporanea interna per contenere le righe da SELECT, quindi inserisce tali righe nella tabella di destinazione. Vedere la Sezione 13.2.5.1, “INSERT … SELECT Syntax”.

  • Valutazione di istruzioni UPDATE a più tabelle.

  • Valutazione delle espressioni GROUP_CONCAT () o COUNT (DISTINCT).

Queste sono le seguenti condizioni in cui vengono create le tabelle temporanee. Le query UNION utilizzano tabelle temporanee.

Alcune viste richiedono tabelle temporanee, come quelle valutate usando l’algoritmo TEMPTABLE o che usano UNION o aggregazione.

Se esiste una clausola ORDER BY e una clausola GROUP BY diversa, oppure se ORDER BY o GROUP BY contiene colonne di tabelle diverse dalla prima tabella nella coda di join, viene creata una tabella temporanea.

DISTINCT combinato con ORDER BY può richiedere una tabella temporanea.

Se si utilizza l’opzione SQL_SMALL_RESULT, MySQL utilizza una tabella temporanea in memoria, a meno che la query contenga anche elementi (descritti in seguito) che richiedono l’archiviazione su disco.

Segui questo link per mysql: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html