Search and compare list of ever-growing IPs
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
ipcolumn. 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.
mysql
asked Jun 26 '15 at 11:27
user2994883user2994883
111
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 1 more comment
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
ipcolumn. 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.
mysql
asked Jun 26 '15 at 11:27
user2994883user2994883
111
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Indexing is fine. I suggest you make it auniqueindex. You could also don't bother checking first and useINSERT ... ON DUPLICATE KEY UPDATE ...syntax instead. But that may not be appropriate for your case (if you need to check the IPs often.)
– ypercubeᵀᴹ
Jun 26 '15 at 11:30
They do need to be checked fairly often, so far about once every 5 seconds. Theipcolumn is set tounique.
– user2994883
Jun 26 '15 at 11:32
Is the column namediporcolumn_client_ip?
– ypercubeᵀᴹ
Jun 26 '15 at 11:34
It's namedipI just named itcolumn_client_ipto make sure people knew what I was talking about in the post.
– user2994883
Jun 26 '15 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ᵀᴹ
Jun 26 '15 at 11:37
|
show 1 more comment
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
ipcolumn. 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.
mysql
asked Jun 26 '15 at 11:27
user2994883user2994883
111
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
ipcolumn. 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.
mysql
mysql
asked Jun 26 '15 at 11:27
user2994883user2994883
111
asked Jun 26 '15 at 11:27
user2994883user2994883
111
asked Jun 26 '15 at 11:27
user2994883user2994883
111
asked Jun 26 '15 at 11:27
user2994883user2994883
111
asked Jun 26 '15 at 11:27
user2994883user2994883
111
111
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Indexing is fine. I suggest you make it auniqueindex. You could also don't bother checking first and useINSERT ... ON DUPLICATE KEY UPDATE ...syntax instead. But that may not be appropriate for your case (if you need to check the IPs often.)
– ypercubeᵀᴹ
Jun 26 '15 at 11:30
They do need to be checked fairly often, so far about once every 5 seconds. Theipcolumn is set tounique.
– user2994883
Jun 26 '15 at 11:32
Is the column namediporcolumn_client_ip?
– ypercubeᵀᴹ
Jun 26 '15 at 11:34
It's namedipI just named itcolumn_client_ipto make sure people knew what I was talking about in the post.
– user2994883
Jun 26 '15 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ᵀᴹ
Jun 26 '15 at 11:37
|
show 1 more comment
Indexing is fine. I suggest you make it auniqueindex. You could also don't bother checking first and useINSERT ... ON DUPLICATE KEY UPDATE ...syntax instead. But that may not be appropriate for your case (if you need to check the IPs often.)
– ypercubeᵀᴹ
Jun 26 '15 at 11:30
They do need to be checked fairly often, so far about once every 5 seconds. Theipcolumn is set tounique.
– user2994883
Jun 26 '15 at 11:32
Is the column namediporcolumn_client_ip?
– ypercubeᵀᴹ
Jun 26 '15 at 11:34
It's namedipI just named itcolumn_client_ipto make sure people knew what I was talking about in the post.
– user2994883
Jun 26 '15 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ᵀᴹ
Jun 26 '15 at 11:37
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ᵀᴹ
Jun 26 '15 at 11:30
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ᵀᴹ
Jun 26 '15 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
Jun 26 '15 at 11:32
They do need to be checked fairly often, so far about once every 5 seconds. The
ip column is set to unique.– user2994883
Jun 26 '15 at 11:32
Is the column named
ip or column_client_ip?– ypercubeᵀᴹ
Jun 26 '15 at 11:34
Is the column named
ip or column_client_ip?– ypercubeᵀᴹ
Jun 26 '15 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
Jun 26 '15 at 11:35
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
Jun 26 '15 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ᵀᴹ
Jun 26 '15 at 11:37
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ᵀᴹ
Jun 26 '15 at 11:37
|
show 1 more comment
1 Answer
1
active
oldest
votes
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
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f105215%2fsearch-and-compare-list-of-ever-growing-ips%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
add a comment |
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
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
add a comment |
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
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
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
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
answered Jun 26 '15 at 15:49
oNareoNare
2,67921231
2,67921231
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f105215%2fsearch-and-compare-list-of-ever-growing-ips%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Indexing is fine. I suggest you make it a
uniqueindex. You could also don't bother checking first and useINSERT ... ON DUPLICATE KEY UPDATE ...syntax instead. But that may not be appropriate for your case (if you need to check the IPs often.)– ypercubeᵀᴹ
Jun 26 '15 at 11:30
They do need to be checked fairly often, so far about once every 5 seconds. The
ipcolumn is set tounique.– user2994883
Jun 26 '15 at 11:32
Is the column named
iporcolumn_client_ip?– ypercubeᵀᴹ
Jun 26 '15 at 11:34
It's named
ipI just named itcolumn_client_ipto make sure people knew what I was talking about in the post.– user2994883
Jun 26 '15 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ᵀᴹ
Jun 26 '15 at 11:37