How to get the last ID in a certain range in MySQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

How to get the last ID in a certain range in MySQL?

Post 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?
Last edited by AGISB on Thu Sep 23, 2004 6:36 am, edited 1 time in total.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

SELECT LAST_INSERT_ID();

Post 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
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Here's a handy function

Post by neophyte »

I'm confused.

But here's a handy function that might help the situation.

HERE
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

For example

Code: Select all

SELECT MAX(user_id)
FROM users
WHERE user_id BETWEEN 1000 AND 2000
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

try

Post by phpScott »

Code: Select all

SELECT MAX( userId )
FROM users WHERE idType='Webmasters'
I think that is what you are looking for
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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 8)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 8)
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.
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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.
Post Reply