integer (int) type column slow response but VARCHAR type much fast

[imranlink]

Hi,

I am getting very late response from int type columns. If i use as "VARCHAR" type, then i get very fast and reply.
Can you please guide me how can i optimize my table with integer colums types?

integer (int) type column slow response but VARCHAR type much fast response.

Table Structure

CREATE TABLE `inbox_header` (
  `nr` int(11) NOT NULL auto_increment,
  `date_iso` date default NULL,
  `from_name` tinytext,
  `subject_text` text,
  `size` smallint(6) default NULL,
  `status` tinyint(3) unsigned default '0',
  `user_id` int(10) unsigned NOT NULL default '44',
  `read_status` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`nr`),
  FULLTEXT KEY `from_name` (`from_name`),
  FULLTEXT KEY `subject_text` (`subject_text`)
) TYPE=MyISAM;

Filled with 15,500 Records and 2870 KB size.

SELECT HIGH_PRIORITY count(nr) as total_emails
				 FROM inbox_header WHERE user_id='1005' AND `status`='1' AND `read_status`='0'

Above Query taken 0.11 Seconds

After Modifiying the table structure

ALTER TABLE `inbox_header` CHANGE `user_id` `user_id` VARCHAR(10)  DEFAULT "44" NOT NULL;
ALTER TABLE `inbox_header` CHANGE `status` `status` VARCHAR(3)  DEFAULT "0";
ALTER TABLE `inbox_header` CHANGE `read_status` `read_status` VARCHAR(3)  DEFAULT "0" NOT NULL;

Above Query taken 0.06 Seconds

How can i get 0.06 Seconds response with INteger Type?

Regards,
Imran Khalid (Senior Web Developer)
[e-mail]

[nemonoman]

Perhaps it's because you are mixing up query and column datatypes:

With integer columns, your query:

SELECT HIGH_PRIORITY count(nr) as total_emails
FROM inbox_header WHERE user_id='1005' AND status='1' AND read_status='0'

asks the db to translate the integer to a string and then compare

But with varchar type columns there's no need to translate.

To improve the integer column search, query integer values, not strings:

SELECT HIGH_PRIORITY count(nr) as total_emails
FROM inbox_header WHERE user_id=1005 AND status=1 AND read_status=0

(note no quotes)

[imranlink]

Thanks

After removing the quotes from Integer (int) type coulms, query speed become much fast. It is now responding in 0.05 Seconds

Regards,
Imran Khalid (PHP Developer)