SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How can I speed up this query?

    I've got a table with just over 1 million rows and am trying to do the following fulltext search on it:

    Code:
    SELECT `id`, `parent` 
    FROM `comments` 
    WHERE MATCH(title, body) AGAINST('*search term*' IN BOOLEAN MODE) 
      AND `type` = 'forum' 
      AND `active` = 'Y' 
      AND `item_id` != 45
    Here is the EXPLAIN output:
    Code:
    id  select_type   table	    type   possible_keys   key   key_len	ref	rows      Extra  
    1    SIMPLE	   comments	fulltext	commentindex,active_reported,title	title	    0	   1   Using where
    It's not using any sort of filesort or temporary tables or anything, but the search on this table can take upwards of 20+ seconds.

    Any ideas?

  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)
    1 million rows? fulltext? 20 seconds? sounds about right.

    what happens if you remove IN BOOLEAN MODE?
    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
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just tried it, it might have have shaved off 2 seconds from the 20 seconds...so nothing significant enough.

  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)
    ok, that was a test to make sure you had a properly defined FULLTEXT index. you passed.

    what do you get for this query?
    Code:
    SELECT count(*) 
    FROM `comments` 
    WHERE `type` = 'forum' 
      AND `active` = 'Y' 
      AND `item_id` != 45
    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

  5. #5
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha, glad I passed.

    That query gets me 108,056.

  6. #6
    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)
    i would check two things.

    first, try forcing use of an index that covers (type, active, item_id).

    second, make sure your server is configured with a large enough index cache to keep all your table indexes in memory.
    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

  7. #7
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying force the use of an index and am getting:
    "Can't find FULLTEXT index matching the column list"

    Here's the query:
    Code:
    SELECT `id`, `parent` FROM `comments` USE INDEX(item_id_active_type_id) 
    WHERE MATCH(title, body) AGAINST('*search term*') AND `type` = 'forum' AND `active` = 'Y' AND `item_id` != 45
    The item_id_active_type_id is an index on item_id, active, type, and id.

  8. #8
    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)
    crap, i forgot that when you force an index, mysql won't also use the FULLTEXT index.

    so back to my other suggestion: make sure your server is configured with a large enough index cache to keep all your table indexes in memory. also, use LOAD INDEX to preload your FULLTEXT index in to memory.
    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

  9. #9
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really haven't ever used LOAD INDEX for anything. Is it something that needs to be run periodically or just once or what?

  10. #10
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, how can I tell what size cache I need?

  11. #11
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've done a little research on this over the last couple of days (but I'm not an expert). What I would do first is obviously make the fulltext index on title,body then do separate indexes on type, active and itemid or a single index on type, active then itemid. The single index on all 3 will work on any queries that have type, type and active, or type, active and itemid in the where clause so this might not be the best way to go if other queries aren't in that order. It sounds to me like the order of the current index on those 3 columns might render the index useless for that query. Have a look at http://www.sitepoint.com/article/opt...ql-application, I found it quite helpful.

  12. #12
    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)
    Quote Originally Posted by RockNRollPig View Post
    Also, how can I tell what size cache I need?
    add up the size of all of the MYI files in your data directory. add 10% for block alignment overhead. if your dataset is growing at a significant rate, make sure to factor that in, too.
    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

  13. #13
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @longneck: Is there a specific parameter that you would set to "make sure your server is configured with a large enough index cache"? I guess I'm just not following exactly what I would be configuring.

  14. #14
    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)
    the parameter is key_buffer_size. so if you have 24 MB of MYI files and you decide that 32 MB is enough to account for a decent amount of future growth, add key_buffer_size=32M to your my.ini file.
    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

  15. #15
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We have ~625M of MYI files and key_buffer is set to 768M. So seems like the there is enough space allocated to the cache.


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
  •