I want to get location from members IP. I can use the command on phpmyadmin, and it costs about 2 seconds. But it will be stuck when the command is executed on MySQL through terminal. How can I fix the problem.
SQL Command:
SELECT
ID,country
FROM
(SELECT ID,substring_index(members.server_ids,',',1) AS IP FROM members) AS A
JOIN
(SELECT country,start,end FROM ip) AS B
ON
INET_ATON(A.IP) BETWEEN INET_ATON(B.start) AND INET_ATON(B.end);
desc members:
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| USER_NAME | varchar(100) | NO | UNI | NULL | |
| PASSWORD | varchar(32) | YES | | NULL | |
| NAME | varchar(100) | YES | | NULL | |
| EMAIL | varchar(50) | YES | MUL | NULL | |
| SAFE_EMAIL | varchar(50) | YES | | NULL | |
| NICK_NAME | varchar(100) | YES | MUL | NULL | |
| GENDER | tinyint(1) | YES | | 0 | |
| BIRTHDAY | date | YES | | NULL | |
| CREATEDAY | datetime | YES | | NULL | |
| PHONE | varchar(20) | YES | | NULL | |
| MOBILE | varchar(15) | YES | | NULL | |
| ID_CARD | varchar(32) | YES | | NULL | |
| COUNTY | int(11) | YES | | NULL | |
| ADDRESS | varchar(255) | YES | | NULL | |
| LOGIN_TIME | datetime | YES | | NULL | |
| BUY_SN | varchar(50) | YES | | NULL | |
| BUY_KIND | varchar(50) | YES | | NULL | |
| PARTNER_SN | varchar(50) | YES | | NULL | |
| cookie_value | varchar(100) | YES | | NULL | |
| CONSIGNEE_INFO | int(11) | YES | MUL | NULL | |
| LOGIN_COUNT | int(11) | YES | | NULL | |
| PAY_COUNT | int(11) | YES | | NULL | |
| status | tinyint(4) | YES | | 1 | |
| experience | int(11) | YES | | 0 | |
| amount | int(11) | YES | | 0 | |
| score | int(11) | YES | | 0 | |
| is_facebook_account | tinyint(4) | YES | | 0 | |
| avatar | varchar(200) | YES | | NULL | |
| avatar_status | tinyint(1) | YES | | 0 | |
| member_authhash | varchar(50) | YES | | NULL | |
| job | varchar(50) | YES | | NULL | |
| server_ids | varchar(255) | YES | | NULL | |
+---------------------+--------------+------+-----+---------+----------------+
SELECT ID,server_ids FROM members LIMIT 10000,1;
+-------+--------------------------+
| ID | server_ids |
+-------+--------------------------+
| 20005 | 61.20.167.219, 127.0.0.1 |
+-------+--------------------------+
desc ip:
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| country | char(2) | NO | | NULL | |
| type | char(4) | NO | | NULL | |
| start | varchar(15) | NO | MUL | NULL | |
| end | varchar(15) | NO | MUL | NULL | |
+---------+------------------+------+-----+---------+----------------+
SELECT * FROM ip LIMIT 100,1;
+-----+---------+------+-----------+---------------+
| id | country | type | start | end |
+-----+---------+------+-----------+---------------+
| 101 | IN | ipv4 | 1.187.0.0 | 1.187.255.255 |
+-----+---------+------+-----------+---------------+
phpMyAdmin automatically adds a LIMIT clause to the query. Because you do not have an ODER BY clause, MySQL sends the results as soon as it has found the number of records that you requested in the LIMIT clause.
Add a LIMIT clause to the query that you execute on the command line.