Optimizing a select statement with a join on two large tables?!?!

[jordanpope]

Firstly - the statement I am executing is as follows:

SELECT ip_addr_table.country_full as country, count(*) as count
FROM ip_addr_table, csr_main_table
WHERE ip_num BETWEEN ip_addr_table.ip_num_from AND ip_addr_table.ip_num_to
GROUP BY country
ORDER BY count DESC;

Which returns the following result:

+---------------+-------+
| country | count |
+---------------+-------+
| BRAZIL | 3773 |
| ARGENTINA | 167 |
| UNITED STATES | 60 |
| PERU | 24 |
| VENEZUELA | 21 |
| PARAGUAY | 14 |
| SPAIN | 12 |
| CUBA | 11 |
| CHILE | 10 |
| URUGUAY | 9 |
| ITALY | 1 |
| PORTUGAL | 1 |
+---------------+-------+
12 rows in set (1 min 45.73 sec)

Basically, the first table consists of web traffic logs (the IP number corresponds to the users IP address) and the second table is used to determine the country of the user based on the ip_num.

However, as you can see, this select statement is very slow (1 min 45.73 sec) - and the actual database that I wish to run this script on is much larger than the test database I have used here.

I currently have an index on the ip_num from the first table, and an group index on the ip_num_from/ip_num_to fields from the second table.

Thanks in advance for any advice.

 

[sleipnir214]

I'd index country.

I don't know how efficient you're going to get this query to be. You're relating two tables not by a single value but by a range of values.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!