SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Searching an InnoDB table

    I'm using InnoDB to be able to add foreign key constraints between my tables.

    However, I just found out that I can't do FULLTEXT search on InnoDB tables for some reason.

    I was hoping to use this search code (PHP) which I've found online:
    Code PHP:
            $sql = "SELECT posts.*,
                            CASE WHEN posts.title REGEXP $term THEN 1 ELSE 0 END AS keyword_in_title,
                            MATCH ( posts.title, posts.body ) AGAINST ($term) AS mysql_score
                            FROM posts
                            WHERE ( posts.title REGEXP $term OR posts.body REGEXP $term )
                            ORDER BY keyword_in_title DESC, mysql_score DESC, posts.date DESC LIMIT 0, 10";

    but since I'm using InnoDB, I can't.

    Are there any equivalents to this query InnoDB? because that's what I EXACTLY need.

    Appreciate your help

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    nope. for righ tnow, you have to make a choice: fulltext and myisam, or foreign key constraints and innodb.

    in the future (mysql 6) fulltext and foreign key constraints will be supported by both.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow that's terrible.

    So what's the most effective way to search an Innodb table?

  4. #4
    SitePoint Enthusiast jameso's Avatar
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mPeror View Post
    Wow that's terrible.

    So what's the most effective way to search an Innodb table?
    I would also like to know the best way to search an InnoDB table.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    right now, LIKE is your only option. if you can stand it, you could move just the columns you want a FULLTEXT index on to a myisam table. or you could also roll your own fulltext indexing code in your host language, or possibly adapt one from another application such as vbulletin.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •