How to optimize query with ORDER BY and FULLTEXT search?

Question

MySQL

There is a table on 12 million lines.

CREATE TABLE `shingles` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `shingle` text NOT NULL,
 `count` int(11) NOT NULL,
 `used` tinyint(1) DEFAULT NULL,
 `stop` tinyint(1) DEFAULT NULL,
 `date` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `shingle` (`shingle`(255)) USING BTREE,
 KEY `count` (`id`),
 FULLTEXT KEY `shingle_fulltext` (`shingle`)
) ENGINE=InnoDB AUTO_INCREMENT=11707635 DEFAULT CHARSET=utf

The next request is executed around 20s.

select `t1`.* from 
(select * from `shingles` 
where MATCH (shingle) AGAINST ('слово' IN BOOLEAN MODE)) as t2 
inner join `shingles` as `t1` on `t1`.`id` = `t2`.`id`
 order by `t1`.`count` desc limit 5

The more results in full text search, the slower...
How can I optimize? Without "order by" everything flies.

Answer 1

You can try without a subquery, so there will be no nested loop over a large table:
SELECT t1.*, MATCH (t1.shingle) AGAINST ('word' IN BOOLEAN MODE) AS ind
FROM shingles as t1
ORDER BY t1.ind DESC, t1.count DESC
LIMIT 5

Answer 2

SELECT * 
  FROM `shingles` 
  WHERE MATCH (`shingle`) AGAINST ('слово' IN BOOLEAN MODE)
  ORDER BY `count` DESC
  LIMIT 5