Hi,

You dont have the book field in the asv_bible table indexed.  Use this to 
index it....

CREATE INDEX myindexname ON asv_bible(book);

(the primary key fields are ok because PostgreSQL creates a unique index to 
implement the PRIMARY KEY constraint).

Hope that helps :)

--
Shane

> 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]

Reply via email to