ottimizzare una query con una join tra tabelle con milioni di record

[james]

Ho due tabelle, una rappresenta l'accesso ad un sito, la sessione per capirci, e c'è una colonna "utente" che identifica l'ID utente, l'altra rappresenta le pagine visitate dall'utente internamente ad ogni singola sessione. La tabella "pagine_visitate" è legata alla tabella "sessioni" da una chiave esterna.

Quando vado a fare il count di tutte le visite fatte da un utente, quindi non le sessioni, ma il count delle pagine visitate per tutte le sessioni a lui riferite, i tempi della query è circa 40 secondi...Sto cercando di ottimizzarla ma non ne vengo fuori. La query è questa:

SELECT COUNT(*) as visite FROM accessi LEFT JOIN sessioni ON accessi.sessione_id=sessioni.id WHERE sessioni.user_id='6' 

la chiave accessi.sessione_id è un indice, così come sessioni.user_id ovviamente. Ho fatto un EXPLAIN, praticamente mysql prima seleziona tutte le sessioni applicando la WHERE SIMPLE e nel caso di alcuni utenti vengono fuori 3 mln di record, poi procede con la JOIN, ma i tempi sono enormi! Ho provato a cambiare la query così sperando di ottenere risultati diversi:

SELECT COUNT(*) as visite FROM accessi WHERE sessione_id IN ( SELECT id FROM sessioni WHERE user_id='6'); 

Pensavo che così facendo ci metteva un attimo a scremare la SELECT annidata con la sola WHERE, ed infatti fa abbastanza subito se testo quella sola query con un LIMIT, ma se non uso il LIMIT e faccio tirare giù tutti i 3milioni di risultati per poi a incrociare 3 milioni di istanze con la causola IN...è comunque un bagno di sangue. Secondo voi c'è modo di ottimizzare una query del genere?

[optime]

prova con

SELECT COUNT(*) as visite FROM accessi LEFT JOIN sessioni ON accessi.sessione_id=sessioni.id AND sessioni.user_id='6'

[james]

prova con

SELECT COUNT(*) as visite FROM accessi LEFT JOIN sessioni ON accessi.sessione_id=sessioni.id AND sessioni.user_id='6'

è esattamente la stessa cosa, anche l'EXPLAIN è proprio identico

[optime]

la colonna session_id è indicizzata?

[james]

la colonna session_id è indicizzata?

sì certo

[optime]

scusa, intendevo la sessioni.user_id

[james]

Se faccio la COUNT della sola tabella sessioni (tralasciando la tabella accessi) i tempi sono sotto il secondo

SELECT COUNT(*) as visite FROM sessioni WHERE user_id='6'; 

[james]

sì certo

Sì anche quel campo ovviamente

[james]

Se faccio la COUNT della sola tabella sessioni (tralasciando la tabella accessi) i tempi sono sotto il secondo

SELECT COUNT(*) as visite FROM sessioni WHERE user_id='6'; 

Il problema sorge dalla JOIN, il che mi fa pensare che (non so come lavora mysql praticamente) che prima faccia la SELECT della condizione come dice l'EXPLAIN, quindi tira fuori tutte le istanze di sessioni in cui la condizione sessioni.user_id='6' è verificata, il che sono circa 3 milioni di record, poi per ciascuno di essi fa 3milioni di controlli per estrarre (e contare) quante istanze di "accessi" sono legate ad ogni singolo risultato della prima parte della SELECT, per cui è come se facesse 3mln(record estratti da sessioni per sessioni.user_id='6')*9mln(record totali della tabella "accessi") per cui i tempi si elevano esponenzialmente.

[sspintux]

... i miei due centesimi;

SELECT COUNT(*) as visite FROM accessi 
LEFT JOIN 
(select * from sessioni WHERE sessioni.user_id='6') as q
ON accessi.sessione_id=q.sessioni.id  

HTH