Using a parameter in a MySQL stored procedure WHERE-CLAUSE slows down performance

Question

I have a stored procedure declared as below:

CREATE DEFINER=`blabla`@`%` PROCEDURE `getAllDomainsByCountry`(IN dom_id INT)

BEGIN
SELECT 
domain.id,
IFNULL(domain.indexed, '-') AS indexed,
domain.name,
country.language_code,
IFNULL(ip_adress.adress, '-') AS adress,
IFNULL(GROUP_CONCAT(category.name
            SEPARATOR ', '),
        '-') AS categories,
IFNULL(GROUP_CONCAT(category.id
            SEPARATOR ', '),
        '-') AS categories_id,
(SELECT 
        IFNULL(GROUP_CONCAT(DISTINCT client.name
                        SEPARATOR ', '),
                    '-')
    FROM
        link
            LEFT JOIN
        client_site ON link.client_site = client_site.id
            LEFT JOIN
        client ON client.id = client_site.client
    WHERE
        link.from_domain = domain.id) AS clients,
IFNULL(domain_host.name, '-') AS domain_host_account,
IFNULL(content_host.name, '-') AS content_host,
status.id AS status,
status.name AS status_name
FROM
domain
    LEFT JOIN
ip_adress ON domain.ip = ip_adress.id
    LEFT JOIN
domain_category ON domain.id = domain_category.domain
    LEFT JOIN
category ON domain_category.category = category.id
    LEFT JOIN
country ON domain.country = country.id
    LEFT JOIN
domain_host_account ON domain.domain_host_account = domain_host_account.id
    LEFT JOIN
domain_host ON domain_host_account.host = domain_host.id
    LEFT JOIN
content_host ON domain.content_host = content_host.id
    LEFT JOIN
domain_status ON domain.id = domain_status.domain
    LEFT JOIN
status ON domain_status.status = status.id
WHERE
domain.country = dom_id
GROUP BY domain.id
ORDER BY domain.name;  
END

If I replace the usage of the parameter dom_id with a static integer, ie:

WHERE
  domain.country = 1

MySQL version: 5.5.41

EXPLAIN:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,domain,ref,idx_domain_country,idx_domain_country,5,const,1858,"Using where; Using temporary; Using filesort"
1,PRIMARY,ip_adress,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain.ip,1,
1,PRIMARY,domain_category,ref,FK_domain_category_domain_idx,FK_domain_category_domain_idx,5,dominfo.domain.id,1,
1,PRIMARY,category,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain_category.category,1,
1,PRIMARY,country,const,PRIMARY,PRIMARY,4,const,1,
1,PRIMARY,domain_host_account,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain.domain_host_account,1,
1,PRIMARY,domain_host,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain_host_account.host,1,
1,PRIMARY,content_host,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain.content_host,1,
1,PRIMARY,domain_status,ALL,NULL,NULL,NULL,NULL,1544,
1,PRIMARY,status,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain_status.status,1,
2,"DEPENDENT SUBQUERY",link,ALL,NULL,NULL,NULL,NULL,8703,"Using where"
2,"DEPENDENT SUBQUERY",client_site,eq_ref,PRIMARY,PRIMARY,4,dominfo.link.client_site,1,
2,"DEPENDENT SUBQUERY",client,eq_ref,PRIMARY,PRIMARY,4,dominfo.client_site.client,1,"Using where"

SHOW CREATE TABLE domain:

CREATE TABLE `domain` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(67) DEFAULT NULL,
`domain_host_account` int(11) DEFAULT NULL,
`content_host` int(11) DEFAULT NULL,
`ip` varchar(45) DEFAULT NULL,
`historic_content` tinytext,
`redirected` int(11) DEFAULT NULL,
`ftp_account` tinyint(1) DEFAULT ''0'',
`comment` tinytext,
`country` int(11) DEFAULT NULL,
`redirected_text` varchar(45) DEFAULT NULL,
`status_text` varchar(500) DEFAULT NULL,
`dhost_text` varchar(500) DEFAULT NULL,
`chost_text` varchar(500) DEFAULT NULL,
`category_text` varchar(150) DEFAULT NULL,
`dhost_acc_text` varchar(45) DEFAULT NULL,
`indexed` tinyint(1) DEFAULT NULL,
`indexed_checked` date DEFAULT NULL,
`origin` tinyint(1) DEFAULT ''0'',
PRIMARY KEY (`id`),
KEY `FK_domain_host_account_idx` (`domain_host_account`),
KEY `idx_domain_ip` (`ip`),
KEY `idx_domain_country` (`country`),
KEY `idx_domain_domain_host_account` (`domain_host_account`),
KEY `idx_domain_content_host` (`content_host`)
) ENGINE=InnoDB AUTO_INCREMENT=12598 DEFAULT CHARSET=latin1

The procedure will take 0.06s to execute whereas using the parameter "dom_id", passing integer value of 1, it will result in an execution time of 5.070s. Any ideas?

Answer 1

As per the question, @sboss wants to know the behaviour of :

The procedure will take 0.06s to execute with static int whereas using the parameter 
"dom_id", passing integer value of 1, it will result in an execution 
time of 5.070s.

This behaviour can be easily understood, if we see how mysql engine works.

MySQL engine caches query and result. The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

Hence, it is most likely in your case that execution time of 5.070s is found when query actually parsed and executed by mysql engine and execution time of 0.06s is found when result-set was retrieved from query cache.

Kindly refer documentation for detail : https://dev.mysql.com/doc/refman/5.5/en/query-cache.html

Answer 2

Optimization 1

Part of the slowdown is the repeatedly executed "dependent subquery":

      ( SELECT  IFNULL(GROUP_CONCAT(DISTINCT client.name SEPARATOR ', '), '-')
            FROM  link
            LEFT JOIN  client_site ON link.client_site = client_site.id
            LEFT JOIN  client ON client.id = client_site.client
            WHERE  link.from_domain = domain.id
      ) AS clients

According to the EXPLAIN, it must scan all ~8703 rows of link each time.

I don't think it can be simplified within the same query. Instead I think this will be useful:

CREATE TEMPORARY TABLE t_clients (
    PRIMARY KEY(from_domain)
)
    SELECT  link.from_domain,
            IFNULL(GROUP_CONCAT(DISTINCT client.name SEPARATOR ', '), '-')
        FROM  link
        LEFT JOIN  client_site ON link.client_site = client_site.id
        LEFT JOIN  client ON client.id = client_site.client;

Then

SELECT  domain.id, IFNULL(domain.indexed, '-') AS indexed, domain.name,
        country.language_code, IFNULL(ip_adress.adress, '-') AS adress,
        IFNULL(GROUP_CONCAT(category.name SEPARATOR ', '), '-') AS categories,
        IFNULL(GROUP_CONCAT(category.id SEPARATOR ', '), '-') AS categories_id,
        t_clients.clients AS clients,                          -- Changed
        IFNULL(domain_host.name, '-') AS domain_host_account,
        IFNULL(content_host.name, '-') AS content_host,
        status.id AS status, status.name AS status_name
    FROM  domain
    LEFT JOIN t_clients  ON t_clients.from_domain = domain.id  -- Added
    LEFT JOIN  ip_adress ON domain.ip = ip_adress.id
    LEFT JOIN  domain_category ON domain.id = domain_category.domain
    LEFT JOIN  category ON domain_category.category = category.id
    LEFT JOIN  country ON domain.country = country.id
    LEFT JOIN  domain_host_account ON domain.domain_host_account = domain_host_account.id
    LEFT JOIN  domain_host ON domain_host_account.host = domain_host.id
    LEFT JOIN  content_host ON domain.content_host = content_host.id
    LEFT JOIN  domain_status ON domain.id = domain_status.domain
    LEFT JOIN  status ON domain_status.status = status.id
    WHERE  domain.country = dom_id
    GROUP BY  domain.id
    ORDER BY  domain.name; 

You can experiment as to whether the PREPARE approach is faster. In one (simpler) test I did, it did not seem to matter.

Optimization 2

Another potential speedup is to do the GROUP_CONCATs in subqueries instead of collecting lots of rows, then collapsing. Note that you had to use GROUP BY. This technique may be able to eliminate that. For example:

IFNULL(GROUP_CONCAT(category.name SEPARATOR ', '), '-') AS categories,
LEFT JOIN category ON ...

-->

IFNULL(
    ( SELECT GROUP_CONCAT(category.name SEPARATOR ', ')
          FROM category
          WHERE category.id = domain_category.category
    ),
'-') AS categories,

The reason why it might be can be observed if you do this with both your variant and with my variant:

SELECT COUNT(*) FROM ( the select, but without the GROUP BY or ORDER BY );

Your variant will (assuming lots of categories, etc) will have a much larger COUNT. That implies that your query is building a much bigger tmp table to feed to GROUP BY and ORDER BY. Hence slower.

Optimization 3

If you manage to get rid of all the aggregates (GROUP_CONCAT), then adding INDEX(country, name) should further optimize it by getting rid of the two FILESORTs.

Answer 3

If you go dynamic go all the way,I used testing not to overwrite your original proc,also make sure you dont have a testing proc already there

DROP PROCEDURE IF EXISTS `testing`;
DELIMITER //
CREATE PROCEDURE `testing`(IN `test` INT)
 BEGIN
SET @id=test;
SET @query="SELECT 
domain.id,
IFNULL(domain.indexed, '-') AS indexed,
domain.name,
country.language_code,
IFNULL(ip_adress.adress, '-') AS adress,
IFNULL(GROUP_CONCAT(category.name
            SEPARATOR ', '),
        '-') AS categories,
IFNULL(GROUP_CONCAT(category.id
            SEPARATOR ', '),
        '-') AS categories_id,
(SELECT 
        IFNULL(GROUP_CONCAT(DISTINCT client.name
                        SEPARATOR ', '),
                    '-')
    FROM
        link
            LEFT JOIN
        client_site ON link.client_site = client_site.id
            LEFT JOIN
        client ON client.id = client_site.client
    WHERE
        link.from_domain = domain.id) AS clients,
IFNULL(domain_host.name, '-') AS domain_host_account,
IFNULL(content_host.name, '-') AS content_host,
status.id AS status,
status.name AS status_name
FROM
domain
    LEFT JOIN
ip_adress ON domain.ip = ip_adress.id
    LEFT JOIN
domain_category ON domain.id = domain_category.domain
    LEFT JOIN
category ON domain_category.category = category.id
    LEFT JOIN
country ON domain.country = country.id
    LEFT JOIN
domain_host_account ON domain.domain_host_account = domain_host_account.id
    LEFT JOIN
domain_host ON domain_host_account.host = domain_host.id
    LEFT JOIN
content_host ON domain.content_host = content_host.id
    LEFT JOIN
domain_status ON domain.id = domain_status.domain
    LEFT JOIN
status ON domain_status.status = status.id
WHERE
domain.country = ?
GROUP BY domain.id
ORDER BY domain.name;"  

PREPARE sqlquery FROM @query;
EXECUTE sqlquery USING @id;
END;
//
DELIMITER ;

Then use

CALL `testing`(1);