Slow as Christmas join
Hello! I have a couple of tables I am doing a join on. Both have 6 fields. Most fields have fewer than 16 characters in them, but at most 75. Neither table is huge: one with 14004 rows and the other with 23677. I created a temp table to insert the data into. When I perform the join, it takes about 17 minutes to complete. The result is only 23674 rows. Question is, why is this query taking so long? I have searched the manual and only found an example where they talk about millions of rows being slow and mine has far fewer than that. It is a one to many relationship between the two tables, but should that really slow things down that much? Is there a way to speed things up a little...or a lot? Just for reference, here is the syntax I am using: insert into tmp1 select table2.field1, table1.field2, table1.field3, table2.field2, table2.field3, table2.field4, table2.field5, table2.field6, table1.field4, table1.field5, table1.field6 from table2,table1 where table2.field1=table1.field1; (I have tried the same query with the tables reversed, i.e, "table1.field1=table2.field1" but it still takes a long time.) MySQL Version: 4.0.15 Mac OS X 10.3.7 Explain: ++--+---+--+-+--+--- +---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--- +---+ | table1 | ALL | NULL | NULL |NULL | NULL | 14004 | | | table2 | ALL | NULL | NULL |NULL | NULL | 23677 | | ++--+---+--+-+--+--- +---+ If there is anything else that will help you solve this problem, let me know and I will be happy to provide it! Thanks In Advance! --kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: summing from multiple tables
You *are* getting both; you just aren't getting them in the same result set. Assuming you are executing both queries in the context of a program that has variables, that should be sufficient for you to do just about anything you can imagine. Yeah, thought about that before I went to sleep. And, since it is only one result, it will be easy to work with. I just wanted MySQL to do the dirty work instead of RealBasic. But both should be equally fast at doing it, so it won't matter. As for the join stuff, I looked at it. Actually, I am using a "LEFT JOIN" in another section of my app. Did not know you could do an implicit join without the syntax "JOIN." Got to admit, I don't understand all I know about JOIN. But it works, so I'm happy! --kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: summing from multiple tables
The solution to your problem is simple: don't attempt to do the final query. Each of your existing queries are perfectly fine the way they are and give sensible and meaningful answers. Simply run the 3rd and 4th queries as two separate queries rather than trying to combine them into one query. That would work, but I am doing calculations based on them and need both to do the calculationsthanks for the other info...I will read the stuff about join... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
summing from multiple tables
Greetings, fellow MySQLers! I know I ain't doing something right, but I don't know what. Say I have a website. It has pages on it that get viewed. The hosting company bills me each day for pages. So.. mysql> select * from bill; ++--+ | amount | day | ++--+ | 1.10 | mon | | 2.20 | tue | | 3.30 | wed | | 4.40 | thu | | 5.50 | fri | | 6.60 | sat | | 7.70 | sun | ++--+ 7 rows in set (0.00 sec) mysql> select * from pageviewed; +--+--+ | page | totaltimesviewed | +--+--+ | example1 |4 | | example2 |1 | | example3 |4 | | example4 |2 | | example5 |1 | +--+--+ 5 rows in set (0.00 sec) mysql> select sum(amount) from bill; +-+ | sum(amount) | +-+ | 30.80 | +-+ 1 row in set (0.01 sec) mysql> select sum(totaltimesviewed) from pageviewed; +---+ | sum(totaltimesviewed) | +---+ |12 | +---+ 1 row in set (0.00 sec) So far so good...but when I do this query, I get this result: mysql> select sum(amount), sum(totaltimesviewed) from bill, pageviewed; +-+---+ | sum(amount) | sum(totaltimesviewed) | +-+---+ | 154.00 |84 | +-+---+ 1 row in set (0.00 sec) Why don't I get "30.80" and "12"? How can I change the query so I do get that result? What in the world did I actually do to get "154.00" and "84"? I can't see how anything adds up to those numbers... TIA for all help! --Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
