Re: execute same query only one time?

On 2/8/16, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>
>> On 08 Feb 2016, at 20:05, Johannes <jotpe(at)posteo(dot)de> wrote:
>>
>> select id, col1, col2, ... from t0 where id = (select max(id) from t0
>> where col1 = value1 and col2 = value2 and …);
>>
>> select col1 from t1 where t0_id = (select max(id) from t0 where col1 =
>> value1 and col2 = value2 and …);
>
> select t0.id, t0.col1, t0.col2, t0…., t1.col1
> from t0
> join t1 on (t1.t0_id = t0.id)
> group by t0.id, t0.col1, t0.col2, t0…., t1.col1
> having t0.id = max(t0.id);
>
> Low complexity and works with any number of rows from t0 (as does Adrian's
> solution, btw).

I think it fully ruins speed at all. Try to create tables, insert at
least 100000 rows into each of them (note that cardinality between
them is 1:m) and see EXPLAIN of your query. You are joining two big
tables, sort and group a resulting table and remove most rows to fit
into one statement...

> I'm not sure what you mean by "copying of columns" in your reply to Adrian's
> solution, but I don't think that happens here.

In the original letter the first query returns one row: "(id, col1,
col2)", and the second one returns rows "(val1), (val2), (val3), ..."
(values of the t1.col1).

If you use joining, you get rows:

(id, col1, col2, val1)
(id, col1, col2, val2)
(id, col1, col2, val3)
...

where values of the first three columns are the same.

>
> Alban Hertroys

--
Best regards,
Vitaly Burovoy