0

I was running a query, the rows of the table are about 10 millions:

select * from zhihu.zhihu_answer_meta  where voteup_count>100000 limit 5;

However, it took more than 20 mins to finish.

I also tried other condition and found that it ran fast (in a second) with small number in where clause:

select * from zhihu.zhihu_answer_meta  where voteup_count>10 limit 5;
select * from zhihu.zhihu_answer_meta  where voteup_count>100 limit 5;

the DDL is as below:

CREATE TABLE `zhihu_answer_meta` (
  `answer_id` bigint(20) NOT NULL,
  `author_name` varchar(500) DEFAULT NULL,
  `reward_member_count` int(11) DEFAULT NULL,
  `answer_created` datetime DEFAULT NULL,
  `voteup_count` int(11) DEFAULT NULL,
  `is_advertiser` tinyint(1) DEFAULT NULL,
  `author_url_token` varchar(500) DEFAULT NULL,
  `comment_count` int(11) DEFAULT NULL,
  `is_org` tinyint(1) DEFAULT NULL,
  `question_type` varchar(100) DEFAULT NULL,
  `question_created` datetime DEFAULT NULL,
  `author_type` varchar(100) DEFAULT NULL,
  `insert_time` datetime DEFAULT NULL,
  `badge_num` int(11) DEFAULT NULL,
  `answer_updated` datetime DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `reward_total_money` int(11) DEFAULT NULL,
  `author_id` varchar(300) DEFAULT NULL,
  `can_comment` tinyint(1) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  UNIQUE KEY `answer_index` (`answer_id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

So why is it slow and is there any way to optimize?

Thanks for any advice.

1
  • 1
    1) LIMIT without ORDER BY is a lottery... 2) You need in index by (voteup_count).
    – Akina
    Sep 27, 2019 at 8:02

1 Answer 1

1

You will need to define an index on the voteup_count column for the query to be performant. Without this index, this query will be scanning all the rows until it reaches the first value of the Range condition.

Assuming that the data tree is in such a way that rows with low voteup_count values come first (basically, in ascending order). In that scenario, MySQL does not need to scan a lot of rows, and stops as soon as the LIMIT is achieved.

To make the query fast in all scenarios, define the following index:

ALTER TABLE zhihu.zhihu_answer_meta ADD INDEX voteup_count_idx (voteup_count) ;

Also, note that LIMIT wihout ORDER BY is non-deterministic, as data is basically stored as an unordered set, so you can get random set of rows.

Also, another optimization tip is to avoid using SELECT *, and instead specify the column name(s) explicitly. Do read: Why is SELECT * considered harmful?

4
  • Thanks a lot for your answer. is there any trade-off for adding the index on the numeric column, such as consuming a lot memory?
    – DennisLi
    Sep 27, 2019 at 8:16
  • 1
    @DennisLi This being a secondary index will consume some space ( a small percent actually), but the benefit of having index in terms of saving CPU processes, memory consumption etc will outweigh everything else. Also, query will be fast, which should eventually make the end user happy (that is the foremost goal imho) Sep 27, 2019 at 8:18
  • @DennisLi in terms of memory consumption, Index is actually going to save it instead. Assuming that your table is InnoDB; your first query would basically put almost all the data into buffer pool (which is really not required). In this case, only index and relevant (fetched) data pages will go into pool. Sep 27, 2019 at 8:20
  • thanks for your clear explanation. learnt a lot ~
    – DennisLi
    Sep 27, 2019 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.