I have a problem with a select statement that I am using in a web
search engine.
I have the following SQL:
SELECT
da_records.TITLE_EN AS TITLE,
da_records.AUTHOR_EN AS AUTHOR,
da_records.DESCRIPTION_EN AS DESCRIPTION,
da_records.PUBLISHER_EN AS PUBLISHER,
da_records.URL_EN AS URL,
da_records.RECORD_ID,
da_records.KEYWORD_LIST_ID,
da_records.LANGUAGE,
da_records.CONTRIBUTOR_NAME,
da_records.CONTRIBUTOR_EMAIL,
da_records.CONTRIBUTOR_ORGANISATION,
da_records.CONTRIBUTOR_CREDIT,
da_records.DEWEY_LIST_ID,
da_records.LISTING_PRIORITY,
da_records.SUBMITTED_DATE,
da_records.LAST_EDIT_DATE,
da_records.STATUS
FROM
da_records,
lu_dewey,
da_dewey_list
WHERE
da_records.RECORD_ID = da_dewey_list.RECORD_ID AND
lu_dewey.DEWEY_ID = da_dewey_list.DEWEY_ID AND
lu_dewey.DEWEY_LANG = 'en' AND
lu_dewey.DEWEY_TYPE = 't' AND
da_records.DESCRIPTION_EN like '%nasty%' OR
da_records.TITLE_EN like '%nasty%'
"nasty" is obviously the search term and if I search for a word which
is unlikely to be found, the query returns 0 results in a fairly short
time, as would be expected. However, if I search for a common word, the
query times out (process gets killed if running it from the pgsql
commandline). Its as if the query is returning a resultset which is too
big for a buffer or something.
I'm afraid I don't know Postgres that well yet so I can't tell you how
big the DBs are but in MySQL they were under 10MB total size, so even a
complete resultset shouldn't be too much for the machine.
Incidentally the hardware I'm running on is: Dual 2GHz Opteron, 2GB
RAM, SCSI. Hardly a slow system!
Any pointers at either, more efficiend SQL or ways to tweak Postgres
will be gladly recieved.
Many thanks in Advance
--
Dan Field <dof@llgc.org.uk> - Support Programmer: Cymru ar y we
cy_GB: http://www.cymruarywe.org
en_GB: http://www.walesontheweb.org
