I have a mysql server. Here is the information about the server:
# Variable_name, Value
innodb_version, 5.6.19
protocol_version, 10
slave_type_conversions,
version, 5.6.19-log
version_comment, MySQL Community Server (GPL)
version_compile_machine, x86_64
version_compile_os, Linux
So far, I have this table:
CREATE TABLE `currency_rate_hist` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`currency_rate_id` bigint(20) NOT NULL,
`date_from` datetime NOT NULL,
`date_to` datetime DEFAULT NULL,
`rate` decimal(10,4) unsigned NOT NULL,
`date` datetime NOT NULL,
`user` varchar(100) NOT NULL,
`spread` decimal(12,10) NOT NULL DEFAULT '1.0000000000',
`commercial_rate` decimal(12,10) NOT NULL,
`tourism_rate` decimal(12,10) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_currency_rate_hist_fromto` (`date_from`,`date_to`),
KEY `fk_currency_rate_hist_cr` (`currency_rate_id`),
CONSTRAINT `fk_currency_rate_hist_cr` FOREIGN KEY (`currency_rate_id`) REFERENCES `currency_rate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002152 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
Aaaaand this sql:
select
dates.min_date, first.rate as first_rate, dates.max_date, last.rate as last_rate
from
(
select
crh.currency_rate_id,
date(crh.date_from) as date,
min(crh.date_from) as min_date,
max(crh.date_from) as max_date
from currency_rate_hist crh
where
crh.currency_rate_id = 2 and
crh.date_from between now() - interval 31 day and date_format(now() - interval 1 day, '%Y-%m-%d 23:59:59')
group by date(crh.date_from) order by crh.date_from asc
) as dates
inner join (select rate, currency_rate_id, date_from from currency_rate_hist) as first on
first.currency_rate_id = dates.currency_rate_id and first.date_from = dates.min_date
inner join (select rate, currency_rate_id, date_from from currency_rate_hist) as last on
last.currency_rate_id = dates.currency_rate_id and last.date_from = dates.max_date
order by dates.max_date desc;
and here is the explain of the query:
{
"query_block": {
"select_id": 1,
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "first",
"access_type": "ALL",
"rows": 739825,
"filtered": 100,
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"table": {
"table_name": "currency_rate_hist",
"access_type": "ALL",
"rows": 739825,
"filtered": 100
}
}
}
}
},
{
"table": {
"table_name": "last",
"access_type": "ref",
"possible_keys": [
"<auto_key2>"
],
"key": "<auto_key2>",
"used_key_parts": [
"currency_rate_id"
],
"key_length": "8",
"ref": [
"first.currency_rate_id"
],
"rows": 13,
"filtered": 100,
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 4,
"table": {
"table_name": "currency_rate_hist",
"access_type": "ALL",
"rows": 739825,
"filtered": 100
}
}
}
}
},
{
"table": {
"table_name": "dates",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"currency_rate_id",
"min_date",
"max_date"
],
"key_length": "20",
"ref": [
"first.currency_rate_id",
"first.date_from",
"last.date_from"
],
"rows": 10,
"filtered": 100,
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"ordering_operation": {
"using_filesort": true,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"table": {
"table_name": "crh",
"access_type": "range",
"possible_keys": [
"idx_currency_rate_hist_fromto",
"fk_currency_rate_hist_cr"
],
"key": "idx_currency_rate_hist_fromto",
"used_key_parts": [
"date_from"
],
"key_length": "5",
"rows": 4505,
"filtered": 75.006,
"index_condition": "(`ebanxpayproduction`.`crh`.`date_from` between '2016-02-18 00:00:00' and '2016-02-18 23:59:59')",
"attached_condition": "(`ebanxpayproduction`.`crh`.`currency_rate_id` = 2)"
}
}
}
}
}
}
}
]
}
}
This query is unpractical in my production server. I could let it run for ten minutes without getting any response. Any ideias? Thanks in advance.
Remove the subqueries and the order by
in the subqueries. These impede the use of indexes in MySQL. So, start with:
select dates.min_date, first.rate as first_rate, dates.max_date, last.rate as last_rate
from (select crh.currency_rate_id, date(crh.date_from) as date,
min(crh.date_from) as min_date, max(crh.date_from) as max_date
from currency_rate_hist crh
where crh.currency_rate_id = 2 and
crh.date_from between now() - interval 31 day and date_format(now() - interval 1 day, '%Y-%m-%d 23:59:59')
group by date(crh.date_from)
) dates inner join
currency_rate_hist first
on first.currency_rate_id = dates.currency_rate_id and
first.date_from = dates.min_date inner join
currency_rate_hist last
on last.currency_rate_id = dates.currency_rate_id and
last.date_from = dates.max_date
order by dates.max_date desc;
For this version, you want indexes on currency_rate_hist(currency_rate_id, date_from)
.
This may give you sufficient performance without a major rewrite.