My MySQL table has about 1.771.631 rows. A query "SELECT ... FROM `suchmaschine_produkte` WHERE `produktname` LIKE 'maschinenbau%' runs infinitely. I use mtop to kill that process. I suppose some record seems to contain characters which totally corrupt the index on produktname.
A "REPAIR TABLE suchmachine_produkte" actually does not help at all. Strangely enough this problem just showed up a few hours ago, so I have to assume some strange character has creeped in. Could that be? What else can cause such a problem? Any experiences with such a problem? What can I do to debug?
Put an index on `produktname`. I've seen adding an index speed up queries by a couple orders of magnitude (no guarantee it'll help you that much but it certainly won't hurt).
And ya, setting up a full text search would also help.
feyd wrote:aren't keys and index the same? visionmaster has a key on produktname ..
may be sometimes people fail to realize indexes with unique property are automatically set for primary keys. i have seen msyql allowing two or more indexes on same fields...
raghavan20 wrote:Although using a full text index might help you a lot, you can simultaneously run this to find record/s which may have these characters.
Do not worry, this would not throw up an error, I have one similar in my db.
select `idsuchmaschine_produkte`,`produktname`
from `suchmaschine_produkte`
where
match(`produktname`)
against ('! @ # $ % ^ & \* \( \) \~ \\ / \, \. ? { } [ ] = -' in boolean mode);
Thanks for your help. Unfortunately none of the above characters can be found. The following message is displayed: Your SQL query has been executed successfully (Query took 44.3513 sec)
This problem is really very strange. I don't know where to start off, what I actually have to look for.
Thanks for your help. Unfortunately none of the above characters can be found. The following message is displayed: Your SQL query has been executed successfully (Query took 44.3513 sec)
44 seconds that seems to be longer. Have you set up your new full text indexes?
AKA Panama Jack wrote:Also, what version of Mysql are you using and what the speed of the database server and is the database server shared with other sites?
MySQL 4.1.12-standard.
Thanks all for your help. I deleted the index on produktname and recreated the index. That was enough to get my MySQL query running again. There probably was some character corrupting the index. But what exactly was the problem is still a mystery to me.
Probably just a fluke with the myIsam storage engine, I've never heard of a character corrupting the index.. at least not in the sense you described (you could cause mysql to not be able to load the database but you said it was loading just running slow). Another thing I would suggesting is running a cronjob to optimize your tables during off-peak hours, I've had slow queries that would have gone for hours that were caused by overhead due to large numbers of records being deleted and inserted back into the table several times in a row.