From: | "K Old" <kevsurf4(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Database tuning |
Date: | 2001-12-21 17:03:43 |
Message-ID: | F55Hp0pAa2G335NHsvX00009046@hotmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello all,
I have a PostgreSQL database that is storing The Bible. It has 31,103
records in it and I have a PHP page executing this query:
SELECT
books.book_name, bible.chapter, bible.verse, bible.versetext
FROM asv_bible bible, book_bible books WHERE bible.book = books.id ORDER BY
random() LIMIT 1
The database schema is:
/* --------------------------------------------------------
Sequences
-------------------------------------------------------- */
CREATE SEQUENCE "book_bible_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1;
/* --------------------------------------------------------
Table structure for table "asv_bible"
-------------------------------------------------------- */
CREATE TABLE "asv_bible" (
"id" int8 NOT NULL,
"book" int8,
"chapter" int8,
"verse" int8,
"versetext" text,
CONSTRAINT "asv_bible_pkey" PRIMARY KEY ("id")
);
/* --------------------------------------------------------
Table structure for table "book_bible"
-------------------------------------------------------- */
CREATE TABLE "book_bible" (
"id" int4 DEFAULT nextval('book_bible_seq'::text) NOT NULL,
"book_name" varchar(20),
CONSTRAINT "book_bible_pkey" PRIMARY KEY ("id")
);
Right now it takes 9 seconds to return the results. I don't think that it
has anything to do with the language executing it, as I have run the same
query in via Perl and had the same luck.
I was wondering if anyone could offer any help with lowering the time it
takes to run?
Am I optimizing the database correctly?
My hardware is a Pentium II 400 with 128MB of RAM.
Or if this is the normal runtime for a database of this size, I'd just like
confirmation.
Thanks,
Kevin
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Harding | 2001-12-21 17:19:51 | Re: How Many Years have Passed? |
Previous Message | Joe Koenig | 2001-12-21 16:49:55 | Is the Windows Version Stable? |