Re: PostgreSQL query taking a long time

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.