MYSQL - 按索引列排序 [英] MYSQL - order by indexed column

问题描述

我有一张有 500,000 行的表格

如果不存在则创建表`listings`(`id` int(10) 无符号 NOT NULL AUTO_INCREMENT，`source_id` int(10) 无符号非空，`cat_id` mediumint(5) 非空，`title` 中文本不为空，`views_point` int(10) 无符号 NOT NULL DEFAULT '0'，`publishedon_hourly` int(10) 无符号 NOT NULL DEFAULT '0'，主键(`id`)，KEY`views_point`(`views_point`)，KEY `listings`(`publishedon_hourly`、`published`、`cat_id`、`source_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=365513 ;

我想做这样的查询

SELECT *来自列表在哪里 (`publishedon_hourly`BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' )AND UNIX_TIMESTAMP( '2015-10-5 12:00:00' ) ) AND (published =1 ) AND cat_id输入 ( 1, 2, 3, 4, 5 )ORDER BY views_point DESC限制 10

这个查询有一段时间完全按照我的意愿工作(

但是我不能添加这个条件

`publishedon_hourly` BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) ) AND (published =1 ) AND cat_id= 5

我不知道，我应该分配给哪个(t1 或 t2)?

另一方面，这种方式怎么样

SELECT *来自列表在哪里 (`publishedon_hourly` BETWEEN UNIX_TIMESTAMP( '2015-09-5 00:00:00' ) 和 UNIX_TIMESTAMP( '2015-09-5 23:00:00' ))AND(已发布=1)并且 views_point 不为空ORDER BY views_point DESC 限制 20

如果有人不好，谁能告诉我大博客是如何通过点击获得帖子的?

解决方案

您应该将 listings 索引拆分为单独的索引.

除此之外，考虑到您按 t1 排序，几乎可以肯定您应该为所有条件指定 t1.

使用条件为

`t1`.`publishedon_hourly` BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) ) AND ( `t1`.`published` = 1 ) AND `t1`.`cat_id` = 5

i have this table with 500,000 row

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(10) unsigned NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `views_point` int(10) unsigned NOT NULL DEFAULT '0',
  `publishedon_hourly` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `views_point` (`views_point`),
  KEY `listings` (`publishedon_hourly`,`published`,`cat_id`,`source_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=365513 ;

i want to make a query like this

SELECT *
FROM listings
WHERE (
`publishedon_hourly`
BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' )AND UNIX_TIMESTAMP( '2015-10-5 12:00:00' ) ) AND ( published =1 ) AND cat_id
IN ( 1, 2, 3, 4, 5 )
ORDER BY views_point DESC
LIMIT 10 

this query some time work as i want exactly (see this) , but after some opinions, this is incorrect, i searched on the web for solution, i found

this : http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/ and this: http://www.getsymphony.com/discuss/issues/view/657/ and i add index on views_point then tried this query

select t1.* 
from  listings t1 
left outer join  listings t2 on (t1.views_point=t2.views_point) 
order by t1.views_point 
limit 10

and this the explain

but i can not add this condition

`publishedon_hourly` BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) ) AND ( published =1 ) AND cat_id = 5

i don't know, which one should i assign to (t1 or t2) ?

in the other hand what about this way

SELECT *
FROM listings
WHERE (
`publishedon_hourly` BETWEEN UNIX_TIMESTAMP( '2015-09-5 00:00:00' ) AND UNIX_TIMESTAMP( '2015-09-5 23:00:00' ))
AND (published =1)
and views_point is not null
ORDER BY views_point DESC limit 20

if any one will not be good,can any one tell me please how the big blogs get the posts by hits?

解决方案

You should split your listings index to separate indices.

Apart from this, given that you sort by t1, it is almost certain that you should specify t1 for all conditions.

EDIT: Use condition as

`t1`.`publishedon_hourly` BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) ) AND ( `t1`.`published` = 1 ) AND `t1`.`cat_id` = 5

这篇关于MYSQL - 按索引列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！