[mySql] query lenta nel trovare il primo codice disponibile tra migliaia di records

[nexus]

Ciao a tutti,
spero di non postare un duplicato, ma non sono riuscito a trovare nulla cercando (forse non inserivo i termini corretti)

vi espongo il problema.

devo eseguire una query che mi mostri qual'è il prossimo codice/id disponibile in una tabella.

questa tabella può contenere codici di lunghezza differente, quindi ho inserito nella query un limite.

questa è la query usata fino ad ora.

SELECT 
   start AS codice 
FROM (
     SELECT codice+1 AS start 
     FROM  `articoli`  
     WHERE figlio='0'  AND codice BETWEEN '0' AND '99999'
) AS a 
LEFT JOIN (
     SELECT codice 
     FROM `articoli` 
     WHERE figlio='0' AND codice BETWEEN '0' AND '99999'
) AS b 
ON a.start=b.codice 
WHERE b.codice IS NULL LIMIT 1 

non ho avuto problemi inizialmente ma ora la tabella è composta da oltre 400 mila records e la query impiega 11 secondi.

c'è un modo per ottimizzarla?

grazie.

[optime]

"codice" è? numerico? alfanumerico? composto?

[nexus]

"codice" è? numerico? alfanumerico? composto?

codice è un campo VARCHAR(20) numerico che può contenere 0 a sinistra, ma anche nulla.
lo tratto come testo anche se è composto da numeri perché ho necessità di mantenere gli zeri a sinistra se presenti.

il campo contiene
codici storici che hanno valori come
1,2 ,3 ,135, 12354, etc..

e valori nuovi che hanno
00001,00002, 00003, 01515, etc..

li distinguo con il campo "figlio"(0,1) TINYINT(1)

[optime]

con questa istruzione dovresti ottenere il codice più alto

SELECT MAX(CONVERT(Codice,UNSIGNED INTEGER)) AS Massimo FROM tabella

[nexus]

con questa istruzione dovresti ottenere il codice più alto

SELECT MAX(CONVERT(Codice,UNSIGNED INTEGER)) AS Massimo FROM tabella

io vorrei cercare il codice progressivo( il primo disponibile), non il codice più alto.

ovvero 12453,12455 dovrebbe trovare il 12454

per quello avevo realizzato una join tra 2 tabelle e cercato il punto in cui non c'è corrispondenza tra le tabelle.

solo che per 3/4 mila righe il problema non si pone.

per 300/400 mila riga il problema si pone

[optime]

hai detto "prossimo" disponibile, non "primo" disponibile

in questi casi o vai di JOIN (cosa che fai già), oppure mantieni una tabella parallela di "buchi"

[nexus]

sto cercando di utilizzare questo tipo di query

SELECT DISTINCT codice, (@rownum:=@rownum+1) FROM `articoli`, (SELECT @rownum:=0) AS r WHERE figlio='0' AND (@rownum:=@rownum+1) != CAST(codice AS UNSIGNED)  ORDER BY codice*1 ASC 

time: 0.5secondi
solo che riscontro dei problemi.
@rownum viene "associato" alla riga prima di essere ordinata, quindi mi risulta come incrementale 12616 quando so che il primo numero disponibile è 16717


ho modificato la query in questo modo

SELECT codice, (@rownum:=@rownum+1) FROM (SELECT DISTINCT codice, figlio FROM articoli ORDER BY codice*1 ASC) AS c, (SELECT @rownum:=0) AS r WHERE figlio='0' AND @rownum != CAST(codice AS UNSIGNED) 

time: 2.4 secondi (meglio di 11.6 della query con il join)
solo che in questo caso , @rownum è sempre diverso da codice anche se a video vedo 1 1

[nexus]

allora, penso di aver ottenuto qualcosa di utile.

se poi qualche guru può ottimizzarla o fare qualcosa di meglio lo ringrazio infinitamente.

nel frattempo....

OBBIETTIVO: trovare il primo id/codice utile in una tabella(senza auto_increment) di 400.000 righe

PUNTO INIZIALE: (esegue la query in 11.3 secondi)

SELECT 
   start AS codice 
FROM (
     SELECT codice+1 AS start 
     FROM  `articoli`  
     WHERE figlio='0'  AND codice BETWEEN '0' AND '99999'
) AS a 
LEFT JOIN (
     SELECT codice 
     FROM `articoli` 
     WHERE figlio='0' AND codice BETWEEN '0' AND '99999'
) AS b 
ON a.start=b.codice 
WHERE b.codice IS NULL LIMIT 1 

PUNTO FINALE (query modificata) esegue la query in 0.34 secondi

SELECT 
              codice-1 AS codice 
FROM (
      SELECT 
             codice, (CAST(codice AS UNSIGNED)-(@rownum:=@rownum+1)) AS test    
      FROM   ( 
                    SELECT 
                           DISTINCT codice  
                    FROM articoli 
                    WHERE figlio='0'   
                    ORDER BY codice*1 ASC   
       ) AS c,   
      ( SELECT @rownum:=0 ) AS r    
      WHERE 1 
) AS d 
WHERE d.test>0 LIMIT 1 

la query è indubbiamente più veloce anche se ha un difetto.
se per caso viene applicata una condizione aggiuntiva per far partire la query da un punto X, il progressivo della colonna temporanea @rownum sballa, perchè parte sempre da 1.
anche se modificando anche il suo punto di partenza potrebbe risolvere.

è da valutare.

[nexus]

una piccola nota.
mi sono reso conto che quella query è in grado di leggere i "buchi" nella tabella, ma non il valore massimo.
quindi l'ho corretta aggiungendo il valore massimo della tabella +1.

SELECT 
    codice-1 AS codice 
FROM (
      SELECT 
             codice, (CAST(codice AS UNSIGNED)-(@rownum:=@rownum+1)) AS test    
      FROM   ( 
                    SELECT 
                           DISTINCT codice 
                    FROM articoli 
                    WHERE figlio='0'   
                    UNION 
                    SELECT 
                            LPAD(MAX(codice)+1, 5, '0') AS codice
                    FROM articoli 
                    WHERE figlio='0'  
                    ORDER BY codice*1 ASC   
       ) AS c,   
      ( SELECT @rownum:=0 ) AS r   
      WHERE 1 
) AS d 
WHERE d.test>0 LIMIT 1 

purtroppo il valore MAX() sembra essere pesante.
la query è passata da 0,34 secondi a 0,67.
ho provato ad eseguire la query
SELECT MAX(codice) FROM articoli WHERE figlio='0'
ed il tempo era 0,29 / 0,3 quindi non sembra essere un problema della UNION, ma di MAX().

gli articoli con figlio =0 attualmente sono 32.000 circa.
un consiglio?

[razzoli]

Consiglio di:

1) Trasformare il VARCHAR in un INT UNSIGNED, molto più piccolo e quindi più veloce. Se il motivo è che vuoi avere gli zeri a sinistra, puoi usare l'attributo ZEROFILL. Poi togli il *1 e il CAST().
2) Consiglio che codice sia chiave primaria e di creare un indice su figlio (che implicitamente diventerebbe figlio + codice).
3) Fare come altri ti hanno suggerito: fai una tabella di numeri da 1 a X e fai una JOIN tra quella tabella e articoli. Seleziona i numeri dalla tab ausiliaria ON numeri.codice = articoli.codice WHERE articoli.codice IS NULL AND articoli.figlio = 0.

La UNION forse è meglio lasciarla (in teoria potresti semplicemente aggiungere una OR, ma non sono sicuro sul query plan).