Slow Performance for some queries

Hi there,

I have discovered some very low performing queries on our MariaDB version 5.3.7. The query shows up with "string results in query cache"

The query:

explain select distinct
        `config`.`agent_id` AS `agent_id`,
        `config`.`agent_name` AS `agent_name`,
        `agent`.`agent_version` AS `agent_version`,
        `config`.`agent_os` AS `agent_os`,
        `agent`.`agent_os_version` AS `agent_os_version`,
        `config`.`pconfig_line` AS `pconfig_line`,
        `config`.`appclass` AS `appclass`,
        `config`.`instance` AS `instance`,
        `config`.`parameter` AS `parameter`,
        `config`.`value` AS `value`,
        substring_index(substring_index(`config`.`variable`, '/', '4ö'),
                '/',
                -(1)) AS `NT_Drive`,
        `config`.`agent_config_date` AS `agent_config_date`,
        `config`.`rule_type_name` AS `rule_type_name`
    from
        (`agent_config_detail_view` `config`
        left join `agent` ON ((`agent`.`agent_id` = `config`.`agent_id`)))
    where
        (((`config`.`rule_type_id` = 'ccbe914e30c6ec6e3aa5c4780a3525d6')
            or (`config`.`rule_type_id` = '0091efee7f9b5f0e27211eb7226d0a23')
            or (`config`.`rule_type_id` = '6311ae17c1ee52b36e68aaf4ad066387'))
            and ((`config`.`variable` = '/AgentSetup/FILESYSTEM.filterList')
            or (`config`.`variable` = '/AgentSetup/FILESYSTEM.filterType')
            or (`config`.`variable` like '/AS/EVENTSPRING/PARAM_SETTINGS/THRESHOLDS/FILESYSTEM%')
            or (`config`.`variable` like '/RecoveryActions/NT_LOGICAL_DISKS/%/LDldFreeSpacePercent/Mode'))
            and (`config`.`agent_config_date` > ((select 
                max(`s`.`agent_config_date`)
            from
                `agent_config` `s`) - interval 24 hour)))
    order by `config`.`agent_name` , `config`.`appclass`

Here is the explain for the query in question:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	a	ALL	PRIMARY	NULL	NULL	NULL	7083	"Using temporary; Using filesort"
1	PRIMARY	agent	eq_ref	PRIMARY	PRIMARY	34	new_budm.a.agent_id	1	
1	PRIMARY	b	ref	PRIMARY,fk_agent_config_agent1,IX_agent_config_date	fk_agent_config_agent1	34	new_budm.a.agent_id	3	"Using where"
1	PRIMARY	c	ref	agent_config_rule_fk_agent_config_id,agent_config_rule_fk_rule_id	agent_config_rule_fk_agent_config_id	34	new_budm.b.agent_config_id	947	
1	PRIMARY	d	eq_ref	PRIMARY,IX_rule_variable	PRIMARY	34	new_budm.c.fk_rule_id	1	"Using where"
1	PRIMARY	e	ref	fk_rule_rule_type_rule_type1,fk_rule_rule_type_rule	fk_rule_rule_type_rule	34	new_budm.c.fk_rule_id	1	"Using where"
1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	34	new_budm.e.fk_rule_type_id	1	
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	"Select tables optimized away"

The query cache (innodb_buffer_pool_size) is set to 8192M.

Other queries are running fine (perfomance can always be better).

Thank you and regards, Patrick

Answer Answered by Sergei Golubchik in this comment.

you didn't provide the definition of the view, so we cannot see the complete query. Show the view definition and the output of EXPLAIN EXTENDED ...; SHOW WARNINGS;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.