I need help on optimizing following query :


SELECT * from location where code1 >= 520005 and code2 <=520005;

This table is having approximate 1500000 rows. I tried using various combination of indexes but mysql is not using any index. This query takes between 3-8 seconds. Our application usage this query very very frequently. Any other alternate suggestion to speed up this query work

Thanks in advance for all help.

ebourn

Can you post the EXPLAIN of that query as well as a DESCRIBE of your table and a SHOW INDEX for that table? That would help us to pinpoint the problem...

Hi bstjean,

Thanks.

Here is the actual query :

SELECT * FROM i2l.ip2loc WHERE ip_from <= 1141533096 AND ip_to >= 1141533096

it is taking time :

Showing rows 0 - 0 (1 total, Query took 4.6680 sec)

This is the explain of query :

table type possible_keys key key_len ref rows Extra
ip2loc ALL PRIMARY,ip_from_2 NULL NULL NULL 1248226 where used

This is the table Structure :

ip_from bigint(20) No
ip_to bigint(20) No
country_code char(2) Yes NULL
country_name varchar(64) Yes NULL
region varchar(128) Yes NULL
city varchar(128) Yes NULL


The following is the indexes

Keyname Type Cardinality Field
===============================
PRIMARY PRIMARY 1248226 ip_from
ip_from_2 INDEX 1248226 ip_from ip_to




once again thanks for your time.

Well, it looks like MySQL won't use the index because your query is probably returning more than 30% of the rows of that table... This is not a SQL problem or a MYSQL problem, any RDBMS would act the same way... Have you tried a with self join and adding an index on ip_to ? That's the only other shot you've got at this problem I guess...

Hi bstjean,

This query is returning only one row. I tried an index on ip_to also but does not work. In fact mysql is not using index in this query.
Mysql Explain see possible keys (PRIMARY,ip_from) but mysql is not using.

table type possible_keys key key_len ref rows Extra
ip2loc ALL PRIMARY,ip_from_2 NULL NULL NULL 1248226 where used


I tried some forced option like use index also in query but does not work.

Can you give me any idea about self join query. Some sample query if possible.

Thanks and regards