sql join on null value …

September 2, 2008

I had to join tables x and y based on three attributes a, b and c. The table y was made to link a specific id with each triplet (a,b,c). Some of the definition of the id’s had a c parameter that was NULL… Thus, the join did not work.

The dirty workaround was the following : a,b and c where only positive integer (except the NULL values). We updated the y table setting NULL to -1. Then in the join query we used the ifnull() mysql function :

SELECT x.id, y.id, x.a, x.b, x.c FROM x
RIGHT OUTER JOIN y ON x.a = y.a
AND x.b = y.b
AND ifnull(x.c, -1) = y.c


MySQL query optimizing

July 11, 2008

A not so complex query was taking a huge time to complete (hours …).

I searched for an “EXPLAIN ANALYSE” a la PostgreSQL on MySQL … this does not exists.

BUT

They have a less featured command called “EXPLAIN EXTENDED”[1][2] that outputs some interesting information.

I found that adding two indexes helped me to have the query processed in some seconds … 😉

[1] The EXTENDED argument did not change the ouput I had …

[2] Using explain from mysql website