Mysql - ottimizzare query

czero

Mysql - ottimizzare query

Ciao a tutti...
Ho un problemino, o meglio, una curiosità...
prendiamo ad esempio una query tipo:

SELECT * FROM tabella WHERE ntipo=1 ORDER BY TimeStamp DESC LIMIT 0,10

Facendo l'analyze vedo che mysql mi fa un filesort per ordinare i dati e questo rallenta la query

Avete qualche suggerimento su come poter ottimizzare la query/tabella?

Ho fatto diversi test, con indici composti, singoli... ma temo che il problema sia quel "TimeStamp DESC" e purtroppo è imperativo usarlo perchè i dati devono essere ordinati per data decrescente...

saro78

so che è banale ma:
dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

se non vanno bene le indicazioni sopra è perchè evidentemente non si tratta di una semplice SELECT come hai detto ma ci sono di mezzo join, funzioni, condizioni di where, ect.
alla peggio dacci maggiori delucidazioni sulla struttura della tabella e sui dati che vuoi estrarne.

czero

@saro78 said:

so che è banale ma:
dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

se non vanno bene le indicazioni sopra è perchè evidentemente non si tratta di una semplice SELECT come hai detto ma ci sono di mezzo join, funzioni, condizioni di where, ect.
alla peggio dacci maggiori delucidazioni sulla struttura della tabella e sui dati che vuoi estrarne.

Ho già letto e riletto quella pagina ma purtroppo non mi è di aiuto...
Credo sia quel "TimeStamp DESC" che mi provoca l'uso di un filesort... non sarebbe un problema, se in produzione la tabella non fosse di 600.000 record con numerosissime query....

Ora sto' facendo delle verifiche memorizzando il timestamp come numero negativo... in questo modo l'indice sul campo è come se fosse sul campo originale ordinato dal più grande al più piccolo...

I primi test sono confortanti... alcune query con complessità maggiore richiedono un quarto del tempo che richiedevano prima...

saro78

potresti indicarci:

il tipo della tabella (MyIsam, InnoDB, ect.)
il numero di query per secondo/minuto
la tipologia di query sopra (per es: 10%insert, 20%update, 70%select)
il tempo di esecuzione della select di cui stiamo parlando
l'applicativo/i che interroga/no il db
due righe su cosa c'è nella tabella e sul perché delle query che vengono eseguite
?
magari una soluzione si trova, non per forza a livello di indici e/o ottimizzazioni select.

czero

Tabella: myisam
Statistiche: non riesco ad isolarle, il server ha 11 database e questo seppure sia il più grande non è il più usato, vi lascio immaginare gli altri...
Le query sono suddivise indicativamente in:
insert: 30%
update: 0
select: 70%
le insert sono praticamente tutte delayed, le update avvengono solo su altre tabelle del db ma non su questa e comunque con low_priority
La select di cui sopra ha impiegato anche 4 secondi e sono richiamate da una pagina php
I dati sono annunci, quindi titolo,desc, città, flag vari, datapubb

saro78

ci provo, ho una tabella simile alla tua (100000 record) con indici su timestamp e ntipo separati:

se faccio la query come detto da te, mi fa un filesort (1,160 sec)
se faccio la query senza where, niente filesort (0,0030 sec)
se aggiungo il campo ntipo all'indice di timestamp come punto 1)

sei certo di non poter risolvere la where a priori separando i record su più tabelle di ntipo? usare le merged table?

il fatto che siano 600000 record vuol dire che hai 600000 annunci attivi? non puoi cancellare/spostare quelli più vecchi di n giorni?

soluzione estrema: cachare l'html generato dalla query e refresharlo con i dati dal db solo ogni tot minuti, in questa maniera fai la query ogni tot minuti, il resto delle volte gli utenti vedranno a tutti gli effetti un sito statico.

czero

Effettivamente i rapporti dei tempi sono quelli che hai visto anche tu
Non credo che spezzare la tabella sia fattibile, perchè comunque ci sono altre query che non coinvolgono ntipo ma altri campi...
La tabella è gia' stata scaricata rimuovendo gli annunci più vecchi, potrebbe arrivare anche a 800.000 se tolgo dei limiti che ho impostato...

La soluzione estrema è già attiva... ho sviluppato un sistema di cache per le query che mi scarica il db da un po' di elaborazioni...

Attualmente ho individuato 3 attività da fare:

Creare un campo ts reverse da usare negli ordinamenti
Convertire in numerico un paio di campi usati abbastanza di frequente nelle ricerche
Eliminare dei count(*) che caricano....

Così, come indicazione... ti allego uno screenshot delle statistiche del server...

saro78

a proposito di count(*), non è che per caso fai la doppia query per la paginazione degli annunci?
query per la lista e query per il count per sapere quanti sono in totale?

sai già che puoi usare
"select SQL_CALC_FOUND_ROWS * from tabella limit 10"
e poi prendere la count() totale con "SELECT FOUND_ROWS()" ?
così facendo dovresti risparmiare qualcosina, ma è da testare.

czero

in alcuni casi c'e' ancora la doppia query... le dovrei eliminare...
ma il comando che mi dici tu mi mancava... grazie mille!
Ho fatto un test al volo, ed effettivamente si ottiene il count in un baleno... domani mattina modifico la classe di paginazione e provo anche questa... ora sono un po' fuso per modificare script in produzione... :S

czero

A titolo di cronaca... ho trovato questo link che parla di un bug con la found_rows()...

bugs.mysql.com/bug.php?id=18454

Devo trovare il tempo di fare un po' di prove e non ce l'ho... uff