Optimizing sql query containing function in where clause

Question

I want to check whether an IP exist in a database after it has been generated.My code works but it takes a whole 6 seconds to execute the following function 50,000 times. I am on PHP 7 (MySQL 5.7.11) but i will use mariadb in production.

How can i modify the sql to make it execute faster

function ip_exist ($db,$ipv6_address) {

    $db->query("SELECT ipv6 FROM tbl_ipv6 WHERE ipv6 = inet6_aton(:ip)  LIMIT 1");
    $db->bind(':ip', $ipv6_address);
    $db->single();
    if ($db->rowCount() > 0) {
        return true;
    } 
    return false;
}

SQL

    CREATE TABLE `tbl_ipv6` (
      `ipv6` varbinary(16) NOT NULL,
      `email_id` varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `tbl_ipv6`
  ADD PRIMARY KEY (`ipv6`);

No of rows in table : 4

Update 2 Query becomes the following (Thanks Bernd Buffen)

$db->query("SELECT ipv6 FROM tbl_ipv6 WHERE ipv6 = inet6_aton(:ip)");

Since you're just checking that an IP address exists you can also just do SELECT 1 FROM ... and check whether the result is true/false - this will have much less of an effect than adding the appropriate index though. — CD001
– CD001, Commented May 20, 2016 at 9:47

Bernd Buffen · Accepted Answer · 2016-05-20 09:45:38Z

1

Create a INDEX on this Column:

ALTER TABLE tbl_ipv6
ADD KEY (ipv6);

now try again:

if the ips are unique you can set the key unique.

answered May 20, 2016 at 9:45

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CD001 Over a year ago

@user2650277 a primary key is an index - you don't need both

Bernd Buffen Over a year ago

tell a bit more. Which engine you are using. how many records in the table, please post the output from SHOW CREATE TABLE tbl_ipv6; and which Version you using

Bernd Buffen Over a year ago

and please post also the output from : EXPLAIN SELECT ipv6 FROM tbl_ipv6 WHERE ipv6 = inet6_aton(:ip) LIMIT 1. NOTE: if you have the Primary key on ipv6 or a unique key you dont use LIMIT 1. there is only one record

user2650277 Over a year ago

@BerndBuffen i added your changes but improvement isn't significant...check post for updates

Collectives™ on Stack Overflow

Optimizing sql query containing function in where clause

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related