Page 1 of 1
How to get the last ID in a certain range in MySQL?
Posted: Thu Sep 23, 2004 5:34 am
by AGISB
Following scenario:
I want one database table to include customers, webmasters and agents.
As webmasters and agents are limited I want them in a certain range.
e.g. Webmasters have id numbers from 0 to 10000, Agents from 10001 - 20000 and customers > 20000
The id is autoincremented by customer so it is beyond 20000.
To add a new webmaster or agent I need to determine the highest number in their ranges.
Ok I can read everything in the range and check the last number but this could mean reading thousands of entries and this might take some time.
Is there a way where I can limit the lines that I have to read?
SELECT LAST_INSERT_ID();
Posted: Thu Sep 23, 2004 6:09 am
by phpScott
I believe if you are using MYSQL then you can use
Code: Select all
SELECT MAX( webMasterId )
FROM webMasters
or if you have just entered in a new value you can use Last_insert_id
Posted: Thu Sep 23, 2004 6:12 am
by AGISB
Would not work as it always gives me the number of the customer as it is higher than the last inserted webmaster
To clear this up: I want webmasters, agents and customers in the same table. This hast to do with authentication and other issues with referral codes etc. . If I have it in seperate tables it is a pain in the ass to make sure a webmaster id is not equal to any customer or agent id.
Here's a handy function
Posted: Thu Sep 23, 2004 6:45 am
by neophyte
I'm confused.
But here's a handy function that might help the situation.
HERE
Posted: Thu Sep 23, 2004 6:46 am
by timvw
For example
Code: Select all
SELECT MAX(user_id)
FROM users
WHERE user_id BETWEEN 1000 AND 2000
try
Posted: Thu Sep 23, 2004 6:53 am
by phpScott
Code: Select all
SELECT MAX( userId )
FROM users WHERE idType='Webmasters'
I think that is what you are looking for
Posted: Thu Sep 23, 2004 6:54 am
by AGISB
Thanks timvw:
Unfortunately:
Code: Select all
EXPLAIN SELECT MAX(Customer_ID) FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000
and my solution I had till now
Code: Select all
EXPLAIN SELECT Customer_ID FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000 ORDER BY Customer_ID DESC LIMIT 1
Shows me that MySQL has to examine all lines in the range.
At least it is shorter

Posted: Thu Sep 23, 2004 10:36 am
by Weirdan
AGISB wrote:
Unfortunately:
Code: Select all
EXPLAIN SELECT MAX(Customer_ID) FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000
and my solution I had till now
Code: Select all
EXPLAIN SELECT Customer_ID FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000 ORDER BY Customer_ID DESC LIMIT 1
Shows me that MySQL has to examine all lines in the range.
At least it is shorter

You could add index over Customer_ID field. That would dramatically increase query speed because it could get data directly from index, not accessing table at all.
Posted: Sat Sep 25, 2004 2:59 am
by AGISB
There is of course an index on Customer_ID . However it seems that MySQL has to examine at least all lines in the index field that are in the range.
I tried it with 9000 dummy entries and it is fast enough to not impose a perforamce issue.