MySQL between query returning redundant results

Question

SELECT webcal_entry.cal_id, webcal_entry.cal_name , webcal_entry.cal_priority,
webcal_entry.cal_date , webcal_entry.cal_time , webcal_entry_user.cal_status,
webcal_entry.cal_create_by , webcal_entry.cal_access, webcal_entry.cal_duration ,
webcal_entry.cal_description , webcal_entry_user.cal_category 
FROM webcal_entry, webcal_entry_user
WHERE webcal_entry.cal_date BETWEEN '20090601' AND '20090631'

When I execute that query php throws back:

mysql_query(): unable to save result set
MySQL client ran out of memory

When I limit the results I see that it is pulling some 2.8 million results back. This table has 7,241 rows.

I realize I could use LIKE, but I really don't want to go that route.

Thanks for any help!

Answer 1

You are joining the webcal_entry and webcal_entry_user tables in your FROM clause but you do not have any JOIN ON or WHERE conditions constraining which rows from webcal_entry_user to return. The result will be the cartesian product of the two tables, which basically means you'll get back each webcal_entry row that has a valid cal_date, multiplied by the number of rows in the webcal_entry_user table.

In other words, if you webcal_entry_user has 400 rows you'll get back 400*7241 = 2.8 million rows! Yikes!

Answer 2

You're doing a cartesian join. Basically, for each row in the first table you're joining against all the rows in the second. If the first table has 4 rows and the second 10 the result set will have 40 (4x10)

Answer 3

These links may answer your question:

• http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/
• http://www.slideshare.net/techdude/how-to-kill-mysql-performance

Answer 4

You need to add the key you're joining on that exists in both tables to the WHERE clause. Something like:

AND   webcal_entry.user_id = webcal_entry_user.user_id