Re: PLEASE GOD HELP US!

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(at)steelsun(dot)com | http://www.steelsun.com/
[ / \