优化大数据库中的SQL查询？

Query

SELECT *
FROM user_ip_tmp
WHERE too = 'http://example.com/'
AND contry != 'CN'
AND contry != 'TW'
ORDER BY id DESC
LIMIT 50 

MySQL returns:

Showing rows 0 - 29 ( 50 total, Query took 11.9276 sec) [id: 3452538 - 3448824]

if i remove ORDER BY id DESC

如果我删除ORDER BY ID DESC

Showing rows 0 - 29 ( 50 total, Query took 0.0033 sec)

Explain plan:

count

SELECT count( * )
FROM user_ip_tmp

Example of the database used

使用的数据库示例

CREATE TABLE IF NOT EXISTS `user_ip_tmp` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `ip` varchar(20) NOT NULL,
  `dataip` bigint(20) NOT NULL,
  `ref` text NOT NULL,
  `click` int(20) NOT NULL,
  `code` varchar(17) NOT NULL,
  `too` text NOT NULL,
  `checkopen` varchar(17) NOT NULL,
  `contry` text NOT NULL,
  `vOperation` text NOT NULL,
  `vBrowser` text NOT NULL,
  `iconOperation` text NOT NULL,
  `iconBrowser` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ip` (`dataip`),
  KEY `ip` (`checkopen`),
  KEY `ip` (`code`),
  KEY `ip` (`too`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5426268 ;

I want the correct way to do the query and optimize database for ORDER BY id DESC

我想要正确的方法来进行查询并优化ORDER BY ID DESC的数据库

2 个解决方案

#1

It would be interesting to know something about the distribution of your data. Could you add the results of the following queries to your post? (no need for pictures, plain text will do).

了解有关数据分布的信息会很有趣。您可以将以下查询的结果添加到您的帖子中吗? (不需要图片,纯文本会做)。

SELECT count(*) FROM user_ip_tmp WHERE too = 'http://example.com/' AND contry != 'CN' AND contry != 'TW'; 

SELECT count(*) FROM user_ip_tmp WHERE too = 'http://example.com/'; 

Also, could you test this alternative for performance? EDIT: alias for subquery

此外,您可以测试这种替代性能吗?编辑:子查询的别名

SELECT sub.* FROM
(SELECT *
FROM user_ip_tmp
WHERE too = 'http://example.com/'
AND contry != 'CN'
AND contry != 'TW'
) sub
ORDER BY sub.id DESC
LIMIT 50

Edit If it is an option to add and experiment with indexes, then you could try one of these (or both and see which is better)

编辑如果它是一个添加和试验索引的选项,那么你可以尝试其中一个(或两个,看看哪个更好)

CREATE INDEX index_name ON `user_ip_tmp` (`too`, `id`);
CREATE INDEX index_name ON `user_ip_tmp` (`too`, `contry`, `id`);

#2

You can create an index with:

您可以使用以下命令创建索引:

 id, too and contry