Ottimizzare WHERE cond1 ORDER BY field LIMIT n

[LordSaga640]

Salve,

ho una tabella user con migliaia di utenti.
Ho anche una tabella LOG che contiene tutte le attività degli utenti e la data di quando sono state compiute.

La tabella è tipo:
(logId, utenteId, dataInserimento, testoLog)

Ho fatto un index di tipo btree con i campi index(utenteId, dataInserimento) dal nome indice_ut_data.

Se faccio:
explain SELECT * FROM log WHERE utenteId=2 ORDER BY dataInserimento DESC LIMIT 10
mi viene:
1, 'SIMPLE', 'homepage', 'ref', 'Index_u,indice_ut_data', 'indice_ut_data', '4', 'const', 1417352, 'Using where'


Come vedete c'è scritto "using where", che significa che prima prende gli elementi di una certa data e poi fa il where degli utenti. Insomma, c'è qualcosa che non va.
Non dovrebbe usare solamente l'indice?

Come posso ottimizzarla?
Grazie in anticipo

[piero.mac]

Originariamente inviato da LordSaga640
Come vedete c'è scritto "using where", che significa che prima prende gli elementi di una certa data e poi fa il where degli utenti. Insomma, c'è qualcosa che non va.
Non dovrebbe usare solamente l'indice?

Come posso ottimizzarla?

Il where viene risolto subito dopo il nome della tabella mentre l'order by viene applicato solo sui risultati ottenuti dalla condizione where (crea una tab temporanea con la selezione) e non su tutta la tabella.

Non vedo tanto un index con id e data se poi selezioni un id nel where. Proverei con un index con la sola data. da tener presente che per prendere gli ultimi 10 deve scorrere comunque tutti i record di selezione ottenuti con il where.

[LordSaga640]

Originariamente inviato da piero.mac
Il where viene risolto subito dopo il nome della tabella mentre l'order by viene applicato solo sui risultati ottenuti dalla condizione where (crea una tab temporanea con la selezione) e non su tutta la tabella.

Non vedo tanto un index con id e data se poi selezioni un id nel where. Proverei con un index con la sola data. da tener presente che per prendere gli ultimi 10 deve scorrere comunque tutti i record di selezione ottenuti con il where.

Ciao Piero.mac,

se uso come indice la sola data rischio però di scorrere l'intero indice di date fino a che non ne trova 10 di un certo utente. Infatti anche con la tua soluzione ci mette quasi un secondo.
Speravo che usando l'indice (utente, data) che scorresse nel btree e fosse una selezione diretta.

A questo punto l'unico modo che ho per ottimizzarlo è avere una tabella dove per ogni utente tengo solo gli ultimi 10 log. A quel punto tolgo anche la limit.

[piero.mac]

Originariamente inviato da LordSaga640
Ciao Piero.mac,

se uso come indice la sola data rischio però di scorrere l'intero indice di date fino a che non ne trova 10 di un certo utente. Infatti anche con la tua soluzione ci mette quasi un secondo.
Speravo che usando l'indice (utente, data) che scorresse nel btree e fosse una selezione diretta.

A questo punto l'unico modo che ho per ottimizzarlo è avere una tabella dove per ogni utente tengo solo gli ultimi 10 log. A quel punto tolgo anche la limit.

Se metti l'id utente nel where PRIMA cerca tutti gli id utente relativi POI ordina i record risultanti secondo l'ORDER BY e poi su questo ordinamento applica il limit.
Quindi non avviene mai la ricerca su tutte le date con l'abbinamento dell'id cercato, ma avviene la ricerca dell'id poi su questi record trovati l'ordinamento della data.

Avviene in modo trasparente qualcosa di molto simile:

create temporary table tab1
select * from tab
where id = $id
order by data desc
limit 10;

select * from tab1;

Lo potresti provare direttamente con phpmyadmin mettendo entrambe le query nella stesa finestra altrimenti non vedresti la tabella temporanea.

Sicuramente eliminare l'obsoleto e/o inutile ritengo sia una buona soluzione.