Problem in mysql indexing.

[subhomoy]

Hello everyone.

 

I have a large table consisting of 135497 rows

 

I have found that by indexing the table, it will take less time in quering the results.

 

In some site it shows that Using "Explain" before the queries will show that how much rows it has to travel to get the desired results.

 

SO I have doen and it shows like below...

mysql> EXPLAIN select * FROM ip2country WHERE 3084727327  BETWEEN begin_long_ip AND end_long_ip;
+----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys                                                                   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | ip2country | ALL  | ip_adress,begin_long_ip,end_long_ip,begin_long_ip_3,count_index,begin_long_ip_2 | NULL | NULL    | NULL | 135497 | Using where |
+----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+

The above results shows 135497. means it has to travel all the rows to get the result.

 

So i have done index on the column ("begin_long_ip and end_long_ip)

mysql> CREATE INDEX count_index ON ip2country(begin_long_ip,end_long_ip);
Query OK, 135497 rows affected, 1 warning (0.87 sec)
Records: 135497  Duplicates: 0  Warnings: 1

But it still shows the same result.

mysql> EXPLAIN select * FROM ip2country WHERE 3084727327  BETWEEN begin_long_ip AND end_long_ip;
+----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys                                                                   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | ip2country | ALL  | ip_adress,begin_long_ip,end_long_ip,begin_long_ip_3,count_index,begin_long_ip_2 | NULL | NULL    | NULL | 135497 | Using where |
+----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+

Ahy help will be greatly apprecaited...

 

[gizmola]

Do a SHOW CREATE TABLE ip2country and copy and paste the results.

[bsmither]

I recently came across a paper on searching and sorting. One of the topics explained the difference between two approaches to the needle/haystack scenario:

This query (I forget it's academic name):

WHERE needle BETWEEN haystack_A AND haystack_B

vs this query:

WHERE haystack BETWEEN needle_A AND needle_B

 

This reply is not an answer to your question, but the paper was very enlightening.

[subhomoy]

Thanks for the reply...

Here it is what u have asked.

mysql> SHOW CREATE TABLE ip2country;
+------------+------------------------------------------------
| Table      | Create Table
+------------+------------------------------------------------
| ip2country | CREATE TABLE `ip2country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `begin_long_ip` varchar(20) NOT NULL,
  `end_long_ip` varchar(20) NOT NULL,
  `country_code` varchar(3) NOT NULL,  
  PRIMARY KEY (`id`),
  KEY `ip_adress` (`begin_long_ip`,`end_long_ip`)
) ENGINE=MyISAM AUTO_INCREMENT=135716 DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------

Edited July 20, 2014 by subhomoy

[subhomoy]

Hello plz help anybody.....

[gizmola]

Just as I expected.

 

`begin_long_ip` varchar(20) NOT NULL,
`end_long_ip` varchar(20) NOT NULL,

You have defined these as strings. Then you want to do a comparison as integers.

 

EXPLAIN select * FROM ip2country WHERE 3084727327  BETWEEN begin_long_ip AND end_long_ip;

This works because mysql is changing the columns on the fly to integers.

 

The solution is.... convert the columns to UNSIGNED INT which is what they should be, since you're actually storing integer values in there.

 

You should see the indexes working as planned once you alter the table to type the columns correctly.

[subhomoy]

Thanks

gizmola ur awesome...

But I've still some problem...

 

After creating the index when i query something, it definately reduces the rows number but still it searches from a large no of rows...

 

Before indexing

mysql> EXPLAIN SELECT country_name FROM ip2country WHERE 1388445696 BETWEEN begin_long_ip AND end_long_ip
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | ip2country | ALL  | NULL          | NULL | NULL    | NULL | 135497 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.03 sec)                                                            ^
                                                                                   |-----------------------------   (Large rows number)

After indexing...

mysql> EXPLAIN SELECT country_name FROM ip2country WHERE 1388445696 BETWEEN begin_long_ip AND end_long_ip;
+----+-------------+------------+-------+-----------------+-----------------+---------+------+-------+-----------------------+
| id | select_type | table      | type  | possible_keys   | key             | key_len | ref  | rows  | Extra                 |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+-------+-----------------------+
|  1 | SIMPLE      | ip2country | range | both_long_index | both_long_index | 4       | NULL | 15637 | Using index condition |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)                                                                          ^
                                                                                                  |----------------------------- (smaller but still large)

Is there any chance i can reduce the number of rows...

 

I have indexed the the two column..

mysql> CREATE INDEX both_long_index ON ip2country(begin_long_ip,end_long_ip);
Query OK, 135497 rows affected (0.64 sec)
Records: 135497  Duplicates: 0  Warnings: 0

Thanks for the reply...
 

Really u r very helpfull....

 

Thank u...

[subhomoy]

please any1 help me....

[gizmola]

Try dropping the combined index, and instead have 2 separate indexes on begin_long_ip and end_long_ip.

[phpPeter]

Your query is performing a range scan. 

 

 

EXPLAIN select * FROM ip2country WHERE 3084727327 BETWEEN begin_long_ip AND end_long_ip;

 

Let rearrange your query:

 

2 BETWEEN a AND b

means:

 

a <= 2   AND   b >= 2

 

 

Your index consists out of two cols: a and b. So it will look like

 

(a,b)

(0, 0)

(0, 1)

(0, 2)

(0, 3)

(0, 4)

(1, 0)

(1, 1)

(1, 2)

(1, 3)

(1, 4)

(2, 0)

(2, 1)

(2, 2)

(2, 3)

(2, 4)

(3, 0)

(3, 1)

(3, 2)

(3, 3)

(3, 4)

(4, 0)

(4, 1)

(4, 2)

(4, 3)

(4, 4)

 

An index can be queried top down. It must run down to find all matching entries and can stop at (3,0), because here a will be greater 2.

 

I don't know, if it is supported by mySQL, but try following index:

 

CREATE INDEX i2 ON ip2country(begin_long_ip,end_long_ip DESC);

 

This will reduce the range for the scan.

 

If no descending sorted columns are possible in mySQL, it is getting difficult. Then you should explain more detailed, what's the idea behind the query to figure out some workarounds.