未加星标

Optimizing the MySQL Query

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

I have the following mysql query

SELECT e.id, IF(c.id = 1, g.url, e.url) AS url, IF(s.id = 4, c.short, s.name) AS sub, e.title, b.name AS category FROM g, c, e, b, s WHERE e.category = b.id AND e.subcategory = s.id AND g.c = c.id AND (g.g = e.id OR s.id != 4) AND e.`release` < UNIX_TIMESTAMP() GROUP BY e.id ORDER BY e.`release` DESC LIMIT 15

This took about 2.5 seconds. But if I remove the GROUP BY, it takes about 1.8 seconds. If I remove ORDER BY, it takes 2.3 seconds. BUT if I remove GROUP BY and ORDER BY, it tooks about 0.005 seconds (but then there is many duplicate rows).

What should I do to get same results with faster time?

Here is the explain of the query, if it helps.

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c ALL PRIMARY NULL NULL NULL 26 Using temporary; Using filesort 1 SIMPLE g ref console console 4 int41988_leveli.consoles.id 60 1 SIMPLE e ALL PRIMARY,id NULL NULL NULL 208 Using where; Using join buffer 1 SIMPLE b eq_ref PRIMARY PRIMARY 4 e.cat 1 1 SIMPLE s eq_ref PRIMARY PRIMARY 4 e.subcategory 1

EDIT:

Here are the indexes that already exist.

c.id PRIMARY KEY b.id PRIMARY KEY e.id PRIMARY KEY s.id PRIMARY KEY g.id PRIMARY KEY g.url AND g.console UNIQUE INDEX

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章
分页:12
转载请注明
本文标题:Optimizing the MySQL Query
本站链接:https://www.codesec.net/view/621255.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(80)