Running 4.1.22-standard, I have two simple MyISAM tables:
Table: temp_del_ids
Create Table: CREATE TABLE `temp_del_ids` (
`cust_id` int(11) NOT NULL default '0',
PRIMARY KEY (`cust_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
and
Table: cust_campaigns
Create Table: CREATE TABLE `cust_campaigns` (
`cust_camp_id` int(11) NOT NULL auto_increment,
`customer_id` int(11) NOT NULL default '0',
`campaign_date` date NOT NULL default '0000-00-00',
`campaign_name` char(255) default NULL,
PRIMARY KEY (`cust_camp_id`),
KEY `customer_id` (`customer_id`),
KEY `campaign_date` (`campaign_date`),
KEY `campaign_name` (`campaign_name`)
) ENGINE=MyISAM AUTO_INCREMENT=1415388 DEFAULT CHARSET=utf8
`temp_del_ids` has 81 records; `cust_campaigns` has 1052796. Here's my
puzzlement. I checked out two queries, and I don't understand why one of
them is using an index only on my small table, and the other is using
indexes on both.
============================
giiexpress.com >explain select * from cust_campaigns join temp_del_ids
-> on temp_del_ids.cust_id = cust_campaigns.customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: temp_del_ids
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 81
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cust_campaigns
type: ref
possible_keys: customer_id
key: customer_id
key_len: 4
ref: giiexpr_customers.temp_del_ids.cust_id
rows: 3
Extra:
=======================
giiexpress.com >explain
-> select cust_campaigns.customer_id from
-> cust_campaigns join temp_del_ids
-> on temp_del_ids.cust_id = cust_campaigns.customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: temp_del_ids
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 81
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cust_campaigns
type: ref
possible_keys: customer_id
key: customer_id
key_len: 4
ref: giiexpr_customers.temp_del_ids.cust_id
rows: 3
Extra: Using index
======================
Am I right to be surprised, or am I just misinterpreting what's going on?
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
