CODEWITHSUNDEEP
phpmysql
user2650277
user2650277

Reputation: 6739

Optimizing sql query containing function in where clause

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)");

Upvotes: 0

Views: 28

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

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.

Upvotes: 1

Related Questions