sql - 优化sql查询

Question

是否可以优化此查询？

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 |
+----+-------------+-------------------+-------+---------------+-------+---------+------+------+----------------------------------------------+

Answer 1

要优化性能，请在 startIpNum 和 endIpNum 上创建索引。

CREATE INDEX index_startIpNum ON geolitecity_block (startIpNum); CREATE INDEX index_endIpNum ON geolitecity_block (endIpNum);