SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to optimize mysql fulltext union search?

    I am making a mysql fulltext search.

    my database table article1 has ~18000 articles, article2 has ~7000 articles, article3 has ~13000 articles. FIELD cat is a INDEX field

    Now I want to make a union search. there are 5 groups words put into 3 table, match out the results. But the process time is 3.1213495136 seconds. (I add microtime() to see how much time it will cost). Is there any way to optimize mysql fulltext union search? Thanks.


    Code:
    (SELECT title,content,date FROM article1 WHERE 
    (cat='novel' AND MATCH (title,content) AGAINST ('+Mary +Barnard' IN BOOLEAN MODE)) 
    OR 
    (cat='novel' AND MATCH (title,content) AGAINST ('+Patricia +Beer' IN BOOLEAN MODE)) 
    OR 
    (cat='novel' AND MATCH (title,content) AGAINST ('+Aphra +Behn' IN BOOLEAN MODE)) 
    OR 
    (cat='novel' AND MATCH (title,content) AGAINST ('+Judy +Blume' IN BOOLEAN MODE)) 
    OR 
    (cat='novel' AND MATCH (title,content) AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE)))
    UNION 
    (SELECT title,content,date FROM article2 WHERE 
    (MATCH (title,content) AGAINST ('+Mary +Barnard' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content) AGAINST ('+Patricia +Beer' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content) AGAINST ('+Aphra +Behn' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content) AGAINST ('+Judy +Blume' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content)AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE)))
    UNION 
    (SELECT title,content,date FROM article3 WHERE 
    (MATCH (title,content) AGAINST ('+Mary +Barnard' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content) AGAINST ('+Patricia +Beer' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content) AGAINST ('+Aphra +Behn' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content) AGAINST ('+Judy +Blume' IN BOOLEAN MODE)) 
    OR 
    (MATCH (title,content)AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE)))
    Order By date DESC LIMIT 10

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    1. why are there three apparently identical article tables?

    2. why are you using IN BOOLEAN MODE?

    3. do the tables actually have fulltext indexes on them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @r937,
    1.the three tables were bulit long time ago, each table fields has some different.
    2.I want make a precise match out, not a `like`.
    3.cat is the tables INDEX, but in this search require, just table article1 need use an INDEX. Thanks.


Tags for this Thread

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
  •