| |
I think I've fixed the painfully slow performance of the "recent" page.
There were some indexes missing in the database that really should have
been there. It loads reasonably fast now (~.8 seconds unless the
machine is really busy).
If there are any database wizards out there, maybe you can help make it
even faster.
here's the "slow" query that is currently limiting performance:
SELECT DISTINCT file_parent FROM files ORDER BY file_modify_time DESC LIMIT 24
each file_parent is then used to retrieve a folder record with icon information
etc. Those 24 queries are extremely fast (0.01 secs ea) so that's not the
problem. I do have indexes for file parent and file_modify_time.
Most queryies return ~instantly (0.01 secs), but the above query returns
in 0.76 seconds. Is there a faster way to do this?
It would be much faster to do this:
SELECT * FROM files ORDER BY file_modify_time DESC LIMIT 24
but that would include empty folders.
omitting the DISTINCT above causes it to return instanly but with many
duplicates, since there is often more than one new image per folder.
KL
|
|
Message has 4 Replies:
 | | Re: Gallery speed
|
| (...) One speed-up would be to store the modify date/time of each folder (updated when a file is added / updated / deleted) and then query directly against the folders. It also depends on your back-end query optimiser - even if you're only selecting (...) (23 years ago, 5-Jul-01, to lugnet.off-topic.geek)
| | | 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)
| | | Re: Gallery speed
|
| Both myself and the original poster were mystified as to why this link didn't work for me - have you any idea as to why not? (URL) ... Geoffrey Hyde "Kevin Loch" <kloch@opnsys.com> wrote in message news:GG0H6J.ALE@lugnet.com... (...) LIMIT 24 (...) (...) (23 years ago, 6-Jul-01, to lugnet.general, lugnet.off-topic.geek)
| | | Re: Gallery speed
|
| (...) I'd go for amalgamating the two steps into one query - something like: SELECT id, name, icon_information, etc. FROM file_parent WHERE file_parent.id IN (SELECT file_parent from files) This avoids the problem of having to query for DISTINCT (...) (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
|
|
|
|
