Select più lenta su campo indicizzato

[Nika90]

Salve,
Su un database MYSQL ho una tabella con circa un milione di record con un campo di tipo 'date'.

Le select del tipo data>'2011-05-01' sono tendenzialmente lente.
Ho provato quindi ad indicizzare il campo data.
Il risultato è che, se nella condizione inserisco una data recente, per cui mi vengono selezionati pochi record, la select è velocissima. Se invece inserisco un data per cui mi vengono selezionati molti record, la select risulta essere molto ma molto più lenta, anche della stessa query eseguita sul campo non indicizzato.

Ho provato a fare le stessissime cose su in campo di tipo intero in cui memorizzo il timestamp unix.
I risultati sono identici.

Qualcuno può aiutarmi ad ottimizzare queste query?

[deleted_29]

Originariamente inviato da Nika90
Salve,
Su un database MYSQL ho una tabella con circa un milione di record con un campo di tipo 'date'.

Le select del tipo data>'2011-05-01' sono tendenzialmente lente.
Ho provato quindi ad indicizzare il campo data.
Il risultato è che, se nella condizione inserisco una data recente, per cui mi vengono selezionati pochi record, la select è velocissima. Se invece inserisco un data per cui mi vengono selezionati molti record, la select risulta essere molto ma molto più lenta, anche della stessa query eseguita sul campo non indicizzato.

Ho provato a fare le stessissime cose su in campo di tipo intero in cui memorizzo il timestamp unix.
I risultati sono identici.

Qualcuno può aiutarmi ad ottimizzare queste query?

Si chiama "selettività" dell'indice, ed è del tutto normale (e ineliminabile).

Gli indici non sono "magici", hanno pregi e difetti come tutto.

Se la tua condizione ti fa prendere 1 riga, su 1.000.000, allora il "costo" di gestione dell'indice è risibile.

Se invece ritorni 999.999 righe, allora l'indice non fa altro che peggiorare la situazione di una fullscan
---
Per quanto ti riguarda se una query è poco selettiva, tornando praticamente tutti i record, non ci sono ottimizzazioni possibili, se non ridurre la dimensione al minimo.

Se invece hai un mix di query ben preciso (ossia quasi sempre fai un tipo di interrogazione, e poche volte un altro) puoi partizionare la tabella, sia a livello applicativo (shardarla) sia a livello db (mysql >=5.1).

Attenzione però: il partizionamento a livello db richiede una conoscenza pressochè perfetta delle query che verranno poste, altrimenti rischi concretamente di peggiorare (e pure di molto) la situazione
---
Bene, detto questo la versione semplice è:
"una query che torna un milione di righe sarà sempre e comunque lenta".
Se sono dati statistici la soluzione più semplice è storicizzarli mediante tabelle intermedie, ed aggiornare solo i "delta".

Supponiamo che tu voglia conoscere che ne so gli articolo più venduti: invece di lanciare un calcolo sull'intero archivio da sempre a sempre, ti storicizzi tutte le vendite < di 2 mesi fa.
Poi calcoli le vendite tra due mesi fa ed oggi, e le sommi alla tabella "storica".

Cose del genere, insomma