Hi, Just have a couple more full-text search inquiries here. :-)
I'm not exactly clear on how matching rows are found when searching for 2 or more required words: '+word1 +word2'. I understand that it can't currently know which word occurs less, so that it can be searched first -- this optimization will come with 4.1's 2-level indexes. :-) I just want to know, when it finds a match for whichever word is tried first, how does it check if the other required word(s) are present in the same row? Say that word1 and word2 are each present in 100,000 rows. 1) Surely it doesn't check the 100,000 entries for word2 for EACH word1 match to see if they're in the same row, does it? 2) It *seems* the best way would be to do a lookup for (word2 + <rowid for word1>) and see if there's a match. Is this what's done? I'm not sure it's possible though with the way the index is structured... 3) Or, and I'm thinking *maybe* this is how it's done from what I've heard, does it get all the matches for word1, then for word2, and then "intersect" them to find ones which are present in the same row? If so, how will the 2-level index optimization change things? Will it do #2? Next question is... a few weeks ago I was doing some test searches like '+word1 +word2'. Actually, maybe I was only using 1 word, I can't remember, but I don't think it matters. Anyway, I happened to try changing the query to '+word1* +word2*' -- e.g. adding a wild-card to the end of the same word(s) -- and I was amazed at how much faster the query was! (And no, there's no query cache; and they were both run many times so the index was cached. :-)) Can't remember how much faster, but it wasn't insignificant. Then I tried adding a wild-card to the end of words in another search (the wild-card did not make more rows match as far as I know), but that made it a little slower (I'd expect that, if anything). Is there any explanation for why adding wild-cards would make a search faster? Thanks in advance! Matt P.S. Sergei, if you see this, in one of your replies to my full-text suggestions back in September ( http://lists.mysql.com/mysql/149644 ), you said "Another reply will follow..." I never saw another reply though. :-/ It's OK, I was just wondering what other interesting things you were going to say! :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
