I'm running MySQL 4.1.22-standard Community on CentOS. My problem is a query
that is using about 800mb for what I assume is a temporary sort file, and
I'm hoping that I can do something about it.
The purpose of this query is to populate an unnormalized table with data
from several other tables. I do this to make it easier for MS Access users
to use simple filters to see subsets of the data without having to construct
queries that have to be done as pass-through SQL.
This post is going to be long, so that you can see exactly what is going on.
I don't want to leave anything out that might be critical. Sorry...
Here are the tables involved:
Table: customers
Create Table: CREATE TABLE `customers` (
`customer_id` int(11) NOT NULL auto_increment,
`priority` int(3) NOT NULL default '0',
`account_id` int(2) NOT NULL default '0',
`sal` varchar(50) NOT NULL default '',
`first_name` varchar(125) NOT NULL default '',
`last_name` varchar(125) NOT NULL default '',
`company` varchar(255) NOT NULL default '',
`dept` varchar(255) NOT NULL default '',
`position` varchar(255) NOT NULL default '',
`address_1` varchar(255) NOT NULL default '',
`address_2` varchar(255) NOT NULL default '',
`address_3` varchar(255) NOT NULL default '',
`zip` varchar(15) NOT NULL default '',
`country` varchar(50) NOT NULL default '',
`phone` varchar(255) NOT NULL default '',
`fax` varchar(50) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`entry_date` datetime default NULL,
`follow_up` date default NULL,
`action` varchar(50) NOT NULL default '',
`stage_id` int(11) default '0',
`status` varchar(50) NOT NULL default '',
`exp_price` decimal(8,2) default '0.00',
`input_source` varchar(255) NOT NULL default '',
`input_date` date default NULL,
`interest_category` varchar(255) NOT NULL default '',
`interest_subcategory` varchar(255) NOT NULL default '',
`interest_keyword` varchar(255) NOT NULL default '',
`db_entry` tinyint(3) NOT NULL default,
`hidden` tinyint(1) NOT NULL default '0',
`email_status` set('Y','N') NOT NULL default 'Y',
`dm_status` set('Y','N') NOT NULL default 'Y',
`num_products_purchased` smallint(6) default NULL,
`key_account` set('Y','N') NOT NULL default 'N',
`phone2` varchar(255) NOT NULL default '',
PRIMARY KEY (`customer_id`),
KEY `account_id` (`account_id`),
KEY `priority_id` (`priority`),
KEY `stage_id` (`stage_id`),
KEY `email` (`email`),
KEY `last_name_index` (`last_name`)
) ENGINE=MyISAM AUTO_INCREMENT=73717 DEFAULT CHARSET=utf8
***********************************************
Table: stage
Create Table: CREATE TABLE `stage` (
`stage_id` int(11) NOT NULL auto_increment,
`stage_name` varchar(15) default NULL,
PRIMARY KEY (`stage_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
******************************************************
Table: cust_topics
Create Table: CREATE TABLE `cust_topics` (
`cust_topic_id` int(11) NOT NULL auto_increment,
`topic_code` varchar(15) NOT NULL default '',
`customer_id` int(11) NOT NULL default '0',
PRIMARY KEY (`cust_topic_id`),
KEY `topic_id` (`customer_id`),
KEY `topic_code` (`topic_code`)
) ENGINE=MyISAM AUTO_INCREMENT=143201 DEFAULT CHARSET=utf8
******************************************************
Table: account
Create Table: CREATE TABLE `account` (
`account_id` int(11) NOT NULL auto_increment,
`account_name` char(2) character set latin1 default NULL,
`real_name` varchar(30) NOT NULL default '' COMMENT 'Real name associated
with account',
PRIMARY KEY (`account_id`),
UNIQUE KEY `account_name` (`account_name`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
******************************************************
Table: consolidated_customer_data
Create Table: CREATE TABLE `consolidated_customer_data` (
`customer_id` int(11) NOT NULL default '0',
`acct_name` varchar(6) default NULL,
`email` varchar(60) default NULL,
`email_status` set('Y','N') NOT NULL default 'Y',
`dm_status` set('Y','N') NOT NULL default '',
`status` varchar(50) NOT NULL default '""',
`last_name` varchar(125) default NULL,
`first_name` varchar(125) default NULL,
`sal` varchar(50) default NULL,
`company` varchar(255) default NULL,
`address_1` varchar(255) default NULL,
`address_2` varchar(255) default NULL,
`address_3` varchar(255) default NULL,
`country` varchar(50) default NULL,
`zip` varchar(15) default NULL,
`input_source` varchar(255) default NULL,
`interest_category` varchar(255) NOT NULL default '""',
`interest_subcategory` varchar(255) NOT NULL default '""',
`topic_list` text,
`stage` varchar(255) NOT NULL default '""',
PRIMARY KEY (`customer_id`),
KEY `acct_name` (`acct_name`),
KEY `email` (`email`),
KEY `last_name` (`last_name`),
KEY `company` (`company`),
KEY `country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
***********************
Before putting the data into the table `consolidated_customer_data`, I
TRUNCATE it. Then I use INSERT INTO consolidated_customer_data SELECT to
populate the table. I also use
SET SESSION group_concat_max_len = 10000;
Because the length of the topic_list can get quite large.
Now, here's an EXPLAIN of the SELECT query:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stage
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 9
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: customers
type: ref
possible_keys: account_id,stage_id
key: stage_id
key_len: 5
ref: giiexpr_customers.stage.stage_id
rows: 4535
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: account
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: giiexpr_customers.customers.account_id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: cust_topics
type: ref
possible_keys: topic_id
key: topic_id
key_len: 4
ref: giiexpr_customers.customers.customer_id
rows: 4
Extra:
=================
Here are the number of rows in each table:
Rows in stage: 9
Rows in customers: 46764
Rows in account: 14
Rows in cust_topics: 94994
Not surprisingly, the largest tables (in terms of disk space) are customers,
followed consolidated_customer_data:
22K customers.frm
8.1M customers.MYD
4.7M customers.MYI
18K consolidated_customer_data.frm
6.3M consolidated_customer_data.MYD
3.3M consolidated_customer_data.MYI
So, after all of that, why am I using 800+ megabytes in my /tmp?
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/[EMAIL PROTECTED]
