PDA

View Full Version : Is this the most efficient way...?


Scenario
18/12/2011, 02:39 AM
I have this piece of code:

stock DoesEmailAddressExist(const emailaddress[])
{
new szQuery[300], iReturnVal;
format(szQuery, sizeof(szQuery), "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'", emailaddress);
mysql_query(szQuery);

mysql_store_result();
if(mysql_num_rows() > 0)
{
mysql_free_result();
iReturnVal = 1;
}
else if(mysql_num_rows() == 0)
{
mysql_free_result();
iReturnVal = 0;
}
return iReturnVal;
}

When players register on my server, their email address is stored. When new players register, I don't want them using the same email address. Therefore, this function is there to check if the email address is already saved in the DB. Now, my question is; Is the query used in this function the most efficient way to determine if the address exists, or is there an actual syntax for this?

[HiC]TheKiller
18/12/2011, 02:45 AM
I don't see any issue with it. You could possibly add it to another query that checks something else too.

Calgon
18/12/2011, 02:47 AM
Theoretically, this should work: (credits) (http://www.petefreitag.com/item/169.cfm)


SELECT COUNT('Email Address') AS NumOccurrences FROM Accounts HAVING ( COUNT(NumOccurrences) >= 1 )


(If that doesn't work, it's because of the two speech marks in the COUNT area, it works fine if you use one word without speech marks.)

You can just return the result, 0 if no, higher (or equal) 1 if so.

Also, a little tip: It's really not wise to use spaces for column names, it's more problematic. Use camel case.

Oh yeah, this code will only check replicates in the database already, not what criteria you specify.

Scenario
18/12/2011, 02:54 AM
Theoretically, this should work: (credits) (http://www.petefreitag.com/item/169.cfm)


SELECT COUNT('Email Address') AS NumOccurrences FROM Accounts HAVING ( COUNT(NumOccurrences) >= 1 )


(If that doesn't work, it's because of the two speech marks in the COUNT area, it works fine if you use one word without speech marks.)

You can just return the result, 0 if no, higher (or equal) 1 if so.

Also, a little tip: It's really not wise to use spaces for column names, it's more problematic. Use camel case.

Wouldn't the second query there be better?

SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )

And thanks for the tip, I always wondered about that!

Calgon
18/12/2011, 02:59 AM
Wouldn't the second query there be better?

SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )

And thanks for the tip, I always wondered about that!

That only checks if there's 1 result, mine checks if there's 1 or more.

Scenario
18/12/2011, 03:01 AM
That only checks if there's 1 result, mine checks if there's 1 or more.

Oh okay, but I think I'll only need the one that check's if there's one result.

Calgon
18/12/2011, 03:04 AM
Never hurts to be safe. Except in some cases...

Scenario
18/12/2011, 03:06 AM
Never hurts to be safe. Except in some cases...

You pose a good point there! Thanks again Calgon. :)

One more question, is it okay to use spaces in table names, or should I avoid spaces in MySQL as much as possible?

Lorenc_
18/12/2011, 03:06 AM
So, you're creating a string 300 cells, "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'" = 67 + A normal email length = 30 + a null character = 98

You just need 98 characters, save some memory realcop! You can round off the 98 to 100 if you want

Scenario
18/12/2011, 03:08 AM
So, you're creating a string 300 cells, "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'" = 67 + A normal email length = 30 + a null character = 98

You just need 98 characters, save some memory realcop! You can round off the 98 to 100 if you want

Damn. I knew someone was going to say something about my cell size! I'll change it here in a few, I actually have several strings where the cell sizes are gigantic and could probably be down-sized a few hundred cells.

By the way, is 30 characters really the average length of an email address?

Calgon
18/12/2011, 03:09 AM
One more question, is it okay to use spaces in table names, or should I avoid spaces in MySQL as much as possible?

I'd avoid it as much as possible. Camel case is a good naming convention and should suffice.

So, you're creating a string 300 cells, "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'" = 67 + A normal email length = 30 + a null character = 98

You just need 98 characters, save some memory realcop! You can round off the 98 to 100 if you want

The maximum length of an email address (combining the domain) is 255 characters, so RealCop's code is adequate.

Scenario
18/12/2011, 03:11 AM
I'd avoid it as much as possible. Camel case is a good naming convention and should suffice.

Oh okay, thanks... again! :)

The maximum length of an email address (combining the domain) is 255 characters, so RealCop's code is adequate.

You two confuse me. I knew the maximum length of an email address was around 255, but I've never come across an email address more than 150 characters. I wonder if it's a good idea to change the size from 150 to 256 in my MySQL structure...?

Lorenc_
18/12/2011, 03:12 AM
Damn. I knew someone was going to say something about my cell size! I'll change it here in a few, I actually have several strings where the cell sizes are gigantic and could probably be down-sized a few hundred cells.

By the way, is 30 characters really the average length of an email address?

Not sure, I've ******d what's the average email address length and got 28-30

I've corrected some of your code, optional if you want to use it but it surely does save you a 32 bit variable!


stock DoesEmailAddressExist(const emailaddress[])
{
static
szQuery[100]; //Since you're going to be using this frequently, why not hit a 'static' on it :D

format(szQuery, sizeof(szQuery), "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'", emailaddress);
mysql_query(szQuery);
mysql_store_result();
return (mysql_num_rows() > 0 ? (1) : (2)), mysql_free_result();
}

EDIT: I saw calgon's post, but think about it, as if someone wants to submit a 255 long email address, that's absolutey insane. Your choice anyway!

Haydz
18/12/2011, 03:12 AM
Also you could try using "SELECT NULL" as you don't need the email address result, you're just checking if it exists.


format(szQuery, sizeof(szQuery), "SELECT NULL FROM `Accounts` WHERE `Email Address` = '%s' LIMIT 1", emailaddress);
mysql_query(szQuery);

not sure if LIMIT 1 is faster / slower then Calgon's way, but if you have the time to do a speed test that's one way to find out. I'd like to see the result also as that's the way I do my query's and if there's a faster way I'd defiantly change.

Scenario
18/12/2011, 03:14 AM
Not sure, I've ******d what's the average email address length and got 28-30

I've corrected some of your code, optional if you want to use it but it surely does save you a 32 bit variable!


stock DoesEmailAddressExist(const emailaddress[])
{
static
szQuery[100]; //Since you're going to be using this frequently, why not hit a 'static' on it :D

format(szQuery, sizeof(szQuery), "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'", emailaddress);
mysql_query(szQuery);
mysql_store_result();
return (mysql_num_rows() > 0 ? (1) : (2)), mysql_free_result();
}

I've never seen code like that before. I've seen static, but what's the purpose of that over "new?"

Calgon
18/12/2011, 03:14 AM
Oh okay, thanks... again! :)



You two confuse me. I knew the maximum length of an email address was around 255, but I've never come across an email address more than 150 characters. I wonder if it's a good idea to change the size from 150 to 256 in my MySQL structure...?

Well, you're going to exclude people that do have really long email addresses, despite the average, if you cut the size. The real size is 255 characters, but the average size is probably around 30 characters or so.

Not sure, I've ******d what's the average email address length and got 28-30

I've corrected some of your code, optional if you want to use it but it surely does save you a 32 bit variable!


stock DoesEmailAddressExist(const emailaddress[])
{
static
szQuery[100]; //Since you're going to be using this frequently, why not hit a 'static' on it :D

format(szQuery, sizeof(szQuery), "SELECT `Email Address` FROM `Accounts` WHERE `Email Address` = '%s'", emailaddress);
mysql_query(szQuery);
mysql_store_result();
return (mysql_num_rows() > 0 ? (1) : (2)), mysql_free_result();
}

EDIT: I saw calgon's post, but think about it, as if someone wants to submit a 255 long email address, that's absolutey insane. Your choice anyway!

The static is a bit pointless. There's no real benefits to using it IIRC.

However, the triadic statement is a lot more logical than your if compound with 'iReturnVal', though the stray mysql_free_result could potentially change the return value, so I'd re-order the code.

jamesbond007
18/12/2011, 03:18 AM
Never hurts to be safe. Except in some cases...
"except in some cases... " lol SQLI )))

Lorenc_
18/12/2011, 03:20 AM
I've never seen code like that before. I've seen static, but what's the purpose of that over "new?"

'static' creates the string once and doesn't recreate it after the function is called again, since you're going to be formatting it, quite frankly pointless to have it be recreated all the time. If you're wishing to reset the string, set it null underneath where you've created the variable such as:


static szTmp[30];
szTmp[0] = '\0';


'new' recreates a variable/array or whatever you choose after a function is called containing it.


SetTimer("Test", 50, true);
public Test()
{
static iTmp;
iTmp++;
printf("%d", iTmp);
}

You'll see what you get :), and to retest what 'new' does, just replace the static with new, simple

@calgon


new iResult = DoesEmailAddressExist("hi@horse.com");
if(iResult) { }


Well, I usually put the results given in new variables since I read somewhere that it's safer (apparently it was RyDeR` who said so)

Calgon
18/12/2011, 03:24 AM
'static' creates the string once and doesn't recreate it after the function is called again, since you're going to be formatting it, quite frankly pointless to have it be recreated all the time. If you're wishing to reset the string, set it null underneath where you've created the variable such as:


static szTmp[30];
szTmp[0] = '\0';


'new' recreates after a function is called containing it.


SetTimer("Test", 50, true);
public Test()
{
static iTmp;
iTmp++;
printf("%d", iTmp);
}

You'll see what you get :), and to retest what 'new' does, just replace the static with new, simple

Ah I see, but the static is still slightly pointless at any rate, because of how little this function will be called throughout the gamemode, it's wiser for memory to destroy it and create it again when needed.

Scenario
18/12/2011, 03:28 AM
@ Lorenc_ - Thanks for the explanation. I think that's a bit pointless for this function, but possibly for something else it would be nice.

------------------

Just because I wanted to see myself:

stock DoesEmailAddressExist(const emailaddress[])
{
new iStartTime = GetTickCount();
static
szQuery[300]; //Since you're going to be using this frequently, why not hit a 'static' on it :D

format(szQuery, sizeof(szQuery), "SELECT `emailAddress` FROM `Accounts` WHERE `emailAddress` = '%s'", emailaddress);
mysql_query(szQuery);
mysql_store_result();

new iStopTime = GetTickCount();
printf("\nDEBUG: Using Lorenc_'s way, process took %dms.", iStopTime-iStartTime);

return (mysql_num_rows() > 0 ? (1) : (2)), mysql_free_result();
}

stock DoesEmailAddressExist2(const emailaddress[])
{
new iStartTime = GetTickCount();
new szQuery[300];
format(szQuery, sizeof(szQuery), "SELECT COUNT('emailAddress') AS NumOccurrences FROM Accounts HAVING ( COUNT(NumOccurrences) >= 1 )", emailaddress);
mysql_query(szQuery);
mysql_store_result();

new iStopTime = GetTickCount();
printf("\nDEBUG: Using a piece of Lorenc_'s code, with Calgon's query, process took %dms.", iStopTime-iStartTime);

return (mysql_num_rows() > 0 ? (1) : (2)), mysql_free_result();
}

stock DoesEmailAddressExist3(const emailaddress[])
{
new iStartTime = GetTickCount();
new szQuery[300];
format(szQuery, sizeof(szQuery), "SELECT NULL FROM `Accounts` WHERE `emailAddress` = '%s' LIMIT 1", emailaddress);
mysql_query(szQuery);
mysql_store_result();

new iStopTime = GetTickCount();
printf("\nDEBUG: Using a piece of Lorenc_'s code, with Haydz's query, process took %dms.", iStopTime-iStartTime);

return (mysql_num_rows() > 0 ? (1) : (2)), mysql_free_result();
}

That's the code I used. These are the responses:

DEBUG: Using Lorenc_'s way, process took 93ms.

DEBUG: Using a piece of Lorenc_'s code, with Calgon's query, process took 92ms.

DEBUG: Using a piece of Lorenc_'s code, with Haydz's query, process took 92ms.

Unless I did something wrong, they're all obviously around the same processing time.

Calgon
18/12/2011, 03:30 AM
Speed isn't what you should be concerned about, using my query shortens the code a lot and requires less effort for the script to process, you should be concerned about which is the most logical.

Scenario
18/12/2011, 03:34 AM
Another good point from you Calgon. Thanks y'all, I really appreciate it! Final code:

stock DoesEmailAddressExist(const emailaddress[])
{
new szQuery[300];
format(szQuery, sizeof(szQuery), "SELECT COUNT('emailAddress') AS NumOccurrences FROM Accounts HAVING ( COUNT(NumOccurrences) >= 1 )", emailaddress);
mysql_query(szQuery);
mysql_store_result();
return (mysql_num_rows() > 0 ? (1) : (2)), mysql_free_result();
}

Lorenc_
18/12/2011, 03:37 AM
I got 1 ms with my own bench marking and 1.54 ms with Slice's bench mark code, quite fast :O

EDIT: Okay, I guess after all it's solved lul

EDIT2:

I just found out, using static makes your code more fast! Use it! LOL.

Calgon
18/12/2011, 03:39 AM
By the way, my query code only checks if there's existing duplicates of rows in the database, it doesn't check depending on the criteria you specify. Sorry, I should've been a bit more clear. I'm sure you can add a WHERE clause though.

Scenario
18/12/2011, 03:40 AM
I got 1 ms with my own bench marking and 1.54 ms with Slice's bench mark code, quite fast :O

EDIT: Okay, I guess after all it's solved lul

Is your MySQL database on your localhost?

By the way, my query code only checks if there's existing duplicates of rows in the database, it doesn't check depending on the criteria you specify. Sorry, I should've been a bit more clear. I'm sure you can add a WHERE clause though.

Ah, okay, thanks.

Lorenc_
18/12/2011, 03:42 AM
Is your MySQL database on your localhost?



Ah, okay, thanks.

Yes it is, seamlessly, indeed static did modify the speed of the code by about 0.20 MS :O

After all the thread is solved, /me looks for another thread to help.