Query su Mysql per data

[jm.keynes]

Salve a tutti, mi sono appena iscritto.

Ho un problema su una query su Mysql
C'è un campo Data nel database nel classico formato
YYYY-MM-DD HH:MM:SS quindi un datetime, il problema, è che anche se indicizzato le query fanno una ricerca full-text e su 9 milioni di record usando un'applicativo in php il tutto va in timeout.

Ad esempio

select * from nome-databse
where month(campo_data) = 11

se utilizzo explain posso leggere che l'indice non viene utilizzato.

Ho provato anche con

explain
select * from nome-database where
campo-data between '2007-11-01 00:00:00' and '2007-11-12 23:59:59'

con
explain select cast(campo-data as date) from nome-database

sembra che usi l'indice, ma appena inserisco un altro campo l'indice viene ignorato.

E' forse un problema di datetime?

[ntd]

9 milioni di records? Porongo!

Comunque MySQL fa esattamente quello che ci si aspetta. Se vuoi ottimizzare questa query:

select * from nome-databse where month(campo_data) = 11

devi "esplodere" il campo campo_data nei suoi componenti (giorno, mese, anno, ora, minuti) e indicizzare mese. Avere l'indice su campo_data non apporta alcun beneficio se ricerchi per mese.

Con una base dati così grande, devi valutare attentamente ogni query necessaria per evitare in tutti i modi di scorrere l'intera tabella.

[jm.keynes]

Purtroppo i dati non sono neanche annuali, in tutto l'anno il database raggiungerà anche 18 milioni di record.

Altro porblema è che sono costretto ad usare i caratteri % e questo comporta necessarimanete un abbandono degli indici, al di là del campo tempo, che in fase di insert potrei splittare in giorno\mese\anno\ora\minuti\secondi magari con un trigger, ho il problema citato, ovvero quando inserisco condizioni come where...and i campi che in cui vado a cercare sono popolati da valori random, tipo elemento-comune-gygg77676 quindi un valore alfa-numerico a seguire per ogni valore, per cui devo utilizzare un qualcosa tipo

where
nome-campo like 'elemento-comnune%'
and . . .

e così via per almento altre 2 o 3 colonne, e questo comporta un full-text, di certo i 4 Xeon dual core e 8 Gb di ram aiutano ma sto valutando come portare al massimo le prestazioni di un mysql installato su una macchina dedicata.

[ntd]

Originariamente inviato da jm.keynes
Altro porblema è che sono costretto ad usare i caratteri % e questo comporta necessarimanete un abbandono degli indici [...]

Non è assolutamente vero: costrutti come LIKE 'partecostante%' usano gli indici, se esistenti.
http://dev.mysql.com/doc/refman/5.0/...l-indexes.html

Medita, keynes, medita...

[jm.keynes]

si, mi sono espresso male, volevo dire che mi sembra più logico utilizzare degli indici composti, invece usa o uno o l'altro a seconda dei campi e questo modo di ragionare di mysql mi lascia un pò perplesso.
Ad esempio per il valore del campo data, ho fatto delle prove se uso un range di 29 giorni, dal primo del mese al 29 utilizzo come incice il campo data, appena supero il trentesimo giorno, mysql preferisce fare la scansione dell'intera tabella. Lo stesso, se ci sono più indici, e dovrebbe essere sensato usarne più di uno, lui ne utilizza uno solo...

[ntd]

Ho sempre trovato logiche le ottimizzazioni di MySQL, ma è anche vero che non ho mai fatto query incredibilmente complesse.

Perché non vai sul pratico e posti una query che secondo te dovrebbe essere ottimizzata ma non lo è, includendo il dump della struttura delle tabelle interessate?