mysql join: what is faster?

Question

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)

So what is it that you are trying to achieve exactly? What should it do and what will be the result of that? — Deep Frozen, Nov 08 '12 at 12:26
try `$sql='...'; $msc=microtime(true); $mysql_query($sql); $msc=microtime(true)-$msc; echo $msc.' seconds'; echo ($msc*1000).' milliseconds'; ` — NullPoiиteя, Nov 08 '12 at 12:27
first would be faster as it getting data form only one table — NullPoiиteя, Nov 08 '12 at 12:28
check http://stackoverflow.com/questions/2349270/in-what-way-does-denormalization-improve-database-performance — NullPoiиteя, Nov 08 '12 at 12:55
The second query is using a table that doesn't exist, `small_table_ratio`? — Barmar, Nov 08 '12 at 12:57

score 1 · Accepted Answer · answered Nov 08 '12 at 12:28

1

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.

answered Nov 08 '12 at 12:28

GolezTrol

114,394
18
182
210

Seeing as he is probably joining on `sub_category_id` and `category_id`, he will just move the bottleneck to somewhere else, and the overall performance will decrease, or am I wrong? – Kao Nov 08 '12 at 12:51

score 0 · Answer 2 · answered Nov 08 '12 at 12:31

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 join: what is faster?

2 Answers2