How to fix the query so that indexes are used?

Question

MySQL

There are two tables

CREATE TABLE `ls_topic` (
  `topic_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `topic_title` varchar(200) NOT NULL,
  `topic_publish` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`topic_id`),
  KEY `topic_publish` (`topic_publish`),
) ENGINE=InnoDB AUTO_INCREMENT=4340 DEFAULT CHARSET=utf8 

CREATE TABLE `ls_vote` (
  `target_id` int(11) unsigned NOT NULL DEFAULT '0',
  `target_type` enum('topic','blog','user','comment','store') NOT NULL DEFAULT 'topic',
  `user_voter_id` int(11) unsigned NOT NULL,
  `vote_direction` tinyint(2) DEFAULT '0',
  `vote_value` float(9,3) NOT NULL DEFAULT '0.000',
  `vote_date` datetime NOT NULL,
  PRIMARY KEY (`target_id`,`target_type`,`user_voter_id`),
  KEY `user_voter_id` (`user_voter_id`),
  KEY `vote_date` (`vote_date`),
  KEY `target_id` (`target_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

If I query only from the ls_vote table, then mysql uses the index of the ls_vote table and all is well.

explain select 
        v.vote_date
        from ls_vote as v

        where
          v.target_type = 'topic' order by v.vote_date desc LIMIT 0,25

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: v
         type: index
possible_keys: NULL
          key: vote_date
      key_len: 8
          ref: NULL
         rows: 25
        Extra: Using where; Using index

but as soon as I start doing a join with the ls_topic table, for some reason, all rows are selected for the ls_topic table and the query is executed for several seconds

explain select 
        t.topic_id,
        v.vote_date
        from ls_vote as v

        join ls_topic as t
          on (t.topic_id = v.target_id)

        where
          v.target_type = 'topic' order by v.vote_date desc LIMIT 0,25

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: PRIMARY
          key: topic_publish
      key_len: 1
          ref: NULL
         rows: 3733
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: v
         type: ref
possible_keys: PRIMARY,target_id
          key: PRIMARY
      key_len: 5
          ref: t.topic_id,const
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

I got this data on a test table in which (3733 records) in the real table everything is the same, only there are about 40 thousand records in the ls_topic table and about 4M records in the ls_vote table
Why does the database make a full selection from the ls_topic table? and how to fix it?

Answer 1

And on a real basis, the execution plan is the same? It's just possible that for some reason mysql decided not to use an index in this situation on this record set.
On a real basis, the plan may be different.
And make a unique cluster index by topic_id.

Answer 2

yes, on a real basis, there is also such a plan.
> Also make on topic_id a unique clustered index.
how is that?