gpt4 book ai didi

mysql加入: what is faster?

转载 作者:行者123 更新时间:2023-11-29 14:12:15 29 4
gpt4 key购买 nike

我的表:big_table

+-----------------+--------------+------+-----+---------+----------------+| Field           | Type         | Null | Key | Default | Extra          |+-----------------+--------------+------+-----+---------+----------------+| id              | mediumint(7) | NO   | PRI | NULL    | auto_increment || title           | varchar(255) | NO   |     | NULL    |                || category_id     | tinyint(2)   | NO   |     | NULL    |                || sub_category_id | tinyint(2)   | NO   |     | NULL    |                || width           | smallint(5)  | NO   |     | NULL    |                || height          | smallint(5)  | NO   |     | NULL    |                || ratio_width     | smallint(5)  | NO   |     | NULL    |                || ratio_height    | smallint(5)  | NO   |     | NULL    |                || size            | int(8)       | NO   |     | NULL    |                || mime            | tinyint(2)   | NO   |     | NULL    |                || views           | mediumint(7) | NO   | MUL | NULL    |                || time            | int(10)      | NO   |     | NULL    |                || file            | varchar(255) | NO   |     | NULL    |                |+-----------------+--------------+------+-----+---------+----------------+

small_table

+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| id     | mediumint(7) | NO   | PRI | NULL    |       || width  | smallint(5)  | NO   | MUL | NULL    |       || height | smallint(5)  | NO   | MUL | NULL    |       |+--------+--------------+------+-----+---------+-------+

so what's faster (example):

   SELECT * FROM `big_table` WHERE `width` =1920 AND `height`=1080;  

或者使用连接

 select big_table.*
from small_table
left join small_table small_table2
ON (small_table.id=small_table2.id
and `small_table`.`height` = '1080')
left join big_table
ON (big_table.id=small_table.id)
where small_table.width = '1920';

或者从同一个表加入?

select big_table.*
from big_table as big_table1
left join big_table big_table2
ON (big_table1.id=big_table2.id and `big_table1`.`height` = '1080')
left join big_table
ON (big_table.id=big_table1.id)
where big_table1.width = '1920';

或者有更好的解决方案,更好的选择吗? (在两个表上我都可以使用索引(宽度和高度),但只有 ID 是唯一的)

最佳答案

无连接比连接更快。如果您有适当的索引(即 widthheight 组合上的单个索引),那么 big_table 上的简单选择将是更快。

关于mysql加入: what is faster?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13289072/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com