mysql join: what is faster?
My tables: 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;
or use join
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';
or join's from the same table?
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';
or there is some better solution, better select's? (on both tables I can use indexes (width and height), but only ID is unique)
No join is faster that join. If you have a proper index (that is, a single index on the combination of width and height ) then the simple select on big_table will be way faster.
Query execution time, is not the only parameter to take into consideration.
If any other table would have to join big_table , it would kill performance on those joins.
For scalability, I would defiantly split the tables, having the data separated will ensure you, that a bottleneck won't arise here.
Make sure you put least used data in a table for itself, and most commonly used data in the other. If you have a huge text , putting this in the least used table, would be good as well.
上一篇: MySQL会加入MUL密钥来影响性能？
下一篇: mysql连接：什么更快？