Archivi tag: MySQL

Query MySQL con ORDER BY,GROUP BY per ordinare e rimuovere righe doppie

Apparentemente questo è un dilemma piuttosto discusso; molti “niubbi” di MySQL (me compreso) vogliono usare un SELECT su righe di una tabella, dove sanno che il campo ‘a’ ha valori ripetuti più volte, ma vogliono estrarre una sola riga per ogni valore, al tempo stesso facendo in modo tale che la riga estratta, tra tutte quelle disponibili, sia quella che ha il campo ‘b’ col valore più basso, o alto, o lungo, o corto tra tutte le possibili… bene, il primo tentativo che fanno questi niubbi è una query come questa:

SELECT a, b, c FROM table WHERE <conditions> ORDER BY b DESC GROUP BY a

e ottenendo un errore si rendono conto che non è possibile, perché la sintassi MySQL vuole che la clausola GROUP BY venga prima di quella ORDER BY, come nel seguente esempio:

SELECT a, b, c FROM table WHERE <conditions> GROUP BY a ORDER BY b DESC

In questo caso però, le righe prima vengono sfoltite rimuovendo i doppioni del campo ‘a’ (con un criterio che non possiamo scegliere noi), e solo dopo vengono ordinate per ‘b’, e questo non ci serve.

Quindi come si fa per ordinare prima le righe così come ci servono, e poi rimuovere quelle di troppo?

Per essere brevi, ecco come:

SELECT t.a, t.b, t.c
  FROM table t
  JOIN (SELECT MIN(b) as b
    FROM table
    GROUP BY a) t2 ON t2.b = t.b
  WHERE <condizioni>
  GROUP BY a
  ORDER BY NULL

Quello che fa questo “scheletro di query”, è ordinare le righe per il campo ‘b’ (in senso crescente, cioè ASC, siccome è stata usata la  funzione MIN(), altrimenti potreste usare MAX() per un ordinamento decrescente, e/o altre funzioni in base al tipo di ordinamento che vi serve) all’interno dell’operatore JOIN, e quindi raggruppa per valori unici del campo ‘a’ le righe nella query esterna. L’operatore ORDER BY NULL serve a evitare che l’interprete MySQL perda tempo a ordinare le righe risultanti, siccome un ordinamento viene già fatto con l’operatore GROUP BY. La query è efficace, ma poco efficiente, siccome risulta molto lenta.

All’inizio pensavo che mi sarei dovuto accontentare, tuttavia non soddisfatto, siccome imparando PHP e MySQL sono col tempo diventato ossessionato con l’ottimizzazione per le prestazioni, ho pensato di provare un approccio alternativo, cioè richiedere via SQL le righe “grezze”, e poi rielaborarle successivamente in PHP.

Esaminiamo questo approccio:

$results=doquery("
  SELECT a, b, c
    FROM table
    WHERE <condizioni>
  ");
$datagrid=array();
while ($slot=mysql_fetch_array($results)) $datagrid[]=$slot;
sort($datagrid);
$compare="";
foreach ($datagrid as $k=>$row)
  if ($row[0]==$compare)
    unset($datagrid[$k]);
  else
    $compare=$row[0];

Alla fine di questas routine, $datagrid contiene tutti i dati di cui avete bisogno, e solo quelli; spieghiamo:

  • la query SQL restituisce le righe non processate che corrispondono alle condizioni di WHERE, quindi con i doppioni del campo ‘a’, e senza nessun ordinamento particolare
  • dopo aver inserito i campi delle righe in un array multidimensionale, la funzione PHP sort() si occupa dell’ordinamento dell’array in base agli elementi dei sotto-array, prima per subarray[0], poi per subarray[1] e così via (apriamo una parentesi su questo discorso: se cercate su internet “ordinamento di un array multimensionale in PHP” -meglio se in inglese-, troverete ogni tipo di script artigianale, come se la sola funzoine sort() funzionasse solo sugli array monodimensionali; beh, io ho PHP5 sul mio hosting, ed ho provato a passare il suddetto array multimendionale a sort(), sorpresa delle sorprese l’ha messo in ordine esattamente come serviva a me; se però per voi non dovesse funzionare, recatevi sulla pagina di php.net dedicata alla funzione sort, e nella sezione dei commenti troverete molti esempi già compilati di script “casalinghi”, anche se non saranno mai veloci come sort() che è una funzione propria di PHP)
  • la stringa $compare è inizializzata, e usata nel successivo ciclo foreach per fare un unset() di tutti i subarray dove l’elemento [0] (il nostro campo ‘a’) è uguale a quello del precedente subarray, quindi rimuovendo i duplicati oltre la prima riga

Dopo di ciò potete passare l’array risultate ad un’altra routine per processare i dati così come vi servono.

Per portare la performance ancora oltre, nel caso abbiate bisogno di eseguire una procedura semplice sui dati ottenuti, potete modificare il ciclo foreach dell’esempio come segue:

foreach ($datagrid as $row)
  if ($row[0]!=$compare) {
    $compare=$row[0];
    <routine di elaborazione in questo spazio>
  }

Chiarendo, questo ciclo foreach non si occupa di eliminare i subarray duplicati, ma si limita ad eseguire la routine di elaborazione solo sulla prima istanza di un subarray, ignorando le copie successive; in questo modo, tagliate diverse volte sui tempi di esecuzione:

  1. risparmiate il tempo di un secondo foreach per scorrere l’array ed eseguire le operazioni di elaborazione, siccome queste vengono lanciate direttamente dal primo foreach
  2. evitate di lanciare la funzione unset() tante volte quanti sono i doppioni
  3. risparmiate i cicli di CPU e la memoria necessari a definire un secondo array per contenere i dati sfoltiti, siccome state lavorando direttamente sul primo array costruito sui risultati della query
  4. semplificate il foreach perché non richiedete più un elemento associativo ($k=>$row) ma solo il valore dell’elemento

Con i miei benchmark (su un set di dati MOLTO ristretto, lo ammetto), ho misurato un incredibile aumento di prestazioni di cento volte (già, 100 volte più veloce) per l’ordinamento/sfoltimento in PHP, paragonato al “doppio GROUP BY” in MySQL (0.08 secondi per MySQL/PHP, contro 8 secondi per la query complessa, su 100 iterazioni). I vostri risultati potrebbero essere diversi, ma partendo da un vantaggio di cento volte, c’è molto spazio di manovra.

Ricerca invertita in MySQL: controlla se le parole chiave nel campo corrispondono ad un testo

Di solito, “cercare in un database” significa cercare quelle righe che contengono un “documento” in un campo, che corrisponde a delle parole chiave (di solito usando la funzione MATCH AGAINST); mettiamo invece che vogliate fare il contrario, cioè avete una tabella con delle righe che contengono un campo in cui avete memorizzato delle parole chiave, e volete selezionare solo quelle righe in cui le parole chiave corrispondono ad un testo o un particolare documento. Questa è quella che io chiamo una ricerca invertita.

Uno scenario reale: avete un sito di offerte di lavoro, ed i candidati cercano annunci di loro gradimento, ma non ne trovano nessuno, oppure ce ne sono troppo pochi e non interessanti. O continuano a collegarsi al sito ripetendo la ricerca periodicamente (ad esempio con le parole chiave “veterinar* gatt*“, perché vogliono prendersi cura degli animali, e hanno un debole per i gatti in praticolare), o si iscrivono al feed RSS di quella ricerca (se ne avete uno)… oppure “salvano” la ricerca e aspettano che sia il sito ad inviare loro una mail non appena compare un nuovo annuncio pertinente, ed è qui che torna utile questo articolo.

Salvare le parole chiave per ogni utente in una tabella MySQL è semplice, ma quanto è semplice verificare se un annuncio appena inserito corrisponde alle parole chiave? In PHP, costruireste un array delle parole chiave, e per ogni elemento richiamereste strpos() per verificare che siano tutte contenute nel testo.

In MySQL la funzione INSTR() è l’equivalente di strpos() in PHP, ma non potete salvare degli array in un campo (intendo array veri e propri, non serializzati), e d’altra parte salvare tante righe quante sono le parole chiave scelte da ogni utente è sconveniente, siccome la normalizzazione non è necessaria: un “insieme di parole chiave” è quasi sempre unico per ogni utente che si iscrive ad una ricerca, per cui ha senso salvarlo in un campo testuale nella forma di “parola1,parola2,parola3,…“; anche qui, in PHP useremmo explode(“,”,$parole) per ottenere un array, ma in MySQL non esiste nessuna funzione simile, per cui è necessario crearne una.

Idealmente, avreste bisogno di una funzione a cui passare il testo/documento in vostro possesso (la somma di titolo e corpo dell’offerta di lavoro, in questo caso), la stringa nella forma “parola1,…,parolaN“, e un parametro per indicare che il delimitatore (“colla” nel caso di implode/explode) è una virgola, quindi:

funzione(document, keywords, delimiter)

Questa funzione dovrebbe restituire un valore intero positivo se tutte le parole chiave sono presenti nel testo, oppure 0 in caso contrario.

La funzione che ho messo assieme incollando informazioni prese un po’ dovunque è:

DELIMITER ;;

DROP FUNCTION IF EXISTS `matchkwdarray`;;
CREATE FUNCTION `matchkwdarray`(`str` text, `arr` text, `delimit` tinytext) RETURNS char(1) CHARSET utf8
 DETERMINISTIC
BEGIN
 DECLARE i INT DEFAULT 1;
 DECLARE matches INT DEFAULT 1;
 DECLARE token TINYTEXT;
 label:WHILE(matches>0) DO
 SET token=replace(substring(substring_index(arr, delimit, i), length(substring_index(arr, delimit, i - 1)) + 1), delimit, '');
 IF token='' THEN LEAVE label; END IF;

 SET matches=INSTR(str, token);
 SET i=i+1;
 END WHILE;
 RETURN matches;
END;;

DELIMITER ;

Per aggiungere questa funzione al vostro database MySQL, potete collegarvi sia tramite CLI, sia usando una interfaccia web al database, come dovranno fare tutti gli utenti su un hosting condiviso, non avendo accesso alla linea di comando; phpMyAdmin, rigonfio com’è, non supporta comunque routine e funzioni, per cui avete bisogno di un altro DB manager, ed io ho scelto Adminer che svolge il suo lavoro egregiamente, più rapido di phpMyAdmin, e tutto in un piccolissimo singolo file PHP di circa 150kb.

Un esempio d’uso della funzione:

SELECT matchkwdarray('questo è solo un piccolo test molto semplice', 'solo,test', ',')
Restituisce: 2

SELECT matchkwdarray('questo è solo un piccolo test molto semplice', 'casa,albero', ',')
Restituisce: 0

SELECT matchkwdarray('questo è solo un piccolo test molto semplice', 'solo,test,semplice,albero', ',')
Restituisce: 0

Questa funzione è efficiente: appena verifica che una parola chiave non è contenuta nel testo interrompe la procedura e restituisce subito 0; ovviamente, se non cercate una precisione del 100% ma vi accontentate di una corrispondenza inferiore, potete modificarla per verificare comunque tutte le parole chiave, e restituire una percentuale di corrispondenza; potete anche rimuovere l’ultimo carattere dalle singole parole in modo tale che siano ammesse più varianti delle stesse.

Una query MySQL SELECT con numerose condizioni WHERE è più veloce

Per un certo motivo volevo aggiungere più condizioni WHERE ridondanti all’interno di una query MySQL SELECT, ma temevo di rallentare l’esecuzione della stessa.

In breve, sto lavorando a questo sito di annunci, dove per ogni oggetto c’è una tabella contenente tra le altre le righe comune_id, provincia_id e regione_id. Conoscendo il valore di comune_id, è possibile usare la query:

SELECT * FROM oggetti WHERE comune_id='12'

invece di:

SELECT * FROM oggetti WHERE comune_id='12' AND provincia_id='34' AND regione_id='56'

siccome i risultati sono gli stessi… ma conviene?

Ho preparato un veloce test:

include "db.inc.php";
$creationstart=strtok(microtime()," ")+strtok(" ");
for($i=0;$i<=999;$i++)
 $test=doquery("SELECT * FROM oggetti WHERE comune_id='58003'");
$creationend=strtok(microtime()," ")+strtok(" ");
$creationtime=number_format($creationend-$creationstart,4);
echo "solo comune: $creationtime<br />";

$creationstart=strtok(microtime()," ")+strtok(" ");
for($i=0;$i<=999;$i++)
 $test=doquery("SELECT * FROM oggetti WHERE comune_id='58003' AND provincia_id='58' AND regione_id='12'");
$creationend=strtok(microtime()," ")+strtok(" ");
$creationtime=number_format($creationend-$creationstart,4);
echo "tutti i parametri: $creationtime<br />";

A sorpresa, ci è voluto un tempo variabile dai 2.4s ai 4.5s per eseguire le 1000 iterazioni con “tutti i parametri”, mentre la versione “solo comune” richiedeva circa 0.4s in più. All’inizio ho pensato che si trattasse della cache MySQL in azione, ma invertendo la posizione delle due routine all’interno del codice i risultati sono rimasti gli stessi.

Quindi, anche se vi sembra ridondante, aggiungere più clausole WHERE ... AND ... nelle vostre query accelera l’esecuzione della ricerca.

This article has been Digiproved