Ok, then you can take the A.numbers which have type null in ascendant order. And just increase your counter when you have a A.Number without value in B range.
Ludo On Fri, Jun 13, 2008 at 12:06 PM, Arun Singhal <[EMAIL PROTECTED]> wrote: > Hi Ludovic, > > Thanks for the reply. > But the problem is how would I terminate the loop as in my case there can be > some Number in A whose range does not lie between low and high in table B. > > > Thanks, > > Arun Singhal > Software Engineer > MPS Technologies (A Macmillan company) > > Email: [EMAIL PROTECTED] > Web: www.mpstechnologies.com > > Mail: MPS Technologies, 3A, 4th & 5th Floors, DLF Corporate Park, Gurgaon > 122 002, Haryana, India > > > -----Original Message----- > From: Ludovic Levesque [mailto:[EMAIL PROTECTED] > Sent: Friday, June 13, 2008 3:21 PM > To: Arun Singhal > Cc: Dustin Sallings; memcached@lists.danga.com; Brian Aker > Subject: Re: memcache as database > > Ok, > > so basically it's like a geo_ip database. > The bottleneck is clearly the number of updates to do, so memcache > cannot help you much. > > Based on the number of rows you have on table A, the best is the following: > - to have an index on A(type IS NULL, Number) > - to have an index on B(LowNumber, HighNumber) > - two possibilities: > - make one statement which updates all A rows where type is not > null, but it takes time > - make the following loop: > - take the first row where type is null, do one statement which > update all A rows which have the same number and type null > - reiterate while you have some rows with type is null > > The update will look like this, for A.Number = 123456. > UPDATE A SET type = (SELECT Description FROM B WHERE LowNumber <= > A.Number AND HighNumber >= A.Number) WHERE Type IS NULL AND Number = > 123456; > > Hope it helps > > Ludovic > > On Fri, Jun 13, 2008 at 11:35 AM, Arun Singhal > <[EMAIL PROTECTED]> wrote: >> Hi, >> >> >> >> Comments are added below. >> >> >> >> Thanks, >> >> >> >> Arun Singhal >> >> >> >> >> >> -----Original Message----- >> From: Ludovic Levesque [mailto:[EMAIL PROTECTED] >> Sent: Friday, June 13, 2008 2:51 PM >> To: Arun Singhal >> Cc: Dustin Sallings; memcached@lists.danga.com; Brian Aker >> Subject: Re: memcache as database >> >> >> >> Hi Arun, >> >> >> >> some questions: >> >> table A column Number: is it unique or no ? Which range is used for >> >> this numbers ? >> >> It column is not unique. This column have long number representation of an >> IP address. For e.g. Ip Address = 1.0.157.34 can be converted to long > number >> = 16817442 >> >> >> >> I think there is no overlap in interval of Numbers in table B, right ? >> >> Yes, there is no overlap of numbers in table B. >> >> >> >> What must be the final result ? >> >> All numbers from B (from the minimum of LowNumber to maximum of >> >> MaxNumber) must be in table A ? Or no ? >> >> No, it is not required. In final result I want that if Number column value >> from table A lie in any range of table B then its Type column got update > by >> description column of B table. >> >> >> >> Ludovic >> >> >> >> On Fri, Jun 13, 2008 at 10:57 AM, Arun Singhal >> >> <[EMAIL PROTECTED]> wrote: >> >>> Hi, >> >>> >> >>> >> >>> >> >>> Lets take an example: >> >>> >> >>> >> >>> >> >>> 1. My first table say A (this table have approximate 100 million records) >> >>> >> >>> >> >>> >> >>> Column- ID >> >>> >> >>> Column- Number >> >>> >> >>> Column-Type >> >>> >> >>> 1 >> >>> >> >>> 123456 >> >>> >> >>> Null >> >>> >> >>> 2 >> >>> >> >>> 111111 >> >>> >> >>> Null >> >>> >> >>> 3 >> >>> >> >>> 222222 >> >>> >> >>> Null >> >>> >> >>> .. >> >>> >> >>> .. >> >>> >> >>> .. >> >>> >> >>> 10000000 >> >>> >> >>> 333333 >> >>> >> >>> Null >> >>> >> >>> >> >>> >> >>> >> >>> >> >>> Now I want to check the value of 'Number' column and if it satisfy the >> >>> condition then want to update 'Type' column. >> >>> >> >>> >> >>> >> >>> 2. My second table say B (this table has approximate 4 Lakh records) >> >>> >> >>> >> >>> >> >>> Column- ID >> >>> >> >>> Column- Low_Number >> >>> >> >>> Column High_Number >> >>> >> >>> Column_Description >> >>> >> >>> 1 >> >>> >> >>> 111111 >> >>> >> >>> 111119 >> >>> >> >>> A >> >>> >> >>> 2 >> >>> >> >>> 222222 >> >>> >> >>> 222230 >> >>> >> >>> B >> >>> >> >>> 3 >> >>> >> >>> 123450 >> >>> >> >>> 1234580 >> >>> >> >>> C >> >>> >> >>> .. >> >>> >> >>> .. >> >>> >> >>> .. >> >>> >> >>> .. >> >>> >> >>> 4 >> >>> >> >>> 333320 >> >>> >> >>> 333339 >> >>> >> >>> D >> >>> >> >>> >> >>> >> >>> >> >>> >> >>> 3. following are the steps I am using inside a stored procedure. >> >>> >> >>> >> >>> >> >>> a) select Low_Number as low, High_Number as high, Description as >>> description >> >>> from B >> >>> >> >>> b) Loop start >> >>> >> >>> c) update A set Type=description where Type is null and Number between > low >> >>> and high >> >>> >> >>> d) end loop >> >>> >> >>> >> >>> >> >>> >> >>> >> >>> Now, the problem is huge data in both the tables as it is taking huge > time >> >>> (in days). Can anybody guide me now? >> >>> >> >>> >> >>> >> >>> Thanks, >> >>> >> >>> Arun Singhal >> >>> >> >>> ________________________________ >> >>> >> >>> From: Dustin Sallings [mailto:[EMAIL PROTECTED] >> >>> Sent: Friday, June 13, 2008 2:01 PM >> >>> To: Arun Singhal >> >>> Cc: 'Brian Aker'; memcached@lists.danga.com; [EMAIL PROTECTED] >> >>> Subject: Re: memcache as database >> >>> >> >>> >> >>> >> >>> >> >>> >> >>> On Jun 13, 2008, at 1:06, Arun Singhal wrote: >> >>> >> >>> I have million of reords in a table and I want to update a column in the >> >>> >> >>> same table by checking value of another column in the same table. For > e.g. >> >>> >> >>> two columns A and B. Now if the value of column A is X then update value >>> of >> >>> >> >>> column B as true else false. Now to apply this functionality on that > table >> >>> >> >>> in mysql is taking huge time. >> >>> >> >>> >> >>> >> >>> How are you attempting to do it now? I can't imagine you > being >> >>> able to do that faster than with a simple SQL query. >> >>> >> >>> I want to reduce the time by using memcache. >> >>> >> >>> Can you guide me how can I do that using memcache? >> >>> >> >>> >> >>> >> >>> This is sort of like declaring your car to be too slow and >> >>> asking someone to install a larger exhaust pipe on it because fast cars >>> have >> >>> large exhaust pipes. You may find that the larger exhaust pipe doesn't do >> >>> nearly as much to speed up your car as shifting into second gear does. >> >>> >> >>> >> >>> >> >>> So to truly help you, we must first understand your problem. >>> My >> >>> suspicion is that you just have a bad query or too few or (or possibly > too >> >>> many) indices. If I believed memcached would solve your problem, I > *still* >> >>> wouldn't be able to tell you how it to apply it without knowing exactly >>> what >> >>> you're doing. > >
