Anything obvious in optimization missed in my left join query? I've really simple join statement, which take a lot time to complete. I would like to know if I'm missing any obvious optimization steps i could take.

So I've got two tables, with the following create statements:

CREATE TABLE `objects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `positive` tinyint(1) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  `feature_id` int(11) DEFAULT NULL,
  `cell_size` int(11) DEFAULT NULL,
  `edge_size` int(11) DEFAULT NULL,
  `file_name` char(255) DEFAULT NULL,
  `frame_number` int(11) DEFAULT NULL,
  `left_coord` tinyblob,
  `right_coord` tinyblob,
  `image_info_id` int(11) DEFAULT NULL,
  `image_data_id` int(11) DEFAULT NULL,
  `image_data_size` tinyblob,
  `opcode` char(50) DEFAULT NULL,
  `object_name` char(50) DEFAULT NULL,
  `pose` char(50) DEFAULT NULL,
  `tilt_angle` float DEFAULT '0',
  `weight_id` float NOT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `video_data_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `image_info_id` (`image_info_id`),
  KEY `feature_id` (`feature_id`),
  KEY `on_ind` (`object_name`),
  CONSTRAINT `feature_link` FOREIGN KEY (`feature_id`) REFERENCES `features` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `image_info_link` FOREIGN KEY (`image_info_id`) REFERENCES `image_info` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1283145 DEFAULT CHARSET=latin1;


And this one:

CREATE TABLE `uchog7x7` (
  `id` int(11) NOT NULL,
  `uchog7x7` blob,
  `iuchog7x7` blob,
  `needs_update` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `needs_update` (`needs_update`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


I'm performing the following query:

SELECT 
    objects.id
FROM
    objects
        LEFT JOIN
    uchog7x7 ON objects.id = uchog7x7.id
WHERE
    uchog7x7.needs_update = 1
        AND uchog7x7.id IS NOT NULL
        AND object_name = 'face'


This query takes a lot of time to complete. Table named objects has 1,035,526 records, table uchog7x7 contains 639,322 objects. 

When I do explain I get the following:



I'm not using file-per-table. 

Am I missing something obvious explaining why does it take a lot of time to complete? My guess is that somehow non-desirable full table scans happen, but I do not know for sure.

Thank you.
SELECT  o.id
    FROM  objects AS o
    JOIN  uchog7x7  ON o.id = u.id  -- Not LEFT
    WHERE  u.needs_update = 1
      -- AND  u.id IS NOT NULL   -- not needed
      AND  o.object_name = 'face'


Don't use CHAR() unless the value is a constant length.

What fraction of the rows have 'face'?  What percentage have needs_update?

The optimizer, when given no good choice, starts with the smaller table.  According to EXPLAIN, it found about 1 row with "needs_update".  Probably that is far off, and you need to remove that index.

What version of MySQL are you using?  5.7 has improved the 'cost models', and might do a better job.

Other notes:

Don't use tinyblob; use VARBINARY(255) instead.

Why are *coord and image_data_size going into blobs; they sound like numeric values?

What will you do with the ids that come back from the query?  Some heavy-duty image processing?  If so, consider adding LIMIT 1 (or LIMIT 10) so that the query comes back faster.  I assume you will pick up others later.