0

I use the following query to get the highest id of a set of rows, but since in some cases, there are tens of thousands or hundreds of thousands of rows in the set, making the query very inefficient. Is there a way to accomplish the same result in a more efficient way?

SELECT MAX(id) FROM table WHERE groupID = '12345'
James Simpson
  • 13,488
  • 26
  • 83
  • 108
  • Have you tried `EXPLAIN SELECT ...`? What indexes do you have on your table? – Brian Hooper Dec 18 '10 at 09:09
  • Yes, I have an index on groupID. The table itself has millions of rows, so the index narrows it down to rows in that specific group, but that group could still have a few hundred thousand rows. – James Simpson Dec 18 '10 at 17:23

4 Answers4

1

The ugly fix is to add an index on groupID, id

alter table `table` add index groupId_with_id_idx (groupId, id);
desc SELECT MAX(id) FROM table use index (groupId_with_id_idx) WHERE groupID=12345;
/* the execution plan should return "Select tables optimized away" */

What is Select tables optimized away?

Community
  • 1
  • 1
ajreal
  • 46,720
  • 11
  • 89
  • 119
0

If the column is an AutoNumber Identity Column, you could do something like:

 IDENT_CURRENT('table')

But that could be hit or miss. If you are using it with a where, i doubt it would get you what you want.

Cheers,

Stefan H
  • 6,635
  • 4
  • 24
  • 35
  • This won't work since the maximum id for a specific group usually won't be the last inserted row. – James Simpson Dec 18 '10 at 07:25
  • Yeah, i figured that your where clause would throw it off. I'm not sure what better option you have than max. I can't imagine a top 1 order by id would be better. – Stefan H Dec 18 '10 at 07:27
0

Alternate query

SELECT * FROM table WHERE groupID = '12345' ORDER BY id DESC LIMIT 1;
Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
0

I believe it would be faster to even ORDER BY id and pick the LIMIT 1 occurrence of groupid, but MySQL can't use an index when you're ordering by a key and fetching another constant key because it's still reading each line.

If you ORDER BY groupid (asc or desc), is the first or last record within that groupid always the highest id? Then your fastest query would be to order by groupid and select LIMIT 1 for the first record listed in that groupid.

danneu
  • 9,244
  • 3
  • 35
  • 63
  • It would be the first one, but I'm not seeing how this would be faster. EXPLAIN still shows that it returns the same number of rows as using MAX. – James Simpson Dec 18 '10 at 17:22