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 8)

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

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.