How do you select the max/min value from a large table?

"ORDER BY column DESC LIMIT 0, 1" - too slow.

[iProgram]

I use
SELECT id FROM tab ... ORDER BY age DESC LIMIT 0, 1
or
SELECT id FROM tab ... ORDER BY age ASC LIMIT 0, 1

Is there other method to do that?

[arran]

Hi iProgram,

Try:

select min(ID) from tableA

select max(ID) from tableA

Although i'm guessing the real problem is that you don't have an index on ID. If you did, mysql wouldn't have to tablescan for the aggregate functions (min/max) or build a worktable for the 'order by'.

arran.

[iProgram]

Do you mean
SELECT max(age) from table? And I should create an index on age column?

[arran]

Exactly.

Sorry about the typo :(

[dcrombie]

SELECT MIN(age) AS min, MAX(age) AS max FROM table;

;)

[iProgram]

New question. How do you select max/min N results from a large table?

For example, I want to display some in-house ADs base on impressions, three step2:

1. SELECT id FROM tab ORDER BY impression ASC LIMIT 0, 2;
(Get two ADs with minimum impressions)

2. $id1=id of AD1, $id2=id of AD2

3. UPDATE tab SET impressoion=impression+1 WHERE id=$id1 or id=$id2 LIMIT 2

According to mysql doc, I should create an index on impression column, but it will make the UPDATE step too slow. Any alternate method?

[coopster]

You can create more than one index ;)