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:
当我执行该查询时,php会抛出:
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.
当我限制结果时,我发现它正在回收大约280万个结果。该表有7,241行。
I realize I could use LIKE, but I really don't want to go that route.
我意识到我可以使用LIKE,但我真的不想走那条路。
Thanks for any help!
谢谢你的帮助!
3 个解决方案
#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.
您正在加入FROM子句中的webcal_entry和webcal_entry_user表,但您没有任何JOIN ON或WHERE条件限制来自webcal_entry_user的哪些行返回。结果将是两个表的笛卡尔积,这基本上意味着您将返回每个具有有效cal_date的webcal_entry行,乘以webcal_entry_user表中的行数。
In other words, if you webcal_entry_user has 400 rows you'll get back 400*7241 = 2.8 million rows! Yikes!
换句话说,如果你的webcal_entry_user有400行,你将获得400 * 7241 = 280万行!哎呀!
#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)
你正在做笛卡尔式的加入。基本上,对于第一个表中的每一行,您将加入第二个表中的所有行。如果第一个表有4行而第二个表10则结果集将有40(4x10)
#3
You need to add the key you're joining on that exists in both tables to the WHERE clause. Something like:
您需要将两个表中存在的密钥添加到WHERE子句中。就像是:
AND webcal_entry.user_id = webcal_entry_user.user_id
#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.
您正在加入FROM子句中的webcal_entry和webcal_entry_user表,但您没有任何JOIN ON或WHERE条件限制来自webcal_entry_user的哪些行返回。结果将是两个表的笛卡尔积,这基本上意味着您将返回每个具有有效cal_date的webcal_entry行,乘以webcal_entry_user表中的行数。
In other words, if you webcal_entry_user has 400 rows you'll get back 400*7241 = 2.8 million rows! Yikes!
换句话说,如果你的webcal_entry_user有400行,你将获得400 * 7241 = 280万行!哎呀!
#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)
你正在做笛卡尔式的加入。基本上,对于第一个表中的每一行,您将加入第二个表中的所有行。如果第一个表有4行而第二个表10则结果集将有40(4x10)
#3
You need to add the key you're joining on that exists in both tables to the WHERE clause. Something like:
您需要将两个表中存在的密钥添加到WHERE子句中。就像是:
AND webcal_entry.user_id = webcal_entry_user.user_id