Query is crashing phpmyadmin/mysql

[dipperdan]

Hi, i'm trying to execute the following query:

SELECT CONCAT_WS(' ', g.genus_name, s.species_name, i.rank, i.infraspecies_name, conf.auth1, conf.auth2 ) AS scientific_name, 
cn.common_name, GROUP_CONCAT(cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, gd.gen_distrib_name 
FROM plants AS p 
LEFT JOIN (genera AS g, species AS s) 
ON (p.genus_num = g.genus_id AND p.species_num = s.species_id)
LEFT JOIN (confirmations AS conf) 
ON (p.plant_id = conf.plant_num)
LEFT JOIN (plant_growth_habits AS pgh, growth_habits AS gh)
ON (p.plant_id = pgh.plant_num)
LEFT JOIN (plant_distributions AS pd, gen_distribs AS gd)
ON (p.plant_id = pd.plant_num)
LEFT JOIN (plant_common_names AS pcn, common_names AS cn) 
ON (p.plant_id = pcn.plant_num)
LEFT JOIN (unvouchered_images AS ui) 
ON (p.plant_id = ui.plant_num)
LEFT JOIN (infraspecies AS i) 
ON (p.infraspecies_num = i.infraspecies_id)
WHERE scientific_name LIKE '%sesuvium%'
GROUP BY scientific_name ORDER BY scientific_name
 

It used to be a bit less and after i added the last 3 or so joins is when the crashing began. Is something wrong with the way i am joining?

[pbmods]

Heya, Dipperdan.

How is it crashing? Are you getting a white screen? 500 error? Is the request timing out?

Try EXPLAINing the query to see if you're dealing with a particularly slow query.

[dipperdan]

Heya, Dipperdan.

How is it crashing? Are you getting a white screen? 500 error? Is the request timing out?

Try EXPLAINing the query to see if you're dealing with a particularly slow query.

It was timing out, like reaaaly long, then i had to restart mysql. But i got it solved tho i reworked the query a bit:

SELECT
CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,
conf.auth1,conf.auth2) AS scientific_name, cn.common_name,
GROUP_CONCAT(DISTINCT cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, 
ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, GROUP_CONCAT(DISTINCT gh.growth_habit ORDER BY gh.growth_habit SEPARATOR ', ') AS growth_habits,
gd.gen_distrib_name, GROUP_CONCAT(DISTINCT gd.gen_distrib_name ORDER BY gd.gen_distrib_name SEPARATOR ', ') AS general_dists
FROM plants AS p 
 LEFT JOIN genera AS g 
    ON g.genus_id = p.genus_num
 LEFT JOIN species AS s 
    ON s.species_id = p.species_num
 LEFT JOIN confirmations AS conf 
    ON conf.plant_num = p.plant_id
 LEFT JOIN (plant_growth_habits AS pgh INNER JOIN growth_habits AS gh ON gh.growth_habit_id = pgh.growth_habit_num) 
    ON pgh.plant_num = p.plant_id
 LEFT JOIN (plant_distributions AS pd INNER JOIN gen_distribs AS gd ON gd.gen_distrib_id = pd.gen_distrib_num) 
    ON pd.plant_num = p.plant_id
 LEFT JOIN (plant_common_names AS pcn INNER JOIN common_names AS cn ON cn.common_name_id = pcn.common_name_num) 
    ON pcn.plant_num = p.plant_id 
 LEFT JOIN unvouchered_images AS ui
    ON p.plant_id = ui.plant_num
 LEFT JOIN infraspecies AS i 
    ON p.infraspecies_num = i.infraspecies_id
WHERE scientific_name LIKE '%sesuvium%' GROUP BY scientific_name ORDER BY scientific_name
 

The above sql works (processes in ~ 0.2 seconds now), but i have another little problem with the output from

CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,conf.auth1,conf.auth2) AS scientific_name
 

where there will not always be a value for conf.auth1 and/or conf.auth2 and the concatenation sometimes prints say for e.g. there was no value for conf.auth2 "Sesuvium microphyllum Willd. NULL". Is this the default behaviour of concat_ws, can i get around this or would i just have to strip that out using php?

Thanks...