List:General Discussion« Previous MessageNext Message »
From:Mike Spreitzer Date:June 20 2009 6:31am
Subject:how to efficiently query for the next in MySQL Community Edition 5.1.34?
View as plain text  
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

Thread
how to efficiently query for the next in MySQL Community Edition 5.1.34?Mike Spreitzer20 Jun
  • Re: how to efficiently query for the next in MySQL Community Edition5.1.34?Peter Brawley20 Jun
    • Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?Johnny Withers20 Jun
    • Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?Mike Spreitzer20 Jun
      • Re: how to efficiently query for the next in MySQL Community Edition5.1.34?Peter Brawley20 Jun
        • Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?Mike Spreitzer20 Jun
          • Re: how to efficiently query for the next in MySQL Community Edition5.1.34?Peter Brawley20 Jun
            • Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?Mike Spreitzer21 Jun
              • Re: how to efficiently query for the next in MySQL Community Edition5.1.34?Peter Brawley21 Jun
                • Re: how to efficiently query for the next in MySQL Community Edition 5.1.34?Mike Spreitzer21 Jun
        • Anyone using LVM for backing up?Timothy Little22 Jun
          • Re: Anyone using LVM for backing up?Thomas A. McGonagle22 Jun
          • Re: Anyone using LVM for backing up?Jim Lyons23 Jun
          • Re: Anyone using LVM for backing up?David Sparks23 Jun
          • Re: Anyone using LVM for backing up?Baron Schwartz4 Jul