代码之家 › 专栏 › 技术社区 › Codler

优化sql查询

optimization mysql sql

Codler · 技术社区 · 15 年前

SELECT count(locId) AS antal , locId 
FROM `geolitecity_block` 
WHERE (1835880985>= startIpNum AND 1835880985 <= endIpNum) 
  OR (1836875969>= startIpNum AND 1836875969 <= endIpNum) 
  OR (1836878754>= startIpNum AND 1836878754 <= endIpNum)
  ...
  ... 
  OR (1843488110>= startIpNum AND 1843488110 <= endIpNum)
GROUP BY locId ORDER BY antal DESC LIMIT 100

桌子看起来像这样

CREATE TABLE IF NOT EXISTS `geolitecity_block` (
  `startIpNum` int(11) unsigned NOT NULL,
  `endIpNum` int(11) unsigned NOT NULL,
  `locId` int(11) unsigned NOT NULL,
  PRIMARY KEY (`startIpNum`),
  KEY `locId` (`locId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

+----+-------------+-------------------+-------+---------------+-------+---------+------+------+----------------------------------------------+
| id | select_type | table             | type  | possible_keys | key   | key_len | ref  | rows | Extra                                        |
+----+-------------+-------------------+-------+---------------+-------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | geolitecity_block | index | PRIMARY       | locId | 4       | NULL |  108 | Using where; Using temporary; Using filesort |
+----+-------------+-------------------+-------+---------------+-------+---------+------+------+----------------------------------------------+