Оптимизация запросов

[Mosik]

Оптимизация запросов

Задача такова:
Есть таблица с валютами. Есть таблица с курсами валют. Нужно получить Список валют с их текущими курсами.
Я написал два запроса, которые решают эту проблему. Но проанализировав результат explain по каждому я остался недоволен результатами. А как оптимизировать не знаю.

Вот структура и содержимое таблиц:
CREATE TABLE `pas_currencies` (
`id` tinyint(2) NOT NULL auto_increment,
`s_name` varchar(5) NOT NULL default '',
`name` varchar(25) NOT NULL default '',
`symbol_left` varchar(12) default NULL,
`symbol_right` varchar(12) default NULL,
`active` tinyint(1) NOT NULL default '0',
`date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`last_modified` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `idx_active` (`active`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=6;

CREATE TABLE `pas_currencies_rates` (
`id` smallint(5) NOT NULL auto_increment,
`currencies_id` tinyint(2) NOT NULL default '0',
`rdate` date NOT NULL default '0000-00-00',
`rate` float NOT NULL default '0',
`frequency` float NOT NULL default '0',
`date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`last_modified` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `idx_currency` (`currencies_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=13;


Вот SQL-запросы:
1-й вариант:
SELECT cur.s_name, cur.active, rates.rate, rates.frequency
FROM pas_currencies as cur
LEFT JOIN (
SELECT currencies_id, MAX(rdate) max_rdate
FROM pas_currencies_rates
WHERE rdate <= '2005-08-15'
GROUP BY currencies_id
) rates1
ON cur.id=rates1.currencies_id
LEFT JOIN pas_currencies_rates rates
ON rates.currencies_id=cur.id AND rates.rdate=rates1.max_rdate
WHERE cur.active=1

2-й вариант:
SELECT cur.s_name, cur.active, rates.rate, rates.frequency
FROM pas_currencies as cur
LEFT JOIN (
SELECT *
from pas_currencies_rates
where rdate <= '2005-08-15'
order by rdate desc
) as rates
ON rates.currencies_id=cur.id
WHERE cur.active=1
GROUP BY cur.id


В моей БД встречаются аналогичные варианты но с более крупными таблицами и там начинаются тормоза.
Если кто может, помогите с оптимизацией.

 

[Фанат]

ну и где результат explain?

 

[Mosik]

Фанат
1-й запрос
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","cur","ref","idx_active","idx_active",1,"const",2,"Using where"
1,"PRIMARY","<derived2>","ALL","","",,"",4,""
1,"PRIMARY","rates","ref","idx_currency","idx_currency",1,"webconsultant.cur.id",2,""
2,"DERIVED","pas_currencies_rates","ALL","","",,"",8,"Using where; Using temporary; Using filesort"

2-й запрос
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","cur","ref","idx_active","idx_active",1,"const",2,"Using where; Using temporary; Using filesort"
1,"PRIMARY","<derived2>","ALL","","",,"",6,""
2,"DERIVED","pas_currencies_rates","ALL","","",,"",8,"Using filesort"

-~{}~ 27.08.05 13:18:

Меня пугает type=ALL
Как его избежать?

-~{}~ 27.08.05 13:54:

Фанат
Можешь чего-то сказать по результатам этих explain?

 

[Фанат]

скажи, а какая программа выдяёт такой странный формат?если честно, довольно трудно понять, что тут написано.

 

[Mosik]

Сейчас попробую в более читаемом формате выложить

 

[Фанат]

слушай,
я правильно понимаю, что тебе всего-то надо получить последние значения курсов + названия валют?

 

[Mosik]

Да последние, которые не больше какой-то даты.

-~{}~ 27.08.05 14:21:

Фанат

скажи, а какая программа выдяёт такой странный формат?если честно, довольно трудно понять, что тут написано.

id select_type table                type possible_keys key          key_len ref    rows Extra   
1  PRIMARY     cur                  ref  idx_active    idx_active   1       const  2    Using where   
1  PRIMARY     <derived2>           ALL                                            4    (null)   
1  PRIMARY     rates                ref  idx_currency  idx_currency 1       cur.id 2    (null)   
2  DERIVED     pas_currencies_rates ALL                                            8    Using where; Using temporary; Using filesort

id select_type table                type possible_keys key         key_len ref   rows Extra 
1  PRIMARY     cur                  ref  idx_active    idx_active  1       const 2    Using where; Using temporary; Using filesort 
1  PRIMARY     <derived2>           ALL                                          6   
2  DERIVED     pas_currencies_rates ALL                                          8    Using filesort

-~{}~ 27.08.05 17:36:

Только что написал еще один запрос:
SELECT cur.s_name, cur.active, rates.rate, rates.frequency
FROM pas_currencies as cur
LEFT JOIN pas_currencies_rates rates
ON rates.currencies_id=cur.id
WHERE cur.active=1 AND rates.rdate=(
SELECT MAX(rdate)
FROM pas_currencies_rates
WHERE rdate <= '2005-08-15' AND currencies_id=cur.id
)

Результат работы explain меня полностью удовлетворил:

id	select_type		table			type	possible_keys		key		key_len	ref			rows	Extra	
1	PRIMARY			cur			ref	PRIMARY,idx_active	idx_active	1	const			2	Using where
1	PRIMARY			rates			ref	idx_currency		idx_currency	1	webconsultant.cur.id	2	Using where
2	DEPENDENT SUBQUERY	pas_currencies_rates	ref	idx_currency		idx_currency	1	webconsultant.cur.id	2	Using where

-~{}~ 27.08.05 17:41:

А еще можно вот так:
SELECT cur.s_name, cur.active, rates.rate, rates.frequency
FROM pas_currencies as cur
LEFT JOIN pas_currencies_rates rates
ON rates.currencies_id=cur.id AND rates.rdate=(
SELECT MAX(rdate)
FROM pas_currencies_rates
WHERE rdate <= '2005-08-25' AND currencies_id=cur.id
)
WHERE cur.active=1

Результат работы explain почти тот-же


Что-то меня поперло

 

[Falc]

Mosik
Про первый запрос.
У тебя идет не быстрый сабселект, который потом лефт джойнишь, следовательно джоин делает фулскан по временой таблице и в добавак еще один лефт джоин не по полному индексу. Вобщем тормоза конкретные.

Сделай индекс для второго лефт джойна, и если возможно сделай так чтобы сабселект шел первым в выборке.

-~{}~ 02.09.05 10:40:

Или же сделай сабселект отдельным запросом во временую таблицу с индексом.