Corrupted MySQL index?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Corrupted MySQL index?

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

total stab in the dark here, but full text search?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

aren't keys and index the same? visionmaster has a key on produktname .. ;)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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);
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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?
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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?
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Corrupted MySQL index?

Post by josh »

He already tried that
visionmaster wrote:A "REPAIR TABLE suchmachine_produkte" actually does not help at all.
Post Reply