2

I'm a newbie when it comes to mysql things and queries in general, just wanted to point that out.

I'll keep this as short as I can.

I have a table where I track the IPs of users that enter the front-page of a site. Any other page is not logged.

I have a unique ID for that table set as the IP of the client.

Every time a new user hits the front-page I have to look-up his IP in the table to make sure his IP is not there, if it's not it gets inserted, if it is, nothing happens.

So far this is working great, but then again, there aren't many IPs in there.

Since I'm using Laravel's Eloquent, I can't give you the precise query, in PHP it looks like this:

Tracker::where('ip', $client_ip)->first();

Although I'm not sure, I suspect that the raw query looks something like this:

SELECT ip FROM table_name WHERE column_client_ip = '127.0.0.1' LIMIT 1

The IP itself is saved as a varchar. I know about INET_ATON but I'm not exactly sure if I can use it with ipv6.

My questions are as follows:

  • I know that searching and comparing varchar type fields isn't the fastest thing in the world, do I have any other options to save IP? Keep in mind that they can also tunnel their IP which, in case I'm not wrong, it adds to the total length of the string I get as the IP - I could be way off here, my sysadmin skills are basically non-existent.
  • Currently the search is really fast, mostly because there aren't a lot of records and I also keep an index on the ip column. Realistically speaking, at what point should I start being concerned that the search/compare will slow down significantly?

If you guys have any other suggestions or want to warn be about possible issues I may have in the future with the current setup, I'm all ears.

Improve this question
7
  • Indexing is fine. I suggest you make it a unique index. You could also don't bother checking first and use INSERT ... ON DUPLICATE KEY UPDATE ... syntax instead. But that may not be appropriate for your case (if you need to check the IPs often.)
    – ypercubeᵀᴹ
    Commented Jun 26, 2015 at 11:30
  • They do need to be checked fairly often, so far about once every 5 seconds. The ip column is set to unique.
    – user2994883
    Commented Jun 26, 2015 at 11:32
  • Is the column named ip or column_client_ip?
    – ypercubeᵀᴹ
    Commented Jun 26, 2015 at 11:34
  • It's named ip I just named it column_client_ip to make sure people knew what I was talking about in the post.
    – user2994883
    Commented Jun 26, 2015 at 11:35
  • Well, if it's one column, use one name everywhere, in the query and the question. But the query should be fine. With the unique index, it should be able to run fast, even with millions of rows.
    – ypercubeᵀᴹ
    Commented Jun 26, 2015 at 11:37

1 Answer 1

Reset to default
-1

I'm doing this at work. I use INET_ATON() to IPv4 and INET6_ATON() to IPv6. This search is faster than VARCHAR fields search. But, one important point:

This function was added in MySQL 5.6.3.

INET_ATON() Return the numeric value of an IP address.

INET_NTOA() Return the IP address from a numeric value.

INET6_ATON() Return the numeric value of an IPv6 address.

INET6_NTOA() Return the IPv6 address from a numeric value.

Documentation: MySQL Misc

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.