Subject:
|
Re: Gallery speed
|
Newsgroups:
|
lugnet.off-topic.geek
|
Date:
|
Fri, 6 Jul 2001 02:34:18 GMT
|
Viewed:
|
202 times
|
| |
|
|
In lugnet.off-topic.geek, Kevin Wilson writes:
> Kevin Loch wrote in message ...
> > here's the "slow" query that is currently limiting performance:
> >
> > SELECT DISTINCT file_parent FROM files ORDER BY file_modify_time DESC LIMIT
> 24
>
> > omitting the DISTINCT above causes it to return instanly but with many
> > duplicates, since there is often more than one new image per folder.
>
> Might be quicker to do it in 2 stages, first the line above without the
> DISTINCT into a cursor, then do a SELECT DISTINCT from that.
>
> SELECT file_parent FROM files ORDER BY file_modify_time DESC into cursor
> temp
> SELECT DISTINCT file_parent FROM temp LIMIT 24
This probably wouldn't help much - the cursor isn't indexed, so the SELECT
DISTINCT would be slow.
A better way to split it (assuming your back-end has a decent optimiser) may
be to create an index on file_parent + file_modify_time, then do:
SELECT file_parent,MAX(file_modify_time) AS file_modify_time FROM files
GROUP BY file_parent into cursor temp
which will also do the DISTINCT, then
SELECT file_parent FROM temp ORDER BY file_modify_time DESC LIMIT 24
However, I think storing each folder's mod time would make more difference.
ROSCO
|
|
Message is in Reply To:
 | | Re: Gallery speed
|
| Kevin Loch wrote in message ... (...) 24 (...) Might be quicker to do it in 2 stages, first the line above without the DISTINCT into a cursor, then do a SELECT DISTINCT from that. SELECT file_parent FROM files ORDER BY file_modify_time DESC into (...) (23 years ago, 6-Jul-01, to lugnet.off-topic.geek)
|
7 Messages in This Thread:
  
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
