Фильр по формуле

[TT.]

Надо оптимизировать запрос, для этого как минимум надо не проводить повторные выборки по уже найденым значениям, вопрос - как это сделать?


Идея: (нерабочие варианты)
select val1,(select ...) as val2,(select ...) as val3 from table order by (val2/val3)
или
select val1,(select ...) as val2,(select ...) as val3 from table order by (val2/val3)



Не оптимальная реализация: (Рабочая)
select val1,(select ...) as val2,(select ...) as val3,(select ...)/(select ...) as val4 from table order by val4


Может кто нибудь сталкивался с оптимизацией такого запроса?


Кому интересно вот реальный запрос:
Время выполнения без этих дополнительных пересчетов в 2 раза ниже, что очень даже существено, поскольку там база огромная...

SELECT
traffic.`key` as keyid,
(select `key` from `keys` where `keys`.`idkey`=keyid),
count(*) AS num_traffic,
(select count(*) from stat where stat.`keyword`=keyid and
UNIX_TIMESTAMP(stat.`leaddatetime`)>=UNIX_TIMESTAMP('2007-10-19') and stat.`affid`='1002' and
UNIX_TIMESTAMP(stat.`leaddatetime`)<=UNIX_TIMESTAMP('2007-10-29 23:59:59')) as num_lead,
(select sum(stat.`money`) from stat where stat.`keyword`=keyid and stat.`affid`='1091' and
UNIX_TIMESTAMP(stat.`leaddatetime`)>=UNIX_TIMESTAMP('2007-10-19') and
UNIX_TIMESTAMP(stat.`leaddatetime`)<=UNIX_TIMESTAMP('2007-10-29 23:59:59')) as amt_leads,
((select sum(stat.`money`) from stat where stat.`keyword`=keyid and stat.`affid`='1002' and
UNIX_TIMESTAMP(stat.`leaddatetime`)>=UNIX_TIMESTAMP('2007-10-19') and
UNIX_TIMESTAMP(stat.`leaddatetime`)<=UNIX_TIMESTAMP('2007-10-29 23:59:59'))
/count(*)) as priceclick,
((select sum(stat.`money`) from stat where stat.`keyword`=keyid and stat.`affid`='1002' and
UNIX_TIMESTAMP(stat.`leaddatetime`)>=UNIX_TIMESTAMP('2007-10-19') and
UNIX_TIMESTAMP(stat.`leaddatetime`)<=UNIX_TIMESTAMP('2007-10-29 23:59:59'))
/(select count(*) from stat where stat.`keyword`=keyid and
UNIX_TIMESTAMP(stat.`leaddatetime`)>=UNIX_TIMESTAMP('2007-10-19') and stat.`affid`='1002' and
UNIX_TIMESTAMP(stat.`leaddatetime`)<=UNIX_TIMESTAMP('2007-10-29 23:59:59'))) as pricelead,
(count(*)/(select count(*) from stat where stat.`keyword`=keyid and
UNIX_TIMESTAMP(stat.`leaddatetime`)>=UNIX_TIMESTAMP('2007-10-19') and stat.`affid`='1002' and
UNIX_TIMESTAMP(stat.`leaddatetime`)<=UNIX_TIMESTAMP('2007-10-29 23:59:59'))) as pricelead
FROM
traffic
WHERE
traffic.affid = '1002' and
traffic.`key`!=0 and
UNIX_TIMESTAMP(traffic.`dateg`)>=UNIX_TIMESTAMP('2007-10-19') and
UNIX_TIMESTAMP(traffic.`dateg`)<=UNIX_TIMESTAMP('2007-10-29 23:59:59')
GROUP BY
traffic.`key`
order by
pricelead desc

 

[lapo4ka]

А если пользоваться временными таблицами? и апдейтить их по идешникам?