[WARNING] Question requires ULTRA PRO GODLIKE MYSQL MASTER![/WARNING]

[this.user]

all i want to do is run a query to test whether or not a user exists.

 

Which one of these is best:

 

#1

$r = mysql_query("SELECT id FROM user WHERE usr= 'tom' AND pwd= 'tom'");
if (mysql_num_rows($r) == 1)
{
//exists
}

#2

$r = mysql_query("SELECT COUNT(*) FROM user WHERE usr= 'tom' AND pwd= 'tom'");
$list = mysql_fetch_array($r)
if ($list['COUNT(*)'] == 1)
{
//exists
}

#3

$r = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM user WHERE usr= 'tom' AND pwd= 'tom'");
$r = mysql_query('Select FOUND_ROWS( );');
if (mysql_num_rows($r) == 1)
{
//exists
}

 

On the other hand I was also thinking about just doing there instead of a select, I just try to update the row. could this be faster?

 

$r = mysql_query("UPDATE user  SET date_stamp=NOW()WHERE username = 'tom' AND pwd= 'tom'");
if (mysql_affected_rows() > 0)
{
//exists
}

 

If there exists better practices than what I mentioned please do share it, but please help me settle this now and for ever!

[trq]

#2 is probably the best option though you will want a LIMIT clause on your query and in reality, #1 is probably just as efficient (it also wants a LIMIT clause though).

 

However, if you need to update the date_stamp anyway (as in example #4) you may as well get it all over and done with in one action.

 

Of course, this is just my opinion. I am in no way "ULTRA PRO GODLIKE MYSQL MASTER!" and I very much doubt you'll find anyone here owning up to that title. Unless of course they are some complete twat.

[Mchl]

None of the three. Use #1 with LIMIT 1

And if you're doing this update anyway,  #4 is your best option.

 

[ignace]

None of the three. Use #1 with LIMIT 1

And if you're doing this update anyway,  #4 is your best option.

 

I concur, you may want to save the id somewhere (like $_SESSION) after they have logged in to verify they are in fact logged in. And you don't need an ULTRA PRO GODLIKE MYSQL MASTER for this as someone with a few months experience could tell you this too.

[this.user]

Well you guys make it sound so simple, but after doing some research, people are saying SQL_CALC_FOUND_ROWS is 10x faster the #1.

 

Does anyone actually know the benchmarks behind these queries?

[Mchl]

Do the benchmark yourself and you'll know for sure. Though I can't imagine how sending two queries to database might be faster than counting number of rows already stored in memmory. And besides, is 10 times faster than 0.001s really worth it?

 

Here's some reading:

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

[ignace]

Well you guys make it sound so simple, but after doing some research, people are saying SQL_CALC_FOUND_ROWS is 10x faster the #1.

 

I don't think so, if you are using MyISAM as an engine and performing a count(*) instantly returns the number of records as MyISAM stores the number of records as-is and thus only has to retrieve the variable value without even having to go over the table and count each record.

 

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

 

This optimization applies only to MyISAM  tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

[this.user]

Alright what about SELECT vs UPDATE, does anyone know what uses less resources and which one executes faster?

[ignace]

Alright what about SELECT vs UPDATE, does anyone know what uses less resources and which one executes faster?

 

SELECT, MySQL is optimized for SELECT queries.

[this.user]

thank you all, i really do appreciate the help you all provided it.

[Mchl]

I don't think so, if you are using MyISAM as an engine and performing a count(*) instantly returns the number of records as MyISAM stores the number of records as-is and thus only has to retrieve the variable value without even having to go over the table and count each record.

 

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

 

But there is a WHERE caluse in this case, so this does not apply. This applies only to situation when you want to get a total number of rows in the table.

 

And how can a database be optimised for SELECT queries? That's like saying a car is better at turning left.

[ignace]

And how can a database be optimised for SELECT queries?

 

I thought of having read that somewhere that MySQL was primarily optimized for SELECT queries, but now I am doubting if it was MySQL or PostGreSQL that they mentioned...

 

I read so many articles and sometimes forget to verify the source while fragments of it linger in my mind to create havoc and destruction every now and then Luckily we have Daniel and Mchl to rectify any wrongs.

 

Edit: I can't find the article anymore so it's most likely to be false.

[Mchl]

I would certainly like to see that source. SELECTs in ganeral ARE faster, because in many cases they do not require disk writes (or even reads), they do not force indexes to be modified, and last but not least, they can use query cache. This applies to all databases, but I wouldn't call it an optimisation. It's just that SELECTs need less resources than other DML statements.