Hi Shane,
As many others have alluded to - performance like this is almost always
attributable to your queries not using an index. Be it on Oracle, Mysql,
or postgres, i have seen this problem popup often.
Also, could you tell us what language you are using, and if you are using
a DB abstraction layer?
On to the particulars:
> # WEBSITE #
>
> # SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES
>
> 8 Queries Totaling 10.7413 Seconds
Since one query is taking 90% of the time, it clearly is the first
cuplrit:
> SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
> DESC,t.lastreply desc LIMIT 25 OFFSET 0
> Num Rows: 25
> Affected Rows: 0
> Exec Time: 9.1602659225464
Your SQL here seems what I would consider not typical. I would write it
as:
SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply
desc LIMIT 25 OFFSET 0;
Run that from a psql shell, and see if that speed things up. If not, run:
db=> EXPLAIN ANALYSE SELECT * FROM thread_listing AS t WHERE t.status=5
ORDER BY t.lastreply desc LIMIT 25 OFFSET 0;
and
db=> \d thread_listing
And send it to the list. You are in good shape I think, and porting won't
be necessary. I've used many db's and postgres is my favorite by far. I'd
say you've made a good choice ;-)
[ \ /
[ >X< spider@steelsun.com | http://www.steelsun.com/
[ / \
