Huh??? On Saturday, June 20, 2009, Peter Brawley <peter.braw...@earthlink.net> 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 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
