# Bad performance with simple join **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 24, 2008, 3:51pm UTC](https://forums.percona.com/t/bad-performance-with-simple-join/683 "2008-03-24T15:51:56Z")\ **Posts on this page:** 6\ **Page:** 1

**Author:** ![kenny01](https://avatars.discourse-cdn.com/v4/letter/k/c6cbf5/32.png) [@kenny01](https://forums.percona.com/u/kenny01)\ **Post date:** [March 24, 2008, 3:51pm UTC](https://forums.percona.com/t/bad-performance-with-simple-join/683/1 "2008-03-24T15:51:56Z")

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [March 25, 2008, 8:44am UTC](https://forums.percona.com/t/bad-performance-with-simple-join/683/2 "2008-03-25T08:44:11Z")

| [B]Quote:[/B] | | … 8 AMD dual core CPU … | Does that appear to the OS as 16 CPU's? MySQL executes a query in only _one_ thread. Which means that if you have a CPU intensive query it will only be able to use 100/16=6.25 percent cpu usage on a 16 way machine. But regardless of that it seems like a long time for that query so here are some thoughts: 1. Why is your “Date\_Time” a VARCHAR(20) and not a real DATETIME? Generally you should try to avoid joining on large fields so just by changing this to a DATETIME you will instead join on a 8 byte field instead of a 22 byte field as you have today. The difference is almost 1/3 in index size. 1. Why are you using SUM([B]DISTINCT/B)? Since I don’t really know your table structure I can’t really understand. But the SUM(DISTINCT()) combination sounds a bit strange, if you had been using COUNT(DISTINCT()) I could have understood it better since you are then counting the distinct values. But the SUM means that you are summarizing on a filtered value where if two mail\_transactions happen to have the same value you are filtering it away. Do you have duplicate entries in the tables that you want to get rid of? 1. Just to rule this out, do you have any IO load or swapping taking place during the execution of this query? BTW: I applauding your excellent post, a wealth of information which makes trying to figure out your problem so much easier. ) ---

**Author:** ![safari](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@safari](https://forums.percona.com/u/safari)\ **Post date:** [March 25, 2008, 10:54am UTC](https://forums.percona.com/t/bad-performance-with-simple-join/683/3 "2008-03-25T10:54:12Z")

My concern is about variables, - join\_buffer\_size = 1048571904 ~ 1000M - key\_buffer\_size = 314572800 ~ 300M - read\_buffer\_size = 524283904 ~ 500M - read\_rnd\_buffer\_size = 209711104 ~ 200M IMHO, join, read, and read\_rnd buffer size is too large; besides that key\_buffer\_size is small comparing to the MyISAM tables size. ---

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [March 26, 2008, 6:49pm UTC](https://forums.percona.com/t/bad-performance-with-simple-join/683/4 "2008-03-26T18:49:53Z")

1. Your Date\_Time string is in the right format. It’s just that the column is defined in the wrong format in the table. A string like ‘2008-10-01 23:55:00’ is the ISO standard format to represent a date and time. And this is the format that MySQL takes/returns when you insert/select times into/from a DATETIME column. So you can without any changes to the application make an alteration of the table so that this column is a DATETIME and the application will not know the difference. Plus you can get rid of the very ugly cast(date\_format()) stuff that you have in this query on this column. 1. Don’t use double to store decimal values. Use the NUMERIC type instead. In your table both the CPU and nr of transactions are created as double and double is per definition an approximation of an value. For example in your case the CPU load always seems to be two digits and one decimal, so type should be NUMERIC(3,1). that means the largest value can be 99.9 and the smallest value can be -99.9. The same goes for the transactions only this values seems to be 2 digits and 3 decimals so NUMERIC(5,3) could seem appropriate for this (unless there is a chance that it races above 99.999 transactions then a NUMERIC(6,3) is more appropriate. Here’s an example of how I defined the tables. CREATE TABLE `cluster2-transactions_per_second` ( `Date_Time` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’, `HostName` varchar(255) NOT NULL DEFAULT ‘’, `mail_transactions` decimal(6,3) DEFAULT NULL, `CPU_usage` decimal(3,1) DEFAULT NULL, KEY `c2_ix_datetime_trans` (`Date_Time`,`mail_transactions`), KEY `hostname_index` (`HostName`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 1. I recommend avoid using a subtraction “-” sign in a table name. That is what is forcing you to use back-ticks around the table names. If you want separation for readability stick to the underscore sign. ( I still used it in the posts here on this post since you have it in your database, but you should avoid it). 1. Now I understand why you had a SUM(DISTINCT…) but the underlying problem is a cross join. And that is solved by the query below in point 5. But the problem with your SUM(DISTINCT …) is that if you have these rows in your table: 2008-03-20 18:30:00,cluster2\_node1,52.902,14.22008-03-20 18:30:00,cluster2\_node2,53.312,15.42008-03-20 18:30:00,cluster2\_node3,55.872,15.12008-03-20 18:30:00,cluster2\_node4,53.312,15.02008-03-20 18:30:00,cluster2\_node5,44.333,14.9 Notice how row 2 and 4 just happen to have the same nr of transactions. Then the DISTINCT means that one of these rows will be filtered away and then you will only calculate the sum of the 4 remaining rows. And suddenly you have dropped 1/5 of the transactions. That is why I was questioning the SUM(DISTINCT …) in the first place. 1. I rewrote your query quite a lot because your query was performing a cross join since you had more than one row with the same Date\_Time value per table. Your DISTINCTs was covering this up but it forced the DBMS to do an enormous amount of unnecessary work since the temporary table would be so huge. My query is utilizing sub-selects and aliases and dropped the DISTINCT: SELECT ctime.Date\_Time as “Date\_Time”, ( select SUM(c2.`mail_transactions`) FROM `cluster2-transactions_per_second` c2 WHERE ctime.Date\_Time = c2.Date\_Time) AS cluster2\_mo\_attempts, (select SUM(c3.`mail_transactions`) FROM `cluster3-transactions_per_second` c3 WHERE ctime.Date\_Time = c3.Date\_Time) AS cluster3\_mo\_attempts, (select SUM(c4.`mail_transactions`) FROM `cluster4-transactions_per_second` c4 WHERE ctime.Date\_Time = c4.Date\_Time) AS cluster4\_mo\_attempts, (select SUM(c5.`mail_transactions`) FROM `cluster5-transactions_per_second` c5 WHERE ctime.Date\_Time = c5.Date\_Time) AS cluster5\_mo\_attempts, (select SUM(c6.`mail_transactions`) FROM `cluster6-transactions_per_second` c6 WHERE ctime.Date\_Time = c6.Date\_Time) AS cluster6\_mo\_attempts, (select SUM(c7.`mail_transactions`) FROM `cluster7-transactions_per_second` c7 WHERE ctime.Date\_Time = c7.Date\_Time) AS cluster7\_mo\_attemptsFROM (SELECT DISTINCT Date\_Time FROM `cluster2-transactions_per_second` c WHERE c.Date\_Time \< ‘2008-10-01 23:55:00’ AND c.Date\_Time \> ‘2008-03-19 23:55:00’ ) ctimeORDER BY ctime.Date\_Time; This query first creates a temp table ctime which only contains unique time values. Then it performs the subselects in the SELECT part matching the current unique time value and summarizes these 5 values. That way you will only have 5 records at a time for the subselects to summarize. 1. To tweak that little extre of speed you can add these combined index on all tables so that MySQL doesn’t have to read from the table at all to execute this query. ALTER TABLE `cluster2-transactions_per_second` ADD INDEX c2\_ix\_datetime\_trans(Date\_Time, mail\_transactions);ALTER TABLE `cluster3-transactions_per_second` ADD INDEX c3\_ix\_datetime\_trans(Date\_Time, mail\_transactions);ALTER TABLE `cluster4-transactions_per_second` ADD INDEX c4\_ix\_datetime\_trans(Date\_Time, mail\_transactions);ALTER TABLE `cluster5-transactions_per_second` ADD INDEX c5\_ix\_datetime\_trans(Date\_Time, mail\_transactions);ALTER TABLE `cluster6-transactions_per_second` ADD INDEX c6\_ix\_datetime\_trans(Date\_Time, mail\_transactions);ALTER TABLE `cluster7-transactions_per_second` ADD INDEX c7\_ix\_datetime\_trans(Date\_Time, mail\_transactions); It got to be a long post with a lot of pointers, but the main one is the different structure of the query so that it doesn’t perform a cross join as it did before. The other ones are recommendations. Please let me know what kind of speed increase you got. Cheers! ---

**Author:** ![kenny01](https://avatars.discourse-cdn.com/v4/letter/k/c6cbf5/32.png) [@kenny01](https://forums.percona.com/u/kenny01)\ **Post date:** [March 27, 2008, 9:53am UTC](https://forums.percona.com/t/bad-performance-with-simple-join/683/5 "2008-03-27T09:53:16Z")

Hi sterin, First of all, thanks for your wide and clear response. The query you proposed improved the response time from minutes to few seconds!!! Thanks again, thats exactly the kind of feedback I was looking for. By Reading your query I can see what the optimizer and MySQL engine was performing wrong and how to work around this. Also the workload seems to spread better on the CPU. I would like to thank you once more for your response. ---

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [March 27, 2008, 10:04am UTC](https://forums.percona.com/t/bad-performance-with-simple-join/683/6 "2008-03-27T10:04:22Z")

My pleasure, nice to know that I have made a difference in the world. ) Take care!