SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Slow SQL search

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2003
    Location
    Brisbane, Australia
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow SQL search

    I have a MySQL database of archived punchlines (I run a caption contest website) that's starting to get bloated (there's now 90,000 punchlines listed). I have a search page to look through archived punchlines and often the search script takes ages to execute. Eg - the page is blank for 10 to 30 seconds then the contents finally load. I'm just using a simple search query such as "SELECT punchline, username FROM punchlines WHERE punchline LIKE '%$searchterm%'".

    So my question - is there any techniques I can try to make things quicker and more efficient or does having a database of that size lead inevitably to slow execution times?
    Tips 'n' Tutorials
    Free webmaster tools, code and resources on web design, HTML, Javascript and Internet marketing

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    your query can't use any index because of the % in front of $searchterm, so it has to do a full table scan, i.e. looking through all 90.000+ lines which may last some time Take a look at the fulltext index feature, maybe that's what you are looking for:


    http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


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
  •