使用或不使用左连接，这些 mysql 查询中哪个更有效
我有以下 sql 查询
$select_query_1 = SELECT * FROM user_module_comments WHERE useid = '$hash' ORDER BY id DESC LIMIT 0, 25
while($table = mysql_fetch_array($select_query_1)){
$user_moid = $table['canvas'];
$user_xtract_canvas = mysql_query("SELECT mcanvas FROM user_module WHERE uid = '$user_moid' LIMIT 1");
$selected = mysql_fetch_array($user_xtract_canvas);
$user_canvas_extract = $selected['mcanvas']; // this is what i need
}
或此 sql 查询，
$select_query = SELECT user_module_comments.useid, user_module.mcanvas FROM user_module_comments LEFT JOIN user_module ON user_module.uid = user_module_comments.useid WHERE useid = '$hash' ORDER BY user_module_comments.id DESC LIMIT 0, 25
其中哪个查询更有效 感谢
i have a following sql query
$select_query_1 = SELECT * FROM user_module_comments WHERE useid = '$hash' ORDER BY id DESC LIMIT 0, 25
while($table = mysql_fetch_array($select_query_1)){
$user_moid = $table['canvas'];
$user_xtract_canvas = mysql_query("SELECT mcanvas FROM user_module WHERE uid = '$user_moid' LIMIT 1");
$selected = mysql_fetch_array($user_xtract_canvas);
$user_canvas_extract = $selected['mcanvas']; // this is what i need
}
OR this sql query
$select_query = SELECT user_module_comments.useid, user_module.mcanvas FROM user_module_comments LEFT JOIN user_module ON user_module.uid = user_module_comments.useid WHERE useid = '$hash' ORDER BY user_module_comments.id DESC LIMIT 0, 25
which of these queries is more efficient
thank
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
JOIN可能比在循环中执行相关查询快得多。一般来说，执行一个查询几乎总是比执行n 个查询更快。我只说“几乎总是”，因为我确信有人可以想出一个相反的用例。MySQL 在循环中一遍又一遍地编译 SQL 语句、执行它并获取行集会产生大量开销。使用单个语句可以消除所有这些开销。
您应该安装 Xdebug 并在 PHP 中实际分析这些语句，以了解它们执行所需的时间。
The
JOINis likely to be far, far faster than doing related queries in a loop. In general it is almost always faster to do one query than to do n queries. I only say "almost always" because I'm sure someone can come up with a use case where the opposite may be true.There is a lot of overhead involved with MySQL compiling the SQL statement over and over in the loop, executing it, and fetching a rowset. Using the single statement eliminates all of that overhead.
You should install Xdebug and actually profile these statements in PHP to find out how long they take to execute.