Why this query is so slow?

[sales2010]

Hello,

Can somebody help me to understand why this query run in 18 seconds? It's a simple join between few lists. It might be because of where condition from query?

Showing rows 0 - 11 ( 12 total, Query took 18.2335 sec) [text: [BLOB - 9 B] - [BLOB - 26 B]]

SELECT DISTINCT(`aplicatii_pipeline_campanii`.`stage`) AS `text`, `aplicatii_pipeline_campanii`.`stage` AS `value` FROM `aplicatii_pipeline_campanii`LEFT JOIN `aplicatii_pipeline_companii` AS `aplicatii_pipeline_companii` ON `aplicatii_pipeline_companii`.`id_campanie` = `aplicatii_pipeline_campanii`.`id` LEFT JOIN `jos_users` AS `jos_users` ON `jos_users`.`id` = `aplicatii_pipeline_companii`.`NAMEAGENTCONT` LEFT JOIN `jos_users` AS `jos_users_0` ON `jos_users_0`.`id` = `aplicatii_pipeline_companii`.`MAJ` LEFT JOIN `aplicatii_pipeline_categorii` AS `aplicatii_pipeline_categorii` ON `aplicatii_pipeline_categorii`.`id_pipeline_cat` = `aplicatii_pipeline_campanii`.`id` LEFT JOIN `aplicatii_pipeline_campanii` AS `aplicatii_pipeline_campanii_0` ON `aplicatii_pipeline_campanii_0`.`id` = `aplicatii_pipeline_categorii`.`id_pipeline_cat` LEFT JOIN `aplicatii_pipeline_info` AS `aplicatii_pipeline_info` ON `aplicatii_pipeline_info`.`id_pipeline_info` = `aplicatii_pipeline_campanii`.`id` LEFT JOIN `#__users` AS `jos_users_1` ON `jos_users_1`.`id` = `aplicatii_pipeline_info`.`actualizare` LEFT JOIN `aplicatii_pipeline_campanii` AS `aplicatii_pipeline_campanii_1` ON `aplicatii_pipeline_campanii_1`.`id` = `aplicatii_pipeline_info`.`id_pipeline_info` LEFT JOIN `aplicatii_pipeline_campanii` AS `aplicatii_pipeline_campanii_2` ON `aplicatii_pipeline_campanii_2`.`id` = `aplicatii_pipeline_companii`.`id_campanie` WHERE `aplicatii_pipeline_campanii`.`stage` IN ('Offering','Closed-not interested','Closed-Not proper to call','Call back','New opportunity','Closed-Healthcheck only','Closed-Lost','Closed-Won','Urgent issue-send to sales','New lead','Negotiating','Incorrect database ') ORDER BY text ASC

Using explain i get this info (see attached image):


thank you

 

[sales2010]

friendly bump..

 

[cheesegrits]

At a guess, it looks like there is at least one field involved which doesn't have an index, and it's probably the `aplicatii_pipeline_campanii`.`stage` element. You seem to be using a text field as the 'value' for the join, and depending what underlying field type that is in your MySQL table, it may not be indexable.

I always recommend using a standard numeric primary key as the "value" in a join element, typically the 'id' field, if it's a table that Fabrik created.

Also, you are joining the companii table to itself four times, which could be producing a logarithmic increase in the number of rows being selected, before the DISTINCT is applied.

I would suggest running this query by hand in phpMyAdmin (you'll have to replace any #__ with jos_), but change DISTINCT(`aplicatii_pipeline_campanii`.`stage`) to just `aplicatii_pipeline_campanii`.`stage`, and see how many rows it returns.

That's the first step, to see if that query is simply producing some unrealistically large number of rows, before a DISTINCT() boils it down to just one instance of each row.

Then I'd suggest changing the join spec on the element to use 'id' as the 'value', which will mean changing the WHERE clause to use the id values rather than the label text. And try running that version of the query by hand as well.

-- hugh