Newsgroup: it.comp.software.database
Titolo: [MYSQL] Utilizzo delle key per migliorare le performances

Buongiorno a tutti.
Sto facendo dei test su Mysql (4.0.16-nt) e trovo dei dubbi di performance
su una query molto semplice.
Ho creato un database di test, una tabella così strutturata (vogliate
scusare l'allineamento delle colonne... eventualmente fate un copia-incolla
su notepad o equivalenti con carattere fixed...):
+----------+-----------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+-----------------+------+-----+---------+----------------+
| ID       | int(10) unsigned |      | PRI | NULL    | auto_increment |
| Codice   | char(18)        |      | MUL |         |                |
| Quantity | double          | YES  |     | NULL    |                |
+----------+-----------------+------+-----+---------+----------------+
e come chiavi:
+-----------+------------+----------+--------------+-------------+----------
-+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+----------
-+-------------+----------+--------+------+------------+---------+
| movimenti |          0 | PRIMARY  |            1 | ID          | A
|    871492   |     NULL | NULL   |      | BTREE      |         |
| movimenti |          1 | Codice   |            1 | Codice      | A
|     11619   |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+----------
-+-------------+----------+--------+------+------------+---------+
Ho riempito la tabella tramite un mio programma, che ha generato una
sequenza di Codici e un numeo casuale di movimenti con quantità negativa o
positiva casuale. Arrivato a 871492 records, ho interrotto il riempimento.
Dopo aver ottimizzato la tabella, ho eseguito questa semplice query:
SELECT Codice, Sum(Quantity)
FROM movimenti
GROUP BY Codice
HAVING Codice="H0123";
ottenendo come tempi di risposta medi di 1,80 secondi.
Facendo un ambiente identico su Access 2000, avevo ottenuto un risultato
istantaneo (!!!).
Come da manuale, ho iniziato ad investigare e ho trovato che il risultato di
EXPLAIN SELECT Codice, Sum(Quantity)
FROM movimenti
GROUP BY Codice
HAVING Codice="H0123";
table = movimenti
type = ALL
possible_keys = NULL
key = NULL
key_len = NULL
ref = NULL
rows = 871492
Extra = Using temporary; Using filesort
Infatti, togliendo l'indice sul campo codice, le performance sono identiche.
Dove sto sbagliando? E' possibile far sfruttare meglio gli indici al motore
MySql e migliorare le performance?
Grazie per l'aiuto
Matteo

 HAVING Codice="H0123";
Se usi HAVING sullo stesso campo in cui fai il GROUP, dici al DBMS di
fare il GROUP di tutto, e poi isolarti solo la riga (le righe) che 
corrispondono alla richiesta. I tempi di risposta dovrebbero essere gli
stessi che avresti se chiedessi il GROUP BY senza HAVING.
Se invece usi WHERE, la risposta e' istantanea, perche' il GROUP e' 
eseguito solo sulle righe che soddisfano la richiesta.
Se fai un EXPLAIN con WHERE, ti accorgi che, anziche' 871492 righe, ti 
dira' che deve esaminarne N, dove N e' il numero di righe che contengono 
il codice richiesto.
HAVING si adopera solitamente per limitare il risultato dopo un calcolo.
Per esempio, ha senso dire
SELECT Codice, Sum(Quantity) as somma
FROM movimenti
GROUP BY Codice
HAVING somma > 1000;
In questo caso non puoi usare "WHERE" perche' la somma non e' nota prima 
del calcolo.
Evidentemente Access sposta la clausola HAVING al posto di una WHERE. 
Anche MySQL usa questo trucco, ma solo nelle query senza GROUP BY.
Ricorda che con HAVING allunghi i tempi di risposta, mentre con WHERE li 
accorci.
gmax

 il codice richiesto.
Sostituendo Where a Having, mi solleva un errore:
mysql> SELECT Codice, Sum(Quantity)
    -> FROM movimenti
    -> GROUP BY Codice
    -> WHERE Codice="H0123";
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'WHERE Codice="H0123"' at line 4
Ho commesso un errore nell'applicare il suggerimento?
Grazie per l'interessamento.
Matteo

 il codice richiesto.
Sono riuscito ad ottimizzare l'esecuzione della query sfruttando una tabella
temporanea:
CREATE TEMPORARY TABLE IF NOT EXISTS TMP (`Quantity` DOUBLE);
INSERT INTO TMP SELECT movimenti.Quantity FROM movimenti WHERE
movimenti.Codice='H0123';
SELECT SUM(quantity) FROM TMP;
DROP TABLE tmp;
In questa maniera il risultato della query, dentro mysql, lo ottengo in
circa 0.09 secondi. Il problema è che la preparazione di queste 5 query
(ottimizzabile al massimo in 3), moltiplicato per molte volte produce un
ritardo piuttosto significativo dentro la mia applicazione di test (Delphi
7, componenti di connessione Zeos)...
C'è un modo per risolvere il problema utilizzando solamente una query? Le
sub-select (implementate dalla versione 4.1.0-alpha, che ho installato per
le prove) possono essere d'aiuto?
Ciao e grazie
Matteo


la clausola "WHERE" va messa prima di GROUP BY
gmax

 la clausola "WHERE" va messa prima di GROUP BY
Funziona perfettamente. Il tempo di risposta è nell'ordine di pochi
centesimi di secondo.
Mi viene quindi da pensare che l'engine di ottimizzazione delle query "fatte
male" di Access è migliore di quello di mysql...boh
Ciao
Matteo