SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow Full-Text Search when table grows constantly

    Hello,
    I have a small issue with a web-search engine I'm working on. The main table is constantly growing (1 insert per second, currently 150 000 records) and it has full-text indexes on 2 fields that contain over 20 000 characters on each row.

    The thing is, as I could observe, that when multiple different full-text searches are made in appropiate period of time, the query doesn't take so much to respond (though i couldn't quite say it's fast), but when no search is made, let's say, for over 2-3 hours, the same query takes up to 30-40 seconds to respond.

    The query is simple:
    SELECT
    id
    day_processed,
    date_processed,
    site_id,
    MATCH (content1, content2) AGAINST ('some text here')
    FROM details
    WHERE (1 = 1)
    AND MATCH (content1, content2) AGAINST ('some text here' IN BOOLEAN MODE)


    The table is defined like this:
    CREATE TABLE `details` (
    `id` int(11) NOT NULL auto_increment,
    `day_processed` date default NULL,
    `date_processed` datetime NOT NULL default '2008-01-01 00:00:00',
    `url` varchar(500) NOT NULL default 'http://',
    `content1` varchar(1000) character set utf8 default NULL,
    `content2` varchar(20000) character set utf8 default NULL,
    `site_id` int(3) unsigned NOT NULL default '0',
    `price` varchar(15) default NULL,
    `phone` varchar(50) default NULL,
    `email` varchar(40) default NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `url` USING HASH (`url`(200)),
    KEY `date_processed` (`date_processed`),
    KEY `id_site` (`id_site`),
    KEY `day_processed` (`day_processed`),
    FULLTEXT KEY `content` (`content1`,`content2`)
    ) ENGINE=MyISAM AUTO_INCREMENT=156243 DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC

    The MySQL Server is configured as Dedicated Server Machine (with MySQL Instance Configurator), it has dual core processor and 2Gb of RAM.

    I would appreciate any suggestion for ways to improve speed. Please let me know if you need any additional information.

    Best regards

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    It almost sounds like the machine is going to sleep or something when there aren't any queries coming in.

  3. #3
    SitePoint Member
    Join Date
    Mar 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't think the machine sleeps... queries (but only insert/update queries) are happening second by second.

  4. #4
    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)
    when you run a query, the operating system reads the data off disk and in to the cache, then provides the data to mysql. if the query is run infrequently, then the data used by the query is easily pushed out of the disk cache by other, more frequently used data. so the next time you run the query it takes longer to process because it has to read the data off disk again.

    my first suggestion would be to increase key_buffer_size to be at least 50% larger than the total sum of your frequently used indexes. if loading the indexes in to memory is causing the slow down, then this will solve your problem.

    my second suggestion would be to change content1 and content2 to TEXT instead of VARCHAR. TEXT columns are not stored with the rest of the row, making access to the rest of the row faster. if you do not frequently access content1 and content2, this should speed up your query. note that changing to TEXT will not affect the speed of fulltext queries.


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
  •