Re: Performance Tuning - Table Joins

[Jason Johnson]

The premise of the query is to return required continuing education  
hours for the entire membership of the organization. Limited to one  
member when providing a membership ID.

The query is a little bulky, and fortunately I cannot take credit for  
its design, but here goes (keep in mind that some of the values used in  
the where clauses are dynamically inserted):

select memupdate.MemID, memupdate.Admit, memupdate.Birth,  
memupdate.Salut, memupdate.First, memupdate.Middle, memupdate.Last,  
memupdate.Company, memupdate.Add1, memupdate.Add2, memupdate.City,  
memupdate.State, memupdate.Zip, tblcc.grp0, tblcc.appl_year,  
tblcc.date_taken, tblcc.sponsor, tblcc.course, tblcc.appl_hrs_04,  
tblcc.appl_hrs_03, tblcc.appl_hrs_02, tblcc.appl_hrs_01, tblcd.Type,  
tblcd.title, tblsp.name AS SponsorName from ( ( ( memupdate inner join  
tblcc on memupdate.MemID = tblcc.member ) inner join tblcd on (  
tblcc.course = tblcd.course ) and ( tblcc.sponsor = tblcd.sponsor ) and  
( tblcc.course_grp0 = tblcd.grp0 ) and ( tblcc.dater = tblcd.dater )  
and ( tblcc.sub = tblcd.sub ) ) inner join tblsp on ( tblcd.grp0 =  
tblsp.grp0 ) and ( tblcd.sponsor = tblsp.sponsor ) ) where  
memupdate.MemID = 300 and ( ( tblcc.appl_year ) >= 2004 and (  
tblcc.appl_year ) <= 2005 ) order by tblcc.date_taken;

Output of explain (note, 4 tables instead of the 3 I had mentioned):
| id | select_type | table     | type | possible_keys | key  | key_len  
| ref  | rows  | Extra                           |
+----+-------------+-----------+------+---------------+------+--------- 
+------+-------+---------------------------------+
|  1 | SIMPLE      | tblsp     | ALL  | NULL          | NULL |    NULL  
| NULL |  4082 | Using temporary; Using filesort |
|  1 | SIMPLE      | tblcd     | ALL  | NULL          | NULL |    NULL  
| NULL | 11563 | Using where                     |
|  1 | SIMPLE      | memupdate | ALL  | NULL          | NULL |    NULL  
| NULL | 44059 | Using where                     |
|  1 | SIMPLE      | tblcc     | ALL  | NULL          | NULL |    NULL  
| NULL | 84567 | Using where                     |

I must point out that when you see this, it may cause an adverse  
physical reaction which may include vomiting and/or heaving. The data  
is coming in from another source and unfortunately has to be typed this  
way. I'm not in control of how I get it, though I can lay the smack  
down on how it's handed off if need be. Also, to my surprise, these  
tables have been created using InnoDB, I apologize for misleading you  
in my first message.

CREATE TABLE `tblsp` (
  `record_status` varchar(255) NOT NULL default '',
  `grp0` varchar(255) NOT NULL default '',
  `sponsor` varchar(255) NOT NULL default '',
  `grp1` varchar(255) NOT NULL default '',
  `date_maint` varchar(255) NOT NULL default '',
  `who_maint` varchar(255) NOT NULL default '',
  `seq_maint` varchar(255) NOT NULL default '',
  `name` varchar(255) NOT NULL default '',
  `contact` varchar(255) NOT NULL default '',
  `addr1` varchar(255) NOT NULL default '',
  `addr2` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `st_prov` varchar(255) NOT NULL default '',
  `zip_code` varchar(255) NOT NULL default '',
  `country` varchar(255) NOT NULL default '',
  `phone_area` varchar(255) NOT NULL default '',
  `phone_exc` varchar(255) NOT NULL default '',
  `phone_nbr` varchar(255) NOT NULL default '',
  `phone_ext` varchar(255) NOT NULL default '',
  `fax_area` varchar(255) NOT NULL default '',
  `fax_exc` varchar(255) NOT NULL default '',
  `fax_nbr` varchar(255) NOT NULL default '',
  `fax_ext` varchar(255) NOT NULL default '',
  `accredited` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `date_opened` varchar(255) NOT NULL default '',
  `comment_flag` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `tblcd` (
  `record_status` varchar(255) NOT NULL default '',
  `grp0` varchar(255) NOT NULL default '',
  `sponsor` varchar(255) NOT NULL default '',
  `course` varchar(255) NOT NULL default '',
  `dater` varchar(255) NOT NULL default '',
  `sub` varchar(255) NOT NULL default '',
  `date_opened` varchar(255) NOT NULL default '',
  `date_maint` varchar(255) NOT NULL default '',
  `who_maint` varchar(255) NOT NULL default '',
  `seq_maint` varchar(255) NOT NULL default '',
  `end_date` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `sp_require` varchar(255) NOT NULL default '',
  `req_hrs_01` varchar(255) NOT NULL default '',
  `req_hrs_02` varchar(255) NOT NULL default '',
  `req_hrs_03` varchar(255) NOT NULL default '',
  `req_hrs_04` varchar(255) NOT NULL default '',
  `req_hrs_05` varchar(255) NOT NULL default '',
  `req_hrs_06` varchar(255) NOT NULL default '',
  `facility` varchar(255) NOT NULL default '',
  `addr1` varchar(255) NOT NULL default '',
  `addr2` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `st_prov` varchar(255) NOT NULL default '',
  `zip_code` varchar(255) NOT NULL default '',
  `country` varchar(255) NOT NULL default '',
  `phone_area` varchar(255) NOT NULL default '',
  `phone_exc` varchar(255) NOT NULL default '',
  `phone_nbr` varchar(255) NOT NULL default '',
  `phone_ext` varchar(255) NOT NULL default '',
  `fax_area` varchar(255) NOT NULL default '',
  `fax_exc` varchar(255) NOT NULL default '',
  `fax_nbr` varchar(255) NOT NULL default '',
  `fax_ext` varchar(255) NOT NULL default '',
  `approved` varchar(255) NOT NULL default '',
  `fee_paid` varchar(255) NOT NULL default '',
  `phone_hookup` varchar(255) NOT NULL default '',
  `video_hookup` varchar(255) NOT NULL default '',
  `cle_coor` varchar(255) NOT NULL default '',
  `facility_rep` varchar(255) NOT NULL default '',
  `check_in` varchar(255) NOT NULL default '',
  `reg_fee` varchar(255) NOT NULL default '',
  `early_reg_fee` varchar(255) NOT NULL default '',
  `registered` varchar(255) NOT NULL default '',
  `attended` varchar(255) NOT NULL default '',
  `comment_flag` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `memupdate` (
  `MemID` varchar(255) NOT NULL default '',
  `Status` varchar(255) NOT NULL default '',
  `Salut` varchar(255) NOT NULL default '',
  `First` varchar(255) NOT NULL default '',
  `Middle` varchar(255) NOT NULL default '',
  `Last` varchar(255) NOT NULL default '',
  `Suffix` varchar(255) NOT NULL default '',
  `SSN` varchar(255) NOT NULL default '',
  `Sex` varchar(255) NOT NULL default '',
  `Admit` varchar(255) NOT NULL default '',
  `Birth` varchar(255) NOT NULL default '',
  `Exam` varchar(255) NOT NULL default '',
  `School` varchar(255) NOT NULL default '',
  `Company` varchar(255) NOT NULL default '',
  `Add1` varchar(255) NOT NULL default '',
  `Add2` varchar(255) NOT NULL default '',
  `City` varchar(255) NOT NULL default '',
  `State` varchar(255) NOT NULL default '',
  `Zip` varchar(255) NOT NULL default '',
  `Country` varchar(255) NOT NULL default '',
  `Phone` varchar(255) NOT NULL default '',
  `Fax` varchar(255) NOT NULL default '',
  `Circuit` varchar(255) NOT NULL default '',
  `County` varchar(255) NOT NULL default '',
  `Year` varchar(255) NOT NULL default '',
  `Email` varchar(255) NOT NULL default '',
  `LastUpdated` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `tblcc` (
  `record_status` varchar(255) NOT NULL default '',
  `grp0` varchar(255) NOT NULL default '',
  `member` varchar(255) NOT NULL default '',
  `appl_year` varchar(255) NOT NULL default '',
  `course_grp0` varchar(255) NOT NULL default '',
  `sponsor` varchar(255) NOT NULL default '',
  `course` varchar(255) NOT NULL default '',
  `dater` varchar(255) NOT NULL default '',
  `sub` varchar(255) NOT NULL default '',
  `date_maint` varchar(255) NOT NULL default '',
  `who_maint` varchar(255) NOT NULL default '',
  `seq_maint` varchar(255) NOT NULL default '',
  `sp_require` varchar(255) NOT NULL default '',
  `appl_hrs_01` varchar(255) NOT NULL default '',
  `appl_hrs_02` varchar(255) NOT NULL default '',
  `appl_hrs_03` varchar(255) NOT NULL default '',
  `appl_hrs_04` varchar(255) NOT NULL default '',
  `appl_hrs_05` varchar(255) NOT NULL default '',
  `appl_hrs_06` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `fee` varchar(255) NOT NULL default '',
  `comment_flag` varchar(255) NOT NULL default '',
  `paid` varchar(255) NOT NULL default '',
  `fee_override` varchar(255) NOT NULL default '',
  `charge` varchar(255) NOT NULL default '',
  `attend_type` varchar(255) NOT NULL default '',
  `instructor` varchar(255) NOT NULL default '',
  `date_taken` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Tables that aren't used in this particular query, but are involved in  
the process (tblcs, tblme):

CREATE TABLE `tblcs` (
  `record_status` varchar(255) NOT NULL default '',
  `grp0` varchar(255) NOT NULL default '',
  `member` varchar(255) NOT NULL default '',
  `grp1` varchar(255) NOT NULL default '',
  `date_maint` varchar(255) NOT NULL default '',
  `who_maint` varchar(255) NOT NULL default '',
  `seq_maint` varchar(255) NOT NULL default '',
  `year` varchar(255) NOT NULL default '',
  `taken_hrs_01` varchar(255) NOT NULL default '',
  `taken_hrs_02` varchar(255) NOT NULL default '',
  `taken_hrs_03` varchar(255) NOT NULL default '',
  `taken_hrs_04` varchar(255) NOT NULL default '',
  `taken_hrs_05` varchar(255) NOT NULL default '',
  `taken_hrs_06` varchar(255) NOT NULL default '',
  `appl_hrs_01` varchar(255) NOT NULL default '',
  `appl_hrs_02` varchar(255) NOT NULL default '',
  `appl_hrs_03` varchar(255) NOT NULL default '',
  `appl_hrs_04` varchar(255) NOT NULL default '',
  `appl_hrs_05` varchar(255) NOT NULL default '',
  `appl_hrs_06` varchar(255) NOT NULL default '',
  `appl_back_01` varchar(255) NOT NULL default '',
  `appl_back_02` varchar(255) NOT NULL default '',
  `appl_back_03` varchar(255) NOT NULL default '',
  `appl_back_04` varchar(255) NOT NULL default '',
  `appl_back_05` varchar(255) NOT NULL default '',
  `appl_back_06` varchar(255) NOT NULL default '',
  `appl_fut_01` varchar(255) NOT NULL default '',
  `appl_fut_02` varchar(255) NOT NULL default '',
  `appl_fut_03` varchar(255) NOT NULL default '',
  `appl_fut_04` varchar(255) NOT NULL default '',
  `appl_fut_05` varchar(255) NOT NULL default '',
  `appl_fut_06` varchar(255) NOT NULL default '',
  `fut_hrs_01` varchar(255) NOT NULL default '',
  `fut_hrs_02` varchar(255) NOT NULL default '',
  `fut_hrs_03` varchar(255) NOT NULL default '',
  `fut_hrs_04` varchar(255) NOT NULL default '',
  `fut_hrs_05` varchar(255) NOT NULL default '',
  `fut_hrs_06` varchar(255) NOT NULL default '',
  `back_hrs_01` varchar(255) NOT NULL default '',
  `back_hrs_02` varchar(255) NOT NULL default '',
  `back_hrs_03` varchar(255) NOT NULL default '',
  `back_hrs_04` varchar(255) NOT NULL default '',
  `back_hrs_05` varchar(255) NOT NULL default '',
  `back_hrs_06` varchar(255) NOT NULL default '',
  `need_hrs_01` varchar(255) NOT NULL default '',
  `need_hrs_02` varchar(255) NOT NULL default '',
  `need_hrs_03` varchar(255) NOT NULL default '',
  `need_hrs_04` varchar(255) NOT NULL default '',
  `need_hrs_05` varchar(255) NOT NULL default '',
  `need_hrs_06` varchar(255) NOT NULL default '',
  `in_house` varchar(255) NOT NULL default '',
  `law_school` varchar(255) NOT NULL default '',
  `cle_exempt` varchar(255) NOT NULL default '',
  `affidavit` varchar(255) NOT NULL default '',
  `fee_due` varchar(255) NOT NULL default '',
  `fee_override` varchar(255) NOT NULL default '',
  `paid` varchar(255) NOT NULL default '',
  `pr_yr_status` varchar(255) NOT NULL default '',
  `cle_status` varchar(255) NOT NULL default '',
  `comment_flag` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `tblme` (
  `record_status` varchar(255) NOT NULL default '',
  `grp0` varchar(255) NOT NULL default '',
  `member` varchar(255) NOT NULL default '',
  `grp1` varchar(255) NOT NULL default '',
  `date_opened` varchar(255) NOT NULL default '',
  `date_maint` varchar(255) NOT NULL default '',
  `who_maint` varchar(255) NOT NULL default '',
  `seq_maint` varchar(255) NOT NULL default '',
  `date_name_addr` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `sex` varchar(255) NOT NULL default '',
  `salutation` varchar(255) NOT NULL default '',
  `name_first` varchar(255) NOT NULL default '',
  `name_middle` varchar(255) NOT NULL default '',
  `name_last` varchar(255) NOT NULL default '',
  `name_suffix` varchar(255) NOT NULL default '',
  `appeal` varchar(255) NOT NULL default '',
  `ssn` varchar(255) NOT NULL default '',
  `company` varchar(255) NOT NULL default '',
  `addr1` varchar(255) NOT NULL default '',
  `addr2` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `st_prov` varchar(255) NOT NULL default '',
  `zip_code` varchar(255) NOT NULL default '',
  `country` varchar(255) NOT NULL default '',
  `phone_area` varchar(255) NOT NULL default '',
  `phone_exc` varchar(255) NOT NULL default '',
  `phone_nbr` varchar(255) NOT NULL default '',
  `phone_ext` varchar(255) NOT NULL default '',
  `fax_area` varchar(255) NOT NULL default '',
  `fax_exc` varchar(255) NOT NULL default '',
  `fax_nbr` varchar(255) NOT NULL default '',
  `fax_ext` varchar(255) NOT NULL default '',
  `admitted` varchar(255) NOT NULL default '',
  `birth` varchar(255) NOT NULL default '',
  `circuit` varchar(255) NOT NULL default '',
  `ballot_cir` varchar(255) NOT NULL default '',
  `activity_01` varchar(255) NOT NULL default '',
  `activity_02` varchar(255) NOT NULL default '',
  `activity_03` varchar(255) NOT NULL default '',
  `activity_04` varchar(255) NOT NULL default '',
  `activity_05` varchar(255) NOT NULL default '',
  `law_school` varchar(255) NOT NULL default '',
  `exam_date` varchar(255) NOT NULL default '',
  `req_exemp_01` varchar(255) NOT NULL default '',
  `req_status_01` varchar(255) NOT NULL default '',
  `req_year_01` varchar(255) NOT NULL default '',
  `req_exemp_02` varchar(255) NOT NULL default '',
  `req_status_02` varchar(255) NOT NULL default '',
  `req_year_02` varchar(255) NOT NULL default '',
  `req_exemp_03` varchar(255) NOT NULL default '',
  `req_status_03` varchar(255) NOT NULL default '',
  `req_year_03` varchar(255) NOT NULL default '',
  `req_exemp_04` varchar(255) NOT NULL default '',
  `req_status_04` varchar(255) NOT NULL default '',
  `req_year_04` varchar(255) NOT NULL default '',
  `first_req_lgth` varchar(255) NOT NULL default '',
  `req_yrs_01` varchar(255) NOT NULL default '',
  `req_yrs_02` varchar(255) NOT NULL default '',
  `req_yrs_03` varchar(255) NOT NULL default '',
  `req_yrs_04` varchar(255) NOT NULL default '',
  `req_yrs_05` varchar(255) NOT NULL default '',
  `req_yrs_06` varchar(255) NOT NULL default '',
  `trial_aff` varchar(255) NOT NULL default '',
  `status` varchar(255) NOT NULL default '',
  `comment_flag` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
On Apr 4, 2005, at 1:55 PM, Michael Stassen wrote:
On Apr 4, 2005, at 1:22 PM, Jason Johnson wrote:
I have been struggling to maintain decent performance on a  
web/database server for a good 6 months now due to MySQL performance  
issues. I have decided that my best option at this point is to take  
it to the list, so in advance, I thank you all for taking a look.

There is no error messages that can be posted, so I will try and  
describe what's happening as best I can.

I am joining 3 tables in one query. I have had numerous people  
examine the queries and all have given their stamp of approval. What  
happens when I run it is MySQL takes the processor for a ride,  
spiking it to 100% until I restart mysqld.

The tables range from 50,000 to 85,000 records, and the join is only  
supposed to return 1 record.
With proper indexing, this should be quick.
My question to you is this: are there changes I can make to the  
configuration to improve performance? --or-- is data de-normalization  
my best option?
Neither.  At least, not until we make sure the query is correct and  
the tables are properly indexed.

Is there any more information you need from me to answer this  
question?
Yes.  Please provide the query, and the output of EXPLAIN on your  
query <http://dev.mysql.com/doc/mysql/en/explain.html>.  The output  
for each table of "SHOW CREATE TABLE tablename", or at least "SHOW  
INDEXES FROM tablename", would be useful.  A brief description of the  
point of the query, if it isn't obvious, would also help.

Current setup:
	2.4ghz Pentium 4, 1gb ram, 360gb 4-disc raid 5 array w/ 3ware  
chassis and card, fedora core 3 w/ all patches and updates, selinux  
-disabled-,  mysql 4.1.10a, MyISAM table format.

Again, thank you all in advance,
Jason
Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]