SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question about indexes

    I added a new column called 'title', which is basically a duplicate of another column called summary. I then edited my index to include this new column, but now the following query errors out. What did I do wrong?


    Query: SELECT tID, date_format(dateAdded, '%m-%d-%Y') as dateAdded, viewed, title, MATCH (title,testimony,keywords) AGAINST ('lemon') AS score FROM testimonies WHERE MATCH (title,testimony,keywords) AGAINST ('lemon') and approved = 'Yes' order by score desc limit 50
    Error: mysql_errno() Can't find FULLTEXT index matching the column list
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE please?

    meanwhile, a comment or two about your query

    you don't have to force the ORDER BY for score, because that's the default

    also, if you expect to find the search word in one of three columns, you should include all three columns in the SELECT clause

    also, did you fix your Unknown column 'memberType¬ ' problem in the other thread?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    CREATE TABLE `testimonies` (
    `tID` int(8) NOT NULL AUTO_INCREMENT,
    `date` datetime DEFAULT NULL,
    `dateAdded` datetime DEFAULT NULL,
    `author` int(4) NOT NULL DEFAULT '0',
    `uID` int(5) NOT NULL DEFAULT '0',
    `approved` varchar(35) DEFAULT NULL,
    `viewed` int(4) DEFAULT '0',
    `questions` int(4) DEFAULT '0',
    `keywords` varchar(100) DEFAULT NULL,
    `summary` varchar(100) DEFAULT NULL,
    `title` varchar(100) DEFAULT NULL,
    `testimony` mediumtext,
    `timeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`tID`),
    FULLTEXT KEY `summary` (`summary`),
    FULLTEXT KEY `keywords` (`keywords`,`summary`,`testimony`),
    FULLTEXT KEY `title` (`title`)
    ) ENGINE=MyISAM AUTO_INCREMENT=7463 DEFAULT CHARSET=latin1

    The purpose of my code change is to being the process of switching over to new column names that I will start using from now on with my new website, while the old website continues to reference the older column names.

    And unfortunately I haven't solved the other issue with 'memberType¬ '

    Can you send me a private note? I think I would like to pay you some money to login to phpMyAdmin one of these days to help tune my whole database.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    FULLTEXT KEY `summary` (`summary`),
    FULLTEXT KEY `keywords` (`keywords`,`summary`,`testimony`),
    FULLTEXT KEY `title` (`title`)
    there's the reason you were getting that error message

    none of your fulltext indexes is built on the three columns you were trying to search -- title,testimony,keywords
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •