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

Have a stored procedure make several calls and return that, as the full table scan is going to kill your performance. — James Black
– James Black, Commented Sep 15, 2010 at 14:57

Arnaud Bienvenu · Accepted Answer · 2010-09-15 15:34:50Z

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);

answered Sep 15, 2010 at 15:34

Arnaud Bienvenu

511 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Codler Over a year ago

should I add index on startIpNum even if it is primary?

jrage99 · Accepted Answer · 2010-09-22 18:35:59Z

0

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.

answered Sep 22, 2010 at 18:35

jrage99

1

Comments

Skarab · Accepted Answer · 2010-09-22 18:55:22Z

0

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.

answered Sep 22, 2010 at 18:55

Skarab

7,16115 gold badges54 silver badges90 bronze badges

Collectives™ on Stack Overflow

Optimize sql query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related