RE: Performance Tuning - Table Joins
I wonder how somebody can approve such a query and tables. 1) There is no indexes, thats the main reason for the time and cpu consuming. At least, the fields used in the joins MUST be indexed. 2) varchar(255) on ALL fields? That's unlikely, in-cre-di-ble. Right type for the right data, int for numbers, float for money, char for fixed string, and certainly NOT 255 for lenght! If its necesary such amount of characters, TEXT or BLOB must be used, but only if it is necesary. CREATE TABLE `memupdate` ( `MemID` int(10) NOT NULL default 0, `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 '', primary key (memID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblcc` ( `record_status` varchar(255) NOT NULL default '', `grp0` varchar(255) NOT NULL default '', `member` int(10) NOT NULL default 0, `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` date NOT NULL default '', index memindex (member), index a_year (appl_year), index d_taken (date_taken) ENGINE=InnoDB DEFAULT CHARSET=latin1 Im assuming memupdate has unique values for memID, thus, the primary key over memID - primary key (memID) - index memindex (member) index for the where -index a_year (appl_year) index fot order -index d_taken (date_taken) For a performance issues, is better that fields in a join be of the same type and lenght - `MemID` int(10) NOT NULL default 0, - `member` int(10) NOT NULL default 0, data types -`appl_year` int(4) NOT NULL default 0, -`date_taken` date NOT NULL default '', Who made that tables and query really REALLY needs to RTFM :) http://dev.mysql.com/doc/mysql/en/select-speed.html ___ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Tuning - Table Joins
j llarens wrote: 2) varchar(255) on ALL fields? That's unlikely, in-cre-di-ble. Right type for the right data, int for numbers, float for money, char for fixed string, and certainly NOT 255 for lenght! If its necesary such amount of characters, TEXT or BLOB must be used, but only if it is necesary. - I'd recommend not using float for money. You get rounding errors. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Tuning - Table Joins
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
Re: Performance Tuning - Table Joins
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]
Re: Performance Tuning - Table Joins
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`
Re: Performance Tuning - Table Joins
At 12:22 PM 4/4/2005, you 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. 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? Is there any more information you need from me to answer this question? 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 Jason, Try running Analyze Table on each of the tables. This will rebalance the index and get rid of deleted space. Returning one row from a 3 table join should take only ms if you're using indexes properly. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance Tuning - Table Joins
Your not indexing properly this should be a blink of a search. Or your looping your loops when you search. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Monday, April 04, 2005 1:30 PM To: MySQL list Subject: Re: Performance Tuning - Table Joins At 12:22 PM 4/4/2005, you 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. 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? Is there any more information you need from me to answer this question? 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 Jason, Try running Analyze Table on each of the tables. This will rebalance the index and get rid of deleted space. Returning one row from a 3 table join should take only ms if you're using indexes properly. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
