This was very good reading. And we all learn something new everyday. What you are writing makes so much sense.
This also comes from me relying on that all things said on this list is true, and that all members do know what they write before they write it. I try to never post a reply to anyone unless I'm convinced that I am right. So I learned a lesson. The big lesson learned was not maybe the one about how a database optimizes a question, but rather something else. /Peter -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, August 18, 2006 1:14 AM To: mysql@lists.mysql.com Cc: Chris; Peter Lauri Subject: Re: 1 to many relationship Peter Lauri wrote: > Is there not a better way to do that? What will happen there is that a large > result set will be created because when you just do "select * from > customers c, issues i, customer_issues ci" it will be like the inner product > from all these tables, and then just choosing the right ones. > > If the table C have 1000 records, issues 5000 and customer_issues 15000 you > would end up with a 75,000,000,000 rows large results set, that would not be > so sweet, would it? Peter Lauri wrote: > Yes, it cuts it down to that number of records in the end, so the final > result set will just be a few rows that match the 'WHERE'. But the internal > process of MySQL do merge all tables and then chooses the records that > matches the 'WHERE' clause. No database would work very well if that were the case. You are essentially asserting that mysql has no optimizer. That just isn't so. The job of the optimizer is to devise a plan to execute the query in a way that will examine the fewest possible rows. Eliminating rows before looking at them is always preferable to eliminating them afterwards. For example, given the query SELECT * FROM customers c JOIN customer_issues ci ON c.customerid = ci.customerid JOIN issues i on ci.issueid = i.issueid WHERE c.customerid = 13; mysql will use the index on customers.customerid to select the 1 row with customerid = 13, it will then use the index on customer_issues.customerid to find matching rows in customer_issues, then finally it will use the index on issues.issueid to find matching rows in issues. This is easily verified using EXPLAIN: EXPLAIN SELECT * FROM customers c JOIN customer_issues ci ON c.customerid = ci.customerid JOIN issues i on ci.issueid = i.issueid WHERE c.customerid = 13; +-------------+-------+--------+---------+---------------------+------+ | select_type | table | type | key | ref | rows | +-------------+-------+--------+---------+---------------------+------+ | SIMPLE | c | const | PRIMARY | const | 1 | | SIMPLE | ci | ref | PRIMARY | const | 4 | | SIMPLE | i | eq_ref | PRIMARY | test.ci.issueid | 1 | +-------------+-------+--------+---------+---------------------+------+ 3 rows in set (0.01 sec) (Note that I've pared the output of EXPLAIN down to a few relevant columns.) The "rows" column tells the story. Mysql plans to use the primary key to find the 1 matching row in customers, then use the primary key to find the 4 matching rows in customer_issues for that 1 customer, then use the primary key to find the 1 matching row in issues for each row found in customer_issues. That is, it expects to produce 1x4x1 = 4 rows, not 'size of c' x 'size of ci' x 'size of i' rows! You might want to read the optimization section of the manual for more on the subject <http://dev.mysql.com/doc/refman/4.1/en/optimization.html>. Chris wrote: > I don't know enough about mysql internals to debate that so I'll take > your word for it. > > 'Explain' doesn't give enough information about what happens behind the > scenes so I'm not sure how to prove/disprove that and I don't know of > any tools that would show you that (if there is let me know!). > > Having said all of that I've never had a problem doing it the way I > mentioned.. ;) EXPLAIN is documented in the manual <http://dev.mysql.com/doc/refman/4.1/en/explain.html>. Michael -- 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]
