Subject:
|
Re: Gallery speed
|
Newsgroups:
|
lugnet.off-topic.geek
|
Date:
|
Fri, 6 Jul 2001 11:51:16 GMT
|
Viewed:
|
574 times
|
| |
|
|
In lugnet.general, Kevin Loch writes:
> 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
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 rows - a good
optimiser won't even need to look at the files table, only its index.
With your original two-stage process though, why not sort to keep duplicates
together, then skip the duplicates programmatically? Depending on what
you're programming with, that may execute quicker overall.
The other thing is to not just have indexes on the row identifiers you're
looking up, but to actually have them as PRIMARY KEYs. That enforces a
UNIQUE constraint, but databases also treat it slightly differently to a
regular unique index. If your query is looking up the primary key columns,
it can execute much faster than a simple index.
Just don't put any more columns in the primary key than are necessary to
uniquely identify a row, as you'll slow things down again.
Jason J Railton
|
|
Message is in Reply To:
 | | Gallery speed
|
| 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 (...) (23 years ago, 5-Jul-01, to lugnet.general, lugnet.off-topic.geek)
|
7 Messages in This Thread:
 
 
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
