CODEWITHSUNDEEP
mysqlperformanceoptimization
Abdul Manaf
Abdul Manaf

Reputation: 4888

Is there any other way to write this Query

Below query executed in 18 minutes

SELECT COUNT(*) FROM psverify_interaction_numeric_ip_address a
WHERE EXISTS (
  SELECT 1 FROM Xwalk_GeoLiteCity_Blocks
  WHERE startIpNum <= a.numeric_ip_address AND endIpNum >= a.numeric_ip_address 
);
+----------+
| COUNT(*) |
+----------+
|      240 |
+----------+
1 row in set (18 min 2.00 sec)

How can i rewrite it to optimize.

Upvotes: 0

Views: 62

Answers (3)

Bohemian
Bohemian

Reputation: 425063

You have a correlated subquery; they must be executed for every row , so they are usually terrible performers.

Instead use a join and COUNT(DISTINCT):

SELECT COUNT(DISTINCT a.id)
FROM psverify_interaction_numeric_ip_address a
JOIN Xwalk_GeoLiteCity_Blocks b
  ON a.numeric_ip_address BETWEEN startIpNum AND endIpNum

The DISTINCT is needed in case there are multiple matching rows and you want to count each row only once.

Note also the use of BETWEEN for clarity.

Upvotes: 1

Jason Heo
Jason Heo

Reputation: 10246

Assuming psverify_interaction_numeric_ip_address.id is PK or UNIQUE..

SELECT COUNT(a.id)
FROM psverify_interaction_numeric_ip_address a
INNER JOIN Xwalk_GeoLiteCity_Blocks b
  ON b.startIpNum <= a.numeric_ip_address AND b.endIpNum >= a.numeric_ip_address

Also you need to make :

`ALTER TABLE Xwalk_GeoLiteCity_Blocks
    ADD INDEX(startIpNum),
    ADD INDEX (endIpNum)

Upvotes: 0

uvais
uvais

Reputation: 416

you can inner join tables if you sure that the table is exist. i am not sure, but hopefully this should work , try this:

SELECT COUNT(*)
FROM psverify_interaction_numeric_ip_address a
INNER JOIN Xwalk_GeoLiteCity_Blocks b
ON b.startIpNum <= a.numeric_ip_address AND b.endIpNum >= a.numeric_ip_address

Upvotes: 0

Related Questions