[mysql] aiuto per ottimizzare/alleggerire una query troppo lunga

[nexus]

ciao a tutti. ho una query un po troppo pesante..causa del fatto che non ho molta esperienza. mi potreste aiutare?

la query permette di recuperare gli impegni da una data_inizio ad una data_fine che:
- non si ripetono.
- si ripetono in base al giorno xx(es ogni 1 del mese) per sempre
- si ripetono in base al giorno xx(es ogni 1 del mese) fino alla data xx.xx.xx
- si ripetono in base al giorno della settimana(es ongi lun o mart etcc.) per sempre
- si ripetono in base al giorno della settimana(es ongi lun o mart etcc.) fino alla data xx.xx.xx

(SELECT * FROM `eventi` 
WHERE ripetibile = '2' 
AND '2008-11-03'>=data_in 
AND '2008-11-03' <= data_fi)
 UNION (SELECT * FROM `eventi` 
        WHERE ripetibile = '1' 
        AND '2008-11-03' <= data_fine_rip 
        AND (
                (
                    '2008-11-03' >= CONCAT('2008-11-',giorno_mese) 
                    AND '2008-11-03' <= ADDDATE(concat('2008-11-',giorno_mese),DATEDIFF( data_fi, data_in )) 
                )
                OR(
                    '2008-11-03' >= CONCAT('2008-10-',giorno_mese) 
                    AND '2008-11-03' <= ADDDATE(concat('2008-10-',giorno_mese),DATEDIFF( data_fi, data_in ))
                )
            )
        )UNION(SELECT * FROM `eventi` 
        WHERE ripetibile = '1'  
        AND (
                (
                    '2008-11-03' >= CONCAT('2008-11-',giorno_mese) 
                    AND '2008-11-03' <= ADDDATE(concat('2008-11-',giorno_mese),DATEDIFF( data_fi, data_in )) 
                )
                OR(
                    '2008-11-03' >= CONCAT('2008-10-',giorno_mese) 
                    AND '2008-11-03' <= ADDDATE(concat('2008-10-',giorno_mese),DATEDIFF( data_fi, data_in ))
                )
            )
        )UNION(SELECT * FROM eventi 
        WHERE ripetibile='1'
        AND    (
                (
                    '2008-11-04' >= DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 7 DAY) 
                    AND 
                    '2008-11-04' <= ADDDATE(DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 7 DAY),DATEDIFF( data_fi, data_in ))
                )
                OR
                (
                    '2008-11-04' >= DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 14 DAY)
                    AND 
                    '2008-11-04' <= ADDDATE(DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 14 DAY),DATEDIFF( data_fi, data_in ))
                )
            )
        )UNION(SELECT * FROM eventi 
        WHERE ripetibile='1'
        AND '2008-11-03' <= data_fine_rip 
        AND    (
                (
                    '2008-11-04' >= DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 7 DAY) 
                    AND 
                    '2008-11-04' <= ADDDATE(DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 7 DAY),DATEDIFF( data_fi, data_in ))
                )
                OR
                (
                    '2008-11-04' >= DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 14 DAY)
                    AND 
                    '2008-11-04' <= ADDDATE(DATE_SUB(STR_TO_DATE(CONCAT(CONCAT('2008',WEEKOFYEAR('2008-11-04 '),giorno_settimana)), '%X%V %W'), INTERVAL 14 DAY),DATEDIFF( data_fi, data_in ))
                )
            )
        ) 

come vedete la query è davvero lunga e piena di ripetizioni che probabilmente sono inutili. sapreste aiutarmi ad ottimizzarla?
grazie in anticipo

[piero.mac]

analizzare una query per ottimizzarla senza conoscere la struttura della tabella ed il significato dei campi e' impresa ardua. cosa differenzia: per sempre, una volta, fino alla data

Forse a qualcuno piace "interpretare" le domande ma e' piu' una tecnica psicologica che una tecnica scientifica. Ottimizzare significa prendere per valido tutto quello che tu hai fatto sinora e questo non e' dato a sapere. Magari lo e' ....

Visto le condizioni di ricerca che richiedi sinceramente farei una query per ogni opzione. Rimarrebbe anche da valutare se la struttura della tabella e' ottimizzata oppure no.

[nexus]

hai ragione.
in pratica voglio realizzare un sistema per memorizzare degli impegni.
nel form ho un campo[data_in] un campo [data_fi] nel caso il mio impegno duri più di 1 singolo giorno.

a quel punto ho un radio button per decidere se l'impegno capiterà una volta o se voglio una ripetiizone automatica.[ripetibile]

ho un altro radio button per decidere come si ripete[tipo_ripetizione] e due rispettivi select uno in base al giorno del mese[giorno_mese] e uno in base al giorno della settimana[giorno_settimana]

un ulteriore radiobutton per decidere se l'evento si ripete per sempre[rip_sempre] o se dura fino ad una certa data[data_fine_rip]

nel db ho:
id | rif_acc | data_in | data_fi | ripetibile | tipo_ripetizione | giorno_mese | giorno_settimana | rip_sempre | data_fine_rip | titolo | desc

[rif_acc] fa riferimento all'account che lo crea e che quindi lo puo modificare. in questo modo non rischio di modificare gl iimpegni dei miei amici

[piero.mac]

mi pare che sia una tabella molto dispersiva e con parecchi dati duplicati.

Andrebbe rivista la struttura del db. Per quanto riguarda le scelte da fare con la query bisognerebbe conoscere meglio il progetto. Una cosa e' l'inserimento, ma un'altra la ricerca dei dati. Parti alla gambero... valuta le cose che devi estrarre ed imposta la struttura per farlo facilmente.

Se poi si tratta di eventi da sottoporre ad uno specifico user basta estrarre tutti gli elementi (non scaduti) e presentarli ordinati a scelta dell'utente stesso. Non ha senso una query come quella che hai postato solo per presentare i dati in un certo ordine.

Se non riesci ad ordinarli come vuoi rivedi la struttura finche' potra' darti una risposta di ordinamento soddisfacente. oppure separa la query in richieste singole.

[nexus]

bhe non devo 'ordinarle' in nessun modo. devo solo estrapolare da una tabella impegni tutti gli impegni possibili. solo che in stile calendario outlook alcuni sono impegni che durano un giorno e basta. altri tipo compleanni magari si ripetono per sempre ogni 23 dell'anno. oppure come l'happyhour di un pub ogni martedì della settimana.

se dovessi rifare il db che non è un problema come potrei fare? alla fine tutti i dati mi servono...a meno che non creo piu tabelle quindi anziche fare una query in lettura del genere faccio 3 o 4 query semplificate che mi uniscano il risultato delle tabelle.

attualmente quella query su ovviamente un carico leggerissimo..(faccio una prova con una 20 di appuntamenti inseriti) ci mette 0.0014 sec è eccessivo immagino se devo pensare che quanti appuntamenti metto in un anno o piu.

[piero.mac]

bisognerebbe vedere un esempio di cosa stampi per capire qualcosa in piu'.

Se devi estrarre "tutti" gli appuntamenti di un certo user ... estrai tutti gli appuntamenti. Che c'entra di che tipo sono, tutti sono tutti. Se li devi dividere per tipo che ti piaccia o no questa divisione e' un ordinamento. I record che estrai hanno un range di tempo? da data_inizio a data_fine?

Tutte quelle union sui record della stessa tabella altro non vorrebbero essere che una successione ordinata di record divisi per tipologia.

vediamo la tabella:

id | rif_acc | data_in | data_fi | ripetibile | tipo_ripetizione | giorno_mese | giorno_settimana | rip_sempre | data_fine_rip | titolo | desc

gli eventi possono essere:

1 - non si ripetono.
2 - si ripetono in base al giorno xx(es ogni 1 del mese) per sempre
3 - si ripetono in base al giorno xx(es ogni 1 del mese) fino alla data xx.xx.xx
4 - si ripetono in base al giorno della settimana(es ongi lun o mart etcc.) per sempre
5 - si ripetono in base al giorno della settimana(es ongi lun o mart etcc.) fino alla data xx.xx.xx

come identificarli

1 - where curdate() = data_in and curdate() = data_fi
(metti nel db data_in = data_fi  questo vale per tutti gli eventi che non si ripetono)

2 - where dayofmonth(data_in) = 1 AND data_fi = '0000-00-00'
(1 = primo del mese) -- data_fi sara' 0000-00-00 per tutti gli eventi che si ripetono

3 - where dayofmonth(data_in) = 1 AND curdate() <= data_fi

4 - where dayofweek(data_in) = 3 AND data_fi = '0000-00-00'
(3 = martedi')

4 - where dayofweek(data_in) = 3 AND curdate() <= data_fin  (3 = martedi')

questa dovrebbe essere la base per selezionare i record. tutti i seguenti campi potrebbero essere descrittivi ma sono campi ripetuti e direi inutili.

| ripetibile | tipo_ripetizione | giorno_mese | giorno_settimana | rip_sempre | data_fine_rip

basta un solo campo "tipo_ripetizione"

se devi mettere dei commenti mettili nella visualizzazione allo user in base alla query che esegui. Non sono dati che possano interessare un database.

riassumendo:

evento unico -> data_in = data_fi
evento eterno -> data_in = data inizio, data_fi = 0000-00-00
evento limitato -> data_in = data inizio, data_fi = data scadenza

tipo_ripetizione --- potresti usare un codice Es.:
0-0 = non ripetibile
1-4 --> 1 = ripetibile mensile - 4=giorno del mese
2-5 --> 2 = ripetibile settimanale 5= giorno della settimana

nel where aggiungerai ad esempio .... and tipo_ripetizione = '1-4'

puoi anche fare una UNION ma anche fare 4 o 5 query select che e' poi la stessa cosa.

[nexus]

mh un esempio di quello che stampo a video è molto difficile..perchè ho un imput che è un form come descritto sopra. in uscita facciosolo tramite mysql al momento..quindi è un po dura.

per il resto un esempio forse molto semplice è questo.

un form con accesso tramite user e pass dove inserisci gli impegni.

in uscita a video vorrei vedere tutti gli impegni di tutti gli utenti che per ripetizione o meno combaciano con la data di oggi.logicamente premendo sul calendario accanto posso vedere gli impegni passati e futuri di quel mese.

se ho una serie di impegni lunedi 1/11 ripetibili per giorno_settimana=martedi allora ogni martedì della settimana mi troverò quell'impegno.

se gli dico che è ripetibile ongi 21 del mese allora ogni 21 del mese me lo ritrovo.

e la ripetizione può avvenire fino ad una data impostata dall'utente o per sempre.

è un po complicato lo so

[piero.mac]

quale che sia il tuo progetto lo devi sapere tu. In base a quanto possibile conoscere ti ho indicato come farei io la struttura a grandi linee. tu che conosci il progetto la farai come credi piu' opportuno.

Ricorda solo che lo script deve essere costruito per gestire il database, e non il contrario cioe' costruire un database in funzione dello script / form.

La tabella che hai fatto mi pare piu' per soddisfare le esigenze dello script che non per custodire dei dati atomici.

Sicuramente non e' che ottimizzando le query risolvi il problema che hai ora esposto. Ci vorra' ben altro.

[nexus]

ok. grazie per il consiglio