The following question has been posted to the Knowledgebase: https://kb.askmonty.org/en/slow-performance-for-some-queries/ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com