Optimize sql query

Question

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

Please show us the table structures, as given by SHOW CREATE TABLE last_login and SHOW CREATE TABLE country_by_ip. Also, would be useful to see what EXPLAIN EXTENDED <your_query> gives. I suspect the LEFT JOIN based on a BETWEEN clause is a badass performance killer. — Romain
– Romain, Commented May 4, 2012 at 13:58
How large (in terms of number of rows) are these tables? What is the clustered index on country_by_ip? — Adam Robinson
– Adam Robinson, Commented May 4, 2012 at 14:00
@AdamRobinson, last_login have 31 records. country_by_ip have 116288 records. — din2
– din2, Commented May 4, 2012 at 14:03
@din2: please also post the EXPLAIN EXTENDED. You probably need to replace your indexes on ip_from and ip_to with a single index on both. — Romain
– Romain, Commented May 4, 2012 at 14:04
@din2: If possible, I'd like to have (full) table content fo both tables in a pastebin/gist/whatever... Getting LEFT JOIN and BETWEEN using indices is... Tricky. — Romain
– Romain, Commented May 4, 2012 at 14:14

fankt · Accepted Answer · 2012-05-04 14:46:06Z

1

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.

answered May 4, 2012 at 14:46

fankt

1,0471 gold badge7 silver badges16 bronze badges

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

2 Comments

din2 Over a year ago

Thanks ! The query not tooks just 544ms

Adam Robinson Over a year ago

The where clause like this essentially makes your join an inner join. This is equivalent to the original query with a normal join instead of a left join.

symcbean · Accepted Answer · 2012-05-04 15:20:55Z

0

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

answered May 4, 2012 at 15:20

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

Collectives™ on Stack Overflow

Optimize sql query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related