MySQL Forums
Forum List  »  Optimizer & Parser

performance issue on simple table access via primary key
Posted by: Maggie Pong
Date: December 11, 2007 12:42AM

Hi,

I just came across that doc and found that very useful.

http://forge.mysql.com/wiki/MySQL_Internals_Optimizer

I have a question regarding the constant table and the optimization that it took to figure out if it is 'impossible WHERE'. Will that process potentially take a long time?

The reason is that we are running 4.0.26 and we have the following table:

CREATE TABLE `yficonf` (
`keycol` tinyblob NOT NULL,
`valcol` blob NOT NULL,
`update_time` bigint(20) unsigned NOT NULL default '0',
`expire_time` bigint(20) unsigned NOT NULL default '0',
`update_source` varchar(32) NOT NULL default '',
`flags` tinyint(4) NOT NULL default '0',
`edit_flag` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`keycol`(255))
) TYPE=MyISAM
;

mysql> Show table status like 'yficonf';
Name: yficonf
Type: MyISAM
Row_format: Dynamic
Rows: 19068
Avg_row_length: 59
Data_length: 1127672
Max_data_length: 4294967295
Index_length: 807936
Data_free: 0
Auto_increment: NULL
Create_time: 2007-09-16 16:13:34
Update_time: 2007-12-04 12:03:48
Check_time: 2007-12-09 01:11:13

As you can see, the table is pretty small. However, we are getting slow query on the following simple query:

# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT valcol, update_time, expire_time, update_source, flags FROM yficonf WHERE keycol = 'abcd';

After examining our slow log, ~ 97% of the time when this query is reported, the constant value is not a valid keycol value (negative hits).

According to the wiki, during the query opt stage, mysql already accessed the primary key and determined that the constant table has no value. I am not sure where mysql could have spend full 2 sec or up to 7 sec in some cases to examine such a small table's primary key.

At this point, I don't even know where to start to look for problem.
My thoughts:
- It doesn't seem to be a caching paged out issue since I can see the same slow query occurs several time within a sec.
- If it is a caching issue or mysql thread starving such that query is waiting to be executed, I should see same problem with positive key hits.
- Even if it needs to access disk for the index. It should be pretty fast since the index should be pretty small. We have good hardware (RAID 10 and 15k disks)
- Could it be because keycol is declared as tinyblob?

Really appreciate this if anyone can shed some light in where I can start looking at resolving this issue.

Thanks.

Maggie

Options: ReplyQuote


Subject
Views
Written By
Posted
performance issue on simple table access via primary key
3648
December 11, 2007 12:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.