All posts by ephestione

Infiltrazioni di acqua nella Rover 75

Fino ad ora ho sperimentato due tipi di infiltrazioni di acqua nella mia Rover 75. Una di queste (si suppone) sta causando la mancanza di alimentazione alle pompe del carburante (sia quella anteriore che quella interna al serbatoio), per cui nel 99% delle volte girando la chiave nel cruscotto, comunque non giunge alcuna differenza di potenziale ai motorini e la macchina non è in grado di avviarsi; questa infiltrazione è dovuta all’accumulo di acqua in una vaschetta appena aldisopra della centralina e dei fusibili, dove un tubo di scarico di gomma dovrebbe prevenire la cosa, ma non ci riesce se vi capita di vivere in un qualche posto dove foglie e semi cadono frequentemente dagli alberi sulla vostra macchina.

rover 75 sotto il cofano
L'area sotto il cofano dove avviene il misfatto, la vasca d'acqua è subito vicino alla cerniera del cofano lato passeggero.
tubo di scarico di gomma
Questa vaschetta appare sporca, ma in realtà è stata abbondantemente pulita, figuratevi cosa poteva esserci prima. Inutile a dirsi, si era riempita d'acqua, che poi è debordata all'interno della macchina, cadendo sopra la centralina e la scatola dei fusibili, per finire sul tappetino lato passeggero.
tubo di scarico
Mio padre sostiene che lo scopo di questa forma sia quella di impedire che i residui solidi finiscano nel vano sottostante lo scarico, permettendo il passaggio solo dell'acqua. Io invece penso che sia semplicemente una progettazione stupida. La prima cosa solida che finisce dentro il tubo tappa l'uscita... nel mio caso si era riempito tutto di semi di pino quasi fino all'orlo.
tubo di scarico di gomma tagliato
Problema risolto, ho tagliato via la punta a becco di flauto ed ora il passaggio è aperto.

Ho scoperto la seconda infiltrazione per puro caso. Come dicevo la pompa del gasolio anteriore era ferma, e sono andato a controllare anche quella posteriore, quindi ho rimosso il sedile posteriore… e sorpresa, ho trovato una piscinetta d’acqua in un punto affossato della base metallica subito dietro al coperchio dell’alloggiamento della pompa interna al serbatoio. Ho seguito il passaggio dell’acqua sin dentro il bagagliaio, ma lì le tracce diventavano scarse, siccome era quasi tutto asciutto, anche se ho notato per caso una macchia sulla parete laterale.

rover 75 bagagliaio
Ho asciugato l'acqua stagnante all'angolo del bagagliaio con lo spazio della ruota, si può notare chiaramente l'area bagnata tutta attorno.
rover 75 infiltrazione bagagliaio
Questa macchia verticale ha tradito l'infiltrazione in questa zona del bagagliaio.
rover 75 decorazione argento
La freccia rossa punta alla zona dove l'acqua si insinua sotto la decorazione cromata e passa attraverso il buco della vite che la fissa in posizione.
guarnizione difettosa
Questo è il colpevole, la guarnizione rotonda premuta contro il metallo lasciava comunque passare l'acqua dall'esterno.
rover 75 doccia bagagliaio
Ho rimontato tutto per testare la tenuta della guarnizione, quindi ho innaffiato abbondantemente la zona sospettata di infiltrazione (notare l'arcobaleno di cortesia)
infiltrazione d'acqua nel bagagliaio
Eccovelo, un rivoletto di acqua scende direttamente dalla guarnizione.
acqua nel bagagliaio
Piccola piscina formatasi nel bagagliaio nello stesso punto dove avevo già asciugato l'acqua presente (che si era accumulata in quantità molto superiori). Non appena si frena o si marcia in discesa, l'acqua cola in avanti e va ad accumularsi proprio sotto il sedile posteriore.

Ho aggiunto generose quantità di silicone in corrispondeza del foro nella carrozzeria dove passa la vite, sia dall’esterno che dall’interno, ed ho rimontato tutto; dovrebbe essere sufficientemente isolato per ora. Per sicurezza, ho ripetuto l’isolamento con silicone su tutte le altre guarnizioni simili (ce ne sono due per lato).

Il problema della mancata alimentazione alle pompe del gasolio permane, questa macchina continua a prendermi in giro; non appena sembra che la cosa sia risolta, basta riprovare a girare la chiave nel quadro, e nuovamente le pompe sono morte. A questo punto, sospetto un cavo di alimentazione ossidato, o qualcosa del genere.

Rover 75 water leakage

Until now I have experienced two kinds of water leakage in my Rover 75. One of them is (supposedly) causing a failure of the fuel pump power output, so 99% of the times the fuel pumps (both under bonnet and in tank) receive no power and the car isn’t able to start; this leakage is due to the water piling up in a small “pool” right above the BCU and the internal fusebox, where a rubber drain is supposed to prevent this occurence, but fails to do that if you live someplace where leaves and seeds from the trees fall on your car.

rover 75 under bonnet
The area under the bonnet, the "pool" is right beneath the hood hinge.
rubber water drain
This area looks terribly dirty, but it's already been cleaned, so go figure what was inside there before. Needless to say it was filled by water, which fell inside the car, falling onto the fusebox area and wetting the passenger carpet.
rubber drain
My father says the meaning of this is to prevent dirt pieces from falling under... I say instead it's just stupid engineering. The very first solid thing that falls in there is bound to clog the drain... in my case, a whole load of pinetree seeds filled the drain up to the top.
rubber drain cut
Problem solved, I cut off the tip making the exit wide open.

The other drain, I discovered by pure chance. As I said, I investigated the first drain after the pumps failed to start, so I thought the pumps actually failed; I went and checked the in-tank pump, by pulling out the back seat; behold the surprise, there was another quite big pool of water in the recessed space close to the in-tank pump housing cover. I was able to trace the water stains up to the trunk, close to the rear lights; it was difficult to find the cause because everything was mostly dry, but I noticed a slight running water stain.

rover 75 trunk
Water was stagnating in the corner of the wheel area, and caused a major wetting of everything close to that area.
rover 75 trunk leakage
The vertical stain running on the metal gave away the water leakage.
rover 75 silver lining
The red arrow points to the part were the silver decoration has a screw running into a hole, and that's where the water came in.
faulty gasket
This is your culprit, the round gasket pressing against the metal lost its ability to isolate the inside from the outside.
rover 75 trunk shower
I putted back everything together, and tested the gasket, even if I was already sure it was faulty. So I showered the area where I suspected the leakage to be (notice the nice courtesy rainbow)
trunk water leakage
There you have it, a fresh water stream coming from the faulty gasket.
trunk water pool
Small pool formed in the trunk after the test (I had just dried the old pool, which was way larger than this). As soon as you brake, or are driving down a slope, the water streams donwards toward the backseat area, where it forms a secondary pool like the one I found.

I generously added silicone to the gasket area, both from outside and inside, and put everything back together, should be pretty much isolated by now. For the sake of it, I did the same treatment for all the accessible gaskets of the silver lining (there are two on both sides).

My no-power-to-fuel-pumps problem is not yet solved though, this car likes to play games, and when I see the pumps are working again, soon after, at the next engine start, they aren’t anymore. At this point, I suspect a faulty wire.

Il driver di rete bridged Virtualbox rallenta la velocità della LAN

Quando sono passato dal WiFi ad una connessione fisica con cablaggio CAT5E dal router nell’altra stanza a questo PC, sono saltato da velocità di trasferimento dei file di massimo 2-3MB/s (ma quasi sempre 1MB/s, spesso meno) a 11-12MB/s.

Ultimamente ho notato come la velocità per scaricare alcuni fansub dal server domestico era scesa a 5-6MB/s, esattamente la metà di quella a cui ero abituato; dall’icona di rete nell’area di notifiche di Windows 7 ho notato che era presente un secondo adattatore di rete, che ho fatto risalire alla scheda virtuale di rete in modalità bridged installata da Virtualbox. Siccome l’avevo recentemente installato per testare una cosa di cui ora non ho più bisogno, ho disinstallato Virtualbox, e non appena il driver dell’adattatore di rete virtuale è stato rimosso, la velocità tornata a 11-12MB/s.

Questo dovrebbe essere un comportamento normale?

Virtualbox bridged network adapter driver slows down LAN speed

When I upgraded from WiFi to a physical CAT5E cable running between my router in the other room and this desktop, I jumped from 2-3MB/s tops (1MB/s usually, sometimes less) to 11-12MB/s speeds during file transfers from my home server.

Lately I was checking that the speed to download some fansubs off the server got down to 5-6MB/s, exactly half of what I was used to; I noticed there was another non specified adapter listed in Windows 7’s statusbar icon hover, and I traced it to Virtualbox’s bridged network adapter. Since I recently had installed that to test something for which I had no need anymore, I simply uninstalled Virtualbox, and as soon as the virtual network card driver was gone, speeds were back to 11-12MB/s.

Should this be in any way a normal behaviour?

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.

MySQL query with ORDER BY,GROUP BY to remove duplicate rows after ordering

Apparently this is a widely discussed issue with MySQL; many newbies (like me) wanting to SELECT rows from a table, where they know there are duplicates of some field ‘a’ which they want unique, but making sure that the selected row has the field ‘b’ with the lower, highest, longest or shortest (whatever) value among the duplicates… well, these newbies first try and make a query like:

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

and they get errors and realize it’s not possible, because MySQL syntax wants the GROUP BY clause to come before the ORDER BY one, like:

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

In this case though, rows first get grouped, and only after duplicates are removed, they get ordered (pretty much useless for what we need).

How to go about first ordering duplicates the way we need, and then remove the rest?

To cut to the chase, this is how:

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 <conditions>
  GROUP BY a
  ORDER BY NULL

What this “query skeleton” does, is ordering the rows by the field ‘b’ (ASC order, since MIN() function is used, otherwise you would be using MAX(), or anyway the function that better suits your needs) inside the JOIN operator, and then grouping these rows by field ‘a’ in the outer query; the ORDER BY NULL clause at the end is to avoid MySQL doing unnecessary ordering steps as the rows have already been ordered by the GROUP BY clause. Effective, sure, but incredibly slow query.

I thought I could go on by just using this, yet I wasn’t satisfied, as I became a performance optimization junkie during my PHP/MySQL learning curve. So I thought, what if I just get the raw rows, and order them later as I need in a PHP array?

So, let’s examine this approach instead:

$results=doquery("
  SELECT a, b, c
    FROM table
    WHERE <conditions>
  ");
$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];

After this, the multidimensional array $datagrid contains the data that you need. Some explaining is in order:

  • the SQL query returns the unprocessed rows corresponding to the WHERE conditions you specified, so you will get duplicate rows, in an arbitrary order
  • after inserting the rows data inside a multidimensional array, the sort() PHP function takes care of sorting this array depending on the sub-arrays in it, first by subarray[0], then by subarray[1] and so on (let’s open a parenthesis here: if you search for “php sort multidimensional array” on the internet, you will find all sorts of hand-made custom scripts to do this, as if sort() alone wasn’t enough; well, I have PHP5 on my hosting, and I threw a multidimensional array at it… it got splendidly sorted just as I needed, so it worked for me; in case it doesn’t for you, look on php.net function_sort user comments, there are all kinds of custom multi-sorting scripts in there, even if nothing is as fast as just sort() which is a builtin function)
  • a $compare string is initialized, and used on the following foreach loop to unset() all the subarrays where the element [0] (our ‘a’ field) is the same as the previous subarray’s [0], thus effectively pruning duplicates after the first occurrence

After this you can pass the resulting array to another routine which will process it as needed.

To take performance even further, in case you need to do a simple processing on the resulting ordered&grouped array, you can change the last foreach of the previous routine into:

foreach ($datagrid as $row)
  if ($row[0]!=$compare) {
    $compare=$row[0];
    <processing routine in here>
  }

Again, to explain, this foreach loop doesn’t care about unsetting the duplicated subarrays, but just runs your processing code on the first occurence of a subarray, ignoring the others; with this approach, you are cutting several times on the excecution time:

  1. you presumably save time as you don’t need another foreach loop to process the pruned multidimensional array, since you do everything in the first and only foreach
  2. you avoid issuing as many unset()‘s as the duplicate rows are
  3. you save on cycles and memory needed to define a new array to store the pruned data, as you only work on the very first array you built on the SQL results
  4. you simplify the foreach because you are not requesting an associative result anymore ($k=>$row) but only request the element value

With my benchmark (on a VERY reduced sets of rows, I must admit), I measured a performance increase of a whopping one hundred times (yes, exactly 100X faster) for the PHP ordering/pruning, if compared to MySQL “double-GROUP BY” approach (0.08 seconds for MySQL/PHP, against 8 seconds for MySQL only, for 100 iterations). Your results may vary on very large, or of a different kind, data sets, anyway testing will not hurt. From my 100x speed experience, there’s still plenty of leeway space.

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.

Inverted search in MySQL database: check if keywords in field match some text

Usually, “searching a database” means looking for rows that contain a “document” in a field that matches a certain keywords set (mostly using the MATCH AGAINST construct); let’s say you want to do the opposite instead, that is finding those rows that have a field containing keywords, or a phrase, that matches to a text you already know. This is what I call an inverted search, or reverse search.

Real-life scenario: you build a website for job offers, candidates come in searching for listings that suit them, but find none, or find just a few that are not satisfactory. Either they keep coming to the website doing a search (let’s say they use the keywords “veterinar* cat” because they want to take care of animals, and like cats above all), or they subscribe to the RSS feed for that search (if your site offers one)… or they “save” this preference, and have the site send them an email as soon as a new offer pops up that matches their search, and this is where this article comes in.

Now, it’s easy to save the keywords for each user in a MySQL table, but how easy is it to check if the text of new messages matches said keywords? In PHP, you would go about building an array of keywords, and using strpos() on each of them to see if they are all contained in the text.

In MySQL you have the INSTR() which is the equivalent of PHP’s strpos(), but you cannot store arrays in a field (I mean real arrays, not serialized ones), and storing as many rows as the keywords are is going to be troublesome, as normalization isn’t needed: a “set of keywords” is mostly unique to each user subscribing to a search, so it makes sense to store them in a text field in the form of “keyword1,keyword2,keyword3,…”; again, if you were in PHP, you would do a explode(“,”,$keywords) to obtain the array of keywords, but in MySQL there is no such function, so you pretty much have to create one.

Ideally, you need a function where you pass the known document/text to match against (the concatenated subject and the body of the job offer, in this example), the string in the form “keyword1,…,keywordN“, and a parameter which tells the function that the delimiter (“glue” in PHP’s explode/implode) is a comma, so:

somefunction(document, keywords, delimiter)

This function would then return a positive integer if all of the keywords are present in the text, or 0 otherwise.

The function I put together by scraping info around was:

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 ;

To add this to your MySQL database, you can either connect via CLI, or, as most people with a website on a shared hosting, not having command-line access, using a database frontend; phpMyAdmin, bloated as it is, doesn’t support functions/routines, so you need another one, and I found Adminer to be a massively better alternative, performance and flexibility-wise, and it’s only a 150kb-something single PHP file, which makes it all the better.

So, when you test the function, you have the following:

SELECT matchkwdarray('this is just a very simple test', 'just,test', ',')
Returns: 2

SELECT matchkwdarray('this is just a very simple test', 'some,stuff', ',')
Returns: 0

SELECT matchkwdarray('this is just a very simple test', 'this,very,stuff', ',')
Returns: 0

The function is resource efficient: as soon as a keyword is not in the text, it stops processing the keywords array. Surely, if you don’t want a 100% match but are fine with lower than that, you can easily modify the function to “stream” the whole keywords array, even removing the last character off each keywords to allow some variations, and return a percentage match which you can then evaluate later on.

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

MySQL SELECT query with more WHERE conditions runs faster

For some reason I wanted to add more redundant conditions to a WHERE clause in a MySQL SELECT query, but I feared that, being redundant conditions, I may end up slowing up the query.

Shortly, there is this classifieds site, where each item’s table has the town_id, province_id and region_id rows. If you know the town_id you’re looking for already, you can query:

SELECT * FROM items WHERE town_id='12'

instead of:

SELECT * FROM items WHERE town_id='12' AND province_id='34' AND region_id='56'

as the results will be the same… but should you?

I prepared a quick test panel:

$creationstart=strtok(microtime()," ")+strtok(" ");
for($i=0;$i<=999;$i++)
 $test=doquery("SELECT * FROM items WHERE town_id='58003'");
$creationend=strtok(microtime()," ")+strtok(" ");
$creationtime=number_format($creationend-$creationstart,4);
echo "town only: $creationtime<br />";

$creationstart=strtok(microtime()," ")+strtok(" ");
for($i=0;$i<=999;$i++)
 $test=doquery("SELECT * FROM items WHERE town_id='58003' AND province_id='58' AND region_id='12'");
$creationend=strtok(microtime()," ")+strtok(" ");
$creationtime=number_format($creationend-$creationstart,4);
echo "all conditions: $creationtime<br />";

Surprisingly, it took anywhere from 2.4s to 4.5s for the “all conditions” routine to complete, while the “town only” query ended up being about 0.4s slower. At first I thought it might be the MySQL cache in action, but even inverting the position of the cycles inside the code, results were the same.

So well, even if it looks redundant to you, adding more WHERE ... AND ... clauses in your query speeds up the fetching of the results.

  This article has been Digiproved