Slow join? NULL ref which is better

I have two tables:

CREATE TABLE `bb_index` (
  `bb_id` smallint(5) unsigned NOT NULL,
  `object_id` bigint(10) unsigned NOT NULL,
  `in_object_id` bigint(10) unsigned NOT NULL,
  `f` int(10) unsigned DEFAULT NULL,
  `length` decimal(10,5) DEFAULT NULL,
  PRIMARY KEY (`bb_id`,`object_id`,`in_object_id`),
  KEY `in_obj_key` (`bb_id`,`in_object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `obj_bb_relation` (`bb_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `object_id` bigint(10) unsigned NOT NULL DEFAULT '0',
  `object_bb_f` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`bb_id`,`object_id`),
  KEY `object_id` (`object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Which is better: Option 1:

SELECT object_id, f, length , object_bb_freq as C 
  FROM (select object_id, f, length 
          from bb_index use index (in_obj_key) 
         where bb_id = 14 
           and in_object_id = XXX
       ) as a join obj_bb_relation as b using (object_id)
开发者_运维百科

EXPLAIN:

Option 2:

SELECT object_id, f, length, object_bb_f AS C 
  FROM bb_index AS a 
  JOIN obj_bb_relation AS b USING ( object_id ) 
 WHERE in_object_id = XXX 
   AND a.bb_id =YY 
   AND b.bb_id =YY 

OR?

Another option..?

In most cases both are the same. Most database systems have now query optimization which automatically will make from query 2, query 1. Or even something better. But if you use some very old or simplified database, option 1 is better. Except some very special cases joins should go last.

To begin with, your queries are hardly similar at all!

SELECT object_id, f, length , object_bb_freq as C
FROM (select object_id, f, length
      from bb_index use index (in_obj_key)
      where bb_id = 14 and in_object_id = XXX) as a
join obj_bb_relation as b
     using (object_id)

SELECT object_id, f, length, object_bb_f AS C
FROM bb_index AS a
JOIN obj_bb_relation AS b
     USING ( object_id )
WHERE in_object_id = XXX AND a.bb_id =YY AND b.bb_id =YY 

Very important bits went missing in translation, namely

• The forced index on bb_index
• The filter on b.bb_id

Other than that, because you have used the ANSI INNER JOIN form rather than the comma form (FROM bb_index, obj_bb_relation WHERE ..), MySQL will shuffle the WHERE and ON/USING clauses as required since they are semantically the same thing. This is regardless of the fact that you have subqueried a, because MySQL is smart enough to unroll it.

Personally, since you have filters on both a.bb_id and b.bb_id, if you knew for a fact that one table would return only 1% of the records of the other table, then you could use the oft-maligned comma notation (FROM bb_index, obj_bb_relation WHERE ..) because MySQL will process the filters on bb_index before joining to obj_bb_relation - otherwise I generally use the flattened JOIN form (option 2) because it is much clearer (than the subquery in option 1)