Hi.
Results are:
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip2isp | CREATE TABLE `ip2isp` (
`start` int(11) unsigned NOT NULL,
`end` int(11) unsigned NOT NULL,
`location` int(11) NOT NULL,
`isp` varchar(255) NOT NULL,
`organization` varchar(255) NOT NULL,
`country` char(2) NOT NULL,
`city` varchar(50) DEFAULT NULL,
KEY `isp2idx` (`start`,`end`),
KEY `start` (`start`) USING BTREE,
KEY `end` (`end`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'ip2isp'\G
*************************** 1. row ***************************
Name: ip2isp
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1126339
Avg_row_length: 71
Data_length: 80891376
Max_data_length: 281474976710655
Index_length: 39110656
Data_free: 0
Auto_increment: NULL
Create_time: 2011-02-11 16:50:02
Update_time: 2011-02-11 16:50:03
Check_time: 2011-02-11 16:50:07
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
mysql> explain SELECT locId, location.city as city,latitude,longitude FROM ip2isp join location on ip2isp.location=locId WHERE INET_ATON('x.x.x.x') BETWEEN start AND end\g
+----+-------------+----------+--------+-------------------+---------+---------+---------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+-------------------+---------+---------+---------------------------------+---------+-------------+
| 1 | SIMPLE | ip2isp | ALL | isp2idx,start,end | NULL | NULL | NULL | 1126339 | Using where |
| 1 | SIMPLE | location | eq_ref | PRIMARY | PRIMARY | 4 | transfer_ro_pre.ip2isp.location | 1 | Using where |
+----+-------------+----------+--------+-------------------+---------+---------+---------------------------------+---------+-------------+
2 rows in set (0.08 sec)
mysql>
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+---------+
12 rows in set (0.00 sec)
mysql>
Edited 1 time(s). Last edit at 02/14/2011 07:58AM by Dragos Gabriel Iancu.