What would be the Fastest Way of Text Search thru database with 1 Mil. Records

Discussion in 'Databases' started by deriklogov, Jul 7, 2009.

0
  1. #1
    What would be the Fastest Way of Text Search thru database with 1 Mil. Records.

    I try :

    SELECT *
    FROM table
    WHERE MATCH (
    title, descr
    )
    AGAINST (
    '+hardwood'
    IN BOOLEAN
    MODE
    )
    LIMIT 0 , 30

    takes 1 minute

    using LIKE '%word%' takes 1 Minute too
     
    deriklogov, Jul 7, 2009 IP
  2. nyxano

    nyxano Peon

    Messages:
    417
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Personally I've always used the LIKE '%word%' method and have never had a problem with it. I think both methods are pretty much the same as each involves one SQL Statement performing the same task. 1 Minute on a million records is pretty good when you think about it.
     
    nyxano, Jul 8, 2009 IP
  3. deriklogov

    deriklogov Well-Known Member

    Messages:
    1,078
    Likes Received:
    22
    Best Answers:
    0
    Trophy Points:
    130
    #3
    I think its toooooooooo slooowwwwww
     
    deriklogov, Jul 8, 2009 IP
  4. shaibibutt

    shaibibutt Member

    Messages:
    606
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    35
    #4
    hmmm 1 million records
    I think a select query in another select query can help u out

    try it

    select * from abc
    where ( Select attribute1 from abv where attribute1=anyvalue;);
     
    shaibibutt, Jul 8, 2009 IP
  5. Goramba

    Goramba Peon

    Messages:
    128
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #5
    The size of the table is sometimes a limiting factor. If the size of the column you're searching is small but the table as a whole is very large you will probably benefit from creating another table with just the search column and matching it against the original table. You can create a trigger that will insert into both if necessary. Haven't done it with fulltext but it has worked amazing with me and other queries.

    EG:

    Make sure originaltable has a unique id column, auto_increment. Then:

    create table search(id int(11) not null auto_increment key,
    title longtext, descr longtext);
    ALTER TABLE search ADD FULLTEXT(title, descr);
    query = SELECT s.*,originaltable.* FROM search s left join originaltable o on o.id=s.id WHERE MATCH (s.title, s.descr) AGAINST ('+hardwood' IN BOOLEAN MODE) LIMIT 0 , 30

    Alter the select to only grab what you need.

    If you're doing it in php you can just grab the ID from the search table in one query then grab the result from originaltable in another query. Sometimes it's faster than a join.
     
    Goramba, Jul 11, 2009 IP