Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14

    Unanswered: How to create increase search performance using fulltext index?

    I have got a table with 2 columns on which I would like to perform a text search. The table details are as follows.

    here is the show create table

    CREATE TABLE `dc_titles` (
    `titleid` bigint(20) NOT NULL AUTO_INCREMENT,
    `titletext` varchar(128) NOT NULL,
    PRIMARY KEY (`titleid`),
    FULLTEXT KEY `titletext` (`titletext`)
    ) ENGINE=MyISAM AUTO_INCREMENT=610 DEFAULT CHARSET=latin1

    Now I would like to perform a text search on this table for finding relevant titles for a string. for example I want to find relevant titletext for the term "Best computer shops".

    I used a query

    SELECT *, MATCH(titletext) AGAINST('Best computer shops') AS score WHERE score > 1 ORDER BY score LIMIT 0,20;

    This is working well if the total number of records in the table is less than 50000 records. When it is crossing 50000 records it is loading server heavily.

    Is this the correct way of using query or is there more better way to improve search performance?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that query couldn't possibly have worked

    first of all, you cannot assign a column alias in the SELECT clause and then use that alias in the WHERE clause

    more importantly, however, you're missing your FROM clause

    please do a SHOW CREATE TABLE so we can see what indexes the table has
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14
    Quote Originally Posted by r937 View Post
    that query couldn't possibly have worked

    first of all, you cannot assign a column alias in the SELECT clause and then use that alias in the WHERE clause

    more importantly, however, you're missing your FROM clause

    please do a SHOW CREATE TABLE so we can see what indexes the table has
    Here is the show table

    CREATE TABLE `dc_titles` (
    `titleid` bigint(20) NOT NULL AUTO_INCREMENT,
    `titletext` varchar(128) NOT NULL,
    PRIMARY KEY (`titleid`),
    FULLTEXT KEY `titletext` (`titletext`)
    ) ENGINE=MyISAM AUTO_INCREMENT=610 DEFAULT CHARSET=latin1

    Now the exact query what I am using is

    SELECT *, MATCH(titletext) AGAINST('Best computer shops') AS score FROM dc_titles WHERE (MATCH(titletext) AGAINST('Best computer shops')) > 1 ORDER BY score DESC LIMIT 0,20;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks fine, thanks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14
    Quote Originally Posted by r937 View Post
    that looks fine, thanks
    Is there any other way to make my search more faster? This query is loading my server heavily after reaching 50000 records.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i have no idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •