SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast lupulet's Avatar
    Join Date
    Oct 2001
    Location
    romania
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    searching through huge amounts of text -> very slow

    hi.
    I have a problem. I have a table which has a column called "text". OK. The problem is that the table has 6000+ rows, each one of them storinf in "text" a large amount of text, something like a story. For a better idea, the table has 40Mb (the other columns are insignificant).

    Now, when i try to search through table rows that have in this column text that match a word, no problemo:

    SELECT text FROM table WHERE text LIKE "%word%";

    But ... when i try to search through table rows that have in this column text that contains exactly a word, the only solution that i know is to use REGEXP like this:

    SELECT text FROM table WHERE text REGEXP "[[:]]word[[:>:]]";

    Welllllll, that's far too slow, about 5 time slower than example 1. And I really need that exact search through those stories after a word!!!

    What should i do? Any idea? Thanks.
    you'll never be what you desire

  2. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want an exact match you could use WHERE something = 'something' instead of using LIKE

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  3. #3
    SitePoint Enthusiast lupulet's Avatar
    Join Date
    Oct 2001
    Location
    romania
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh yeah? Don't think so ...

    How can a big story, let's say a tutorial, be equal to a word ?!?!?!?!?!
    you'll never be what you desire

  4. #4
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh crap, not with it today! Sorry. My next suggestion is use fulltext indexing

    Code:
    SELECT something FROM table WHERE MATCH (field) AGAINST ('+search_term_1 +search_term_2' IN BOOLEAN MODE);
    http://www.mysql.com/doc/en/Fulltext_Search.html

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature


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
  •