My responses blended in.... Jason Johnson <[EMAIL PROTECTED]> wrote on 04/04/2005 02:19:12 PM:
> 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): This has the reek of MS Access all over it (gag, cough, sputter...) > > 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` ( <snip> > CREATE TABLE `tblcd` ( <snip> > CREATE TABLE `memupdate` ( <snip> > CREATE TABLE `tblcc` ( <snip> (retch) > Tables that aren't used in this particular query, but are involved in > the process (tblcs, tblme): > > CREATE TABLE `tblcs` ( <snip> > CREATE TABLE `tblme` ( <big snip> (retch - just kidding :-D ) Don't let everyone put you down. I have also used tables just like the ones you have (all fields are character-based each a particularly large size). However, I only used them as temporary storage locations while importing text files as data. Please take the time, and it's going to take a day or so, to create some actual (production-ready) data tables so that each field is the appropriate size and type for the data it contains. You can keep the tables you have so that you can still have somewhere to put your incoming data but please abandon those tables except to help you process incoming data. Next you need to decide which columns (in your production tables) must contain unique values and assign those to primary keys. After that, decide which columns (or combinations of columns) participate in the WHERE, GROUP BY, and ORDER BY clauses of your queries most often and create indexes for them. Once you have completed all of this groundwork, your database performance issues *should* disappear. As a temporary fix, you might be able to add some indexes to your existing tables but that's like putting makeup on a rock and calling it pretty. It could get you through the short term but it's only a patch. It's your design that's broken. Fix that and you will be much better off. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
