Page 1 of 1
Corrupted MySQL index?
Posted: Thu Jan 26, 2006 4:18 pm
by visionmaster
Hello together,
Hello together,
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?
Code: Select all
CREATE TABLE `suchmaschine_produkte` (
`idsuchmaschine_produkte` int(10) unsigned NOT NULL auto_increment,
`produktname` varchar(120) collate latin1_german1_ci NOT NULL default '',
`verwendung` varchar(100) collate latin1_german1_ci NOT NULL default '',
`eigenschaft` varchar(100) collate latin1_german1_ci NOT NULL default '',
`id_firmendaten` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`idsuchmaschine_produkte`),
KEY `id_firmendatenIndex` (`id_firmendaten`),
KEY `productnameIndex` (`produktname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
AUTO_INCREMENT=3160091 ;
Thanks for your help!
Posted: Thu Jan 26, 2006 5:00 pm
by feyd
total stab in the dark here, but full text search?
Posted: Thu Jan 26, 2006 5:06 pm
by pickle
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.
MySQL manual entry on FULLTEXT searching
Zend tutorial on setting up a full text search
Posted: Thu Jan 26, 2006 5:20 pm
by feyd
aren't keys and index the same? visionmaster has a key on produktname ..

Posted: Thu Jan 26, 2006 5:27 pm
by raghavan20
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.
Code: Select all
select `idsuchmaschine_produkte`,`produktname`
from `suchmaschine_produkte`
where
match(`produktname`)
against ('! @ # $ % ^ & \* \( \) \~ \\ / \, \. ? { } [ ] = -' in boolean mode);
Posted: Thu Jan 26, 2006 5:28 pm
by raghavan20
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...
Posted: Fri Jan 27, 2006 12:37 am
by visionmaster
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.
Code: Select all
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.
Regards,
visionmaster
Posted: Fri Jan 27, 2006 3:57 am
by raghavan20
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?
Posted: Fri Jan 27, 2006 4:32 pm
by AKA Panama Jack
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?
Posted: Fri Jan 27, 2006 5:08 pm
by visionmaster
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.
Regards,
visionmaster
Posted: Sat Jan 28, 2006 12:13 am
by josh
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.
Posted: Sat Jan 28, 2006 12:23 am
by AKA Panama Jack
You could also enter this query...
REPAIR TABLE {tablename}
That can usually fix any corrupted information in the table and indexes.
Re: Corrupted MySQL index?
Posted: Sat Jan 28, 2006 2:20 am
by josh
He already tried that
visionmaster wrote:A "REPAIR TABLE suchmachine_produkte" actually does not help at all.