Optimize sql query

Question

Is it possable to optimize this query?

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

The table looks like this

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;

UPDATE and the explain-query looks like this

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

To optimize performance, create an index on startIpNum and endIpNum.

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

Answer 2

Indexing columns that are being grouped or sorted on will almost always improve performance. I would suggest plugging this query into the DTA (Database Tuning Advisor) to see if SQL can make any suggestions, this might include the creation of one or more indexes in addition to statistics.

Answer 3

If it is possible in your use case, create a temporary table *TMP_RESULT* (remove order) and than submit a second query that orders results by antal. Filesort is extremely slow and -- in your case -- you can not avoid this operation, because you do not sort by any of keys/indices. To perform count operation, you have to scan complete table. A temporary table is a much faster solution.

ps. Adding an index on (startIpNum, endIpNum) definitely will help you to get better performance but -- if you have a lot of rows -- it will not be a huge improvement.