About MYSQL index optimization and replacing inor with unionall _ MySQL

BitsCN.com
An article Library contains two tables: category and article. There are 10 classified data entries in category. There are 0.2 million articles in articles.

There is a "article_category" field in article that corresponds to the "category_id" field in category. In the article table, the article_category is defined as an index. The database size is 1.3 GB.
Problem description:
Execute a common query: Select * FROM 'article' Where article_category = 11 orDER BY article_id desc limit 5. The execution time is about 5 seconds. solution:
Create index idx_u on article (article_category, article_id); Select * FROM 'article' Where article_category = 11 orDER BY article_id desc limit 5 to 0.0027 seconds to continue: select * FROM 'article' Where article_category IN (2, 3) orDER BY article_id desc limit 5 execution time takes 11.2850 seconds.
Use OR: select * from articlewhere article_category = 2or article_category = 3 order by article_id desclimit 5 Execution time: 11.0777 solution: Avoid using in or (OR may cause table scanning ), use union all to use union all: (select * from article where article_category = 2 order by article_id desc limit 5) union all (select * from article where article_category = 3 order by article_id desc limit 5) orDER BY article_id desclimit 5 Execution time: 0.0261
Author: adparkingbitsCN.com