CODEWITHSUNDEEP
mysql
Rizwan Ranjha
Rizwan Ranjha

Reputation: 380

How to improve MySQL query execution time

I am having an issue that with my query, it is taking around 14 seconds to execute, and I need it to be faster.

Is there anyway I can optimize it?

SELECT *
FROM large_table
WHERE (
INET_ATON( 'record number' )
BETWEEN INET_ATON( starting_number )
AND INET_ATON( ending_number )
)
LIMIT 0 , 30

Upvotes: 1

Views: 70

Answers (2)

Rizwan Ranjha
Rizwan Ranjha

Reputation: 380

Thank you Guys ! I have done research on Alex link and it was great solution but I can't add integers to my all 4 million IP Address records as it was something time consuming. I have solved my specific issue with http://php.net/explode and I just explode IP address and then remove last number and search on varchar based column and my query get results in less than a second. I am currently going with this solution. Thanks All

Upvotes: 1

BigBlackQuery
BigBlackQuery

Reputation: 8

Even if the the column used as criteria/filter is indexed it would still result in FULL SCAN of table because of INET_ATON() function. try this.

    SELECT INET_ATON(`record number`)
    FROM large_table
    WHERE 
    'record number' 
    BETWEEN starting_number 
    AND ending_number 
    LIMIT 0 , 30

Upvotes: 0

Related Questions