use EXPLAIN to get more information about how postgres executes this query.
I'm not sure about this, but there are some issues with fields of type int8.
It may help to cast explicitly the fields involved in join like:
WHERE bible.book::int4 = books.id::int4
Ask it the postgres mailing list, too!
btw, what version of pg do you have?
----- Original Message -----
From: "K Old" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, December 21, 2001 5:51 PM
Subject: [PHP] PostgreSQL query taking a long time
| 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 was wondering if
| anyone could offer any help with lowering the time it takes to run?
|
| Or if this is the normal runtime for a database of this size, I'd just like
| confirmation.
|
| Thanks,
| Kevin
|
|
| _________________________________________________________________
| Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
|
|
| --
| PHP General Mailing List (http://www.php.net/)
| To unsubscribe, e-mail: [EMAIL PROTECTED]
| For additional commands, e-mail: [EMAIL PROTECTED]
| To contact the list administrators, e-mail: [EMAIL PROTECTED]
|
