Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    30

    Unanswered: How to improve DB search performance and make search faster?

    My site is using PHP + PostgreSQL. There are about 50,000 row in one of the tables and it's kind of slow to make keywords search. Is there any way in DB side can make the search faster?

    thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    the first thing that comes to mind is to add an index to the table/field in question. With a well normalized design, this usually helps.

    Note however, that nothing comes for free. (the old saying "There's NO such thing as a Free Lunch," comes to mind.) While indexing often improves searches, it will slow down inserts, deletes, and updates (if the indexed field is changed.) Plus, it makes the database larger.

    Without knowing more about the table design and usage, it's difficult to offer any more relevant responses. For instance, what is the field type that you're searching? Does the field contain a single word, or is it more or less free-form text? What do the current SQL queries look like?
    Last edited by loquin; 09-07-07 at 13:30.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    I am assuming that by keyword you mean that there are 'text' columns and you are allowing users to search for keywords in a whole bunch of text on each row for thousands of rows.

    I have never messed with this, but outside of indexing (which I don't believe will work well for text types) you could use a module like OpenFTS or Tsearch2 (there may be some others)

    http://openfts.sourceforge.net/ (OpenFTS off of the postgresql site)
    www.postgis.org (This link doesn't seem to work, but PostGIS used to make Tsearch2 or something named like that. It would do text searches I believe)

    I have never used this.. I am only aware of it. I can't do much here but say 'there is stuff out there that you might be interested in' hehe

    good luck.

  4. #4
    Join Date
    Aug 2003
    Posts
    30
    Thank you all, I will try those modules

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •