| Subject | Re: [firebird-support] MAX() and index |
|---|---|
| Author | |
| Post date | 2015-09-07T13:44:05Z |
You need to use a descending index for it (pk can be descending also).
Also change your sql to:
Select first 1 mycol from mytab order by mycol desc
This will have the spected result.
Please can someone explain to me, again, why
select Max(MYCOL) from MYTAB
doesn't use the primary key
PLAN (MYTAB NATURAL)
Current memory = 3074072
Delta memory = -20
Max memory = 3217516
Elapsed time= 0.27 sec
Buffers = 150
Reads = 1811
Writes 0
Fetches = 894779
of which it is the first field
CONSTRAINT PK_MYTABPRIMARY KEY (MYCOL, OTHERCOL)
and instead reads thousands of pages from disk and takes over a quarter
of a second?
(I'm just curious and wanting to understand, really. I'm going to fix
the actual problem with a change of approach, that's needed for other
reasons anyway, which eliminates the problem query.)
--
Tim Ward