Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?

[Johnny Withers]

Huh???

On Saturday, June 20, 2009, Peter Brawley <peter.brawley@stripped> wrote:
> Mike
>
>>J holding the next integer that T has for S
>
> You mean for each i, the next value of i with that s?
>
>>(U having no row for the last integer of each string).
>
> I do not understand that at all.
>
> PB
>
>
> Mike Spreitzer wrote:
>
> Suppose I have a table T with two column, S holding strings (say, VARCHAR(200)) and I
> holding integers.  No row appears twice.  A given string appears many times, on
> average about 100 times.  Suppose I have millions of rows.  I want to make a
> table U holding those same columns plus one more, J holding the next integer that T has
> for S (U having no row for the last integer of each string).  I could index T on
> (S,I) and write this query as
>
> select t1.*, t2.I as J from T as t1, T as t2
> where t1.S=t2.S and t1.I < t2.I
> and not exists (select * from T as t12 where t12.S=t1.S and t1.I < t12.I and t12.I
> < t2.I)
>
> but the query planner says this is quite expensive to run: it will enumerate all of T
> as t1, do a nested enumeration of all t2's entries for S=t1.S, and inside that do a
> further nested enumeration of t12's entries for S=t1.S --- costing about 10,000 times the
> size of T.  There has to be a better way!
>
> Thanks,
> Mike Spreitzer
>
>   ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com <http://www.avg.com> Version: 8.5.364 / Virus
> Database: 270.12.80/2187 - Release Date: 06/19/09 06:53:00
>
>
>
>

-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped