Matt, thanks for your suggestions. The index on content_id was for deletes. You're right...I could get rid of the C.id field...it has not been useful.
To avoid making too many changes at once, I created a UNIQUE index on stem_word_id and content_id. Now EXPLAIN says it's using the UNIQUE index, but also: Using where; Using index; Using temporary; Using filesort I'm not sure if that's an improvement or not. I don't notice any speed changes. Searches can return any number of results, say a hundred or so on average. I'm using LIMIT for 25 results at a time, but also SQL_CALC_FOUND_ROWS to know how many total results there are. Some queries can take 30 or more seconds to run, average is maybe 10 seconds. The reason I'm not using MySQL's fulltext: this project started on a 3.23.x server where I had no control over the setup parameters (hosted system). There are several important words used that are < 4 characters. So I went to building my own index and using word stems (requested by the client). The searches then ran fairly quickly (faster than now), so I'm sure some of this has to do with the hardware/software configuration of that server, but I don't know what it was. The project was then moved to a new server, the one I described. I have contol over the MySQL parameters but I think the hardware/software combo is not as powerful. I installed 4.0.16 and am trying to get improve the performance of my existing stem-index search. My word search on table B is also slow at times. I wonder if I should split the word search query into separate queries to take advantage of caching. So if one person searches "piano move" and the other "play piano" and someone else "piano instruct" the results for "piano" would be cached. So many variables to consider! Thanks again for your input. Mike. -----Original Message----- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 9:25 PM To: Mike Boone; [EMAIL PROTECTED] Subject: Re: Optimizing Custom Full Text Index Hi Mike, Those tables aren't that big for what you're doing (which is about how I'd do it if I wasn't using built-in full-text :-)). How many results are your searches returning? How long are the queries taking? The C table: Do you need the index on content_id? For deletes or something? Doesn't really matter for speed, but you might as well dump it if it's not used. What's the PRIMARY id there for? Just to have an id column? :-) It looks like you can get rid of it. I'd make a composite PRIMARY KEY on (stem_word_id, content_id). Otherwise make it UNIQUE if you *need* the PRIMARY id. This will make EXPLAIN say "Using index" on C for searches which will save a lot of random disk seeks to the data file. Can I ask what the problems are with MySQL's built-in full-text search? I know there's a few since I've encountered them too, but I have some ideas to work around them. Unfortunately, one that would be hard to work around is stemming (waiting for that to be implemented internally). -( Or are you just doing stemming to save space in the index and not for functionality? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
