SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hostrocket closed my account because their servers couldn't take it.

    I run an 80mb mysql db.

    Some of the fields will have an entire chapter of text in them - and the most popular function on my website is searching through these fields.

    Is there any way to optimize this procedure?

    I would like to stay with hostrocket because I like everything about them except what their servers can handle - if perhaps I found a way to decrease the system resources required by my site (which btw my old host didn't have a problem with) I could still use them.

    If someone needs more information and would like to email me that'd be fine too.

    Chris
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I indexed 3 of the 4 fields in that table - that should help no?
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried using FULLTEXT indexes? I have *no* experience or knowledge of them, but they are designed for what you are doing. http://www.mysql.com/doc/M/y/MySQL_f...xt_search.html

    Anyway, I will follow this thread with interest. Good luck!

  4. #4
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Get hold of vBulletin 2 and have a look at how they index posts ready for searching. They have a seperate table for 'words' which contains a field for a word, then they have another table which relates words to posts that they appear in.

    When you add a new item to the database (or hit the re-index option in the admin control panel) a script goes through all the posts splitting them into lower case words and removing all punctuation etc. The script that adds words that are not already there to the word list, and adds a load of entries to the relationship table. A list of common words (such as and and the) are ignored.

    These tables are then used to dramatically speed up searches. I've only had a breif look at vBulletin 2 so some of the above may be incorrect but it demonstrates the principle just fine.


Bookmarks

Posting Permissions

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