CODEWITHSUNDEEP
mysqlsqlindexing
din2
din2

Reputation: 195

Optimize sql query

I'm trying to optimize sql query which now takes about 20s to execute.

Here is the structure of my tables.

last_login

id | ip_address |when
1    2130706433 2012-05-04 12:00:36

and

country_by_ip

ip_from | ip_to | country
16843008 | 16843263 | CN

Here is the query I use:

SELECT 
ll.ip_address,
ll.when,
cbi.country
FROM last_login ll
LEFT JOIN `country_by_ip` cbi on ll.ip_address BETWEEN  cbi.ip_from AND cbi.ip_to

The fields ip_from and ip_to are indexed.

Can you recommend me how to speed up this query ?

//EDIT

CREATE TABLE `last_login` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` int(11) unsigned NOT NULL,
  `when` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=utf8


CREATE TABLE `country_by_ip` (
  `ip_from` int(20) unsigned NOT NULL,
  `ip_to` int(20) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  KEY `ip_from` (`ip_from`),
  KEY `ip_to` (`ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

EXPLAIN EXTENDED

Upvotes: 0

Views: 214

Answers (2)

symcbean
symcbean

Reputation: 48357

You're not doing yourself any favours by splitting the country_by_ip range across 2 sperate indexes - change these to KEY ip_range (ip_from,ip_to).

Upvotes: 0

fankt
fankt

Reputation: 1047

How about:

SELECT 
ll.ip_address,
ll.when,
cbi.country
FROM last_login ll
LEFT JOIN `country_by_ip` cbi on ll.ip_address > cbi.ip_from 
WHERE ll.ip_address < cbi.ip_to

However, I am totally agree with @Romain, change the DB schema to better design.

Upvotes: 1

Related Questions