PHP or MySQL faster?

[mdewyer]

Say I have a MySQL table with two fields: "id" (primary key, INT(7)) and "hash" (indexed, VARCHAR(32)), and there will be hundreds of thousands of records in this table. Would it be faster / less CPU intensive to use MySQL to match both the id and hash fields to cleaned / sanitized request vars, or just match the id field with MySQL, and then use PHP to determine whether or not the hash is correct?

 

So,

 

mysql_query("SELECT * FROM table WHERE id='" .$id. "' AND hash=' .$hash. '");

or:

 

$query = mysql_query("SELECT * FROM table WHERE id='" .$id. "'");
$row = mysql_fetch_assoc($query);
if ($row['hash'] == $hash) ..

One scenario you're requiring MySQL to match two things (the hash field would be 32 characters long, like a md5 string), the other way it would only have to find one field (a unique, primary key integer, which should be very fast), but MySQL would need to pull the data and put into an array that PHP can then read to determine if the other field is a match. I'm thinking the all-MySQL way would be faster, but I didn't know about it having to search through hundreds of thousands of records for a string of that size.

 

Any thoughts? Thanks!

[BlueSkyIS]

1

[MadTechie]

or 3

list ever record and have PHP compare both fields!

 

Na.. that would be slow.. first option

[BlueSkyIS]

yeah, both fields are indexed. mysql would not be looping over every record.

[Mchl]

You can gain some performance. by changing datatype to CHAR(32) - will save you a byte or two on every row, which will mean MySQL will be able to load more row into memory. Also make sure you're indexing all 32 characters of hash column.

 

Whether or not the `hash` column is indexed will make no difference. Since we're selecting a single row using a primary key column, MySQL will lookup a PK only.

If however there are more columns in this table, but you only need these two AND the table is InnoDB, then an index on `hash` column will help (because it will in fact be a composite index over (`id`,`hash`) and query like SELECT id FROM table WHERE id = ? AND hash = ? will lookup index only.

 

Another thing to consider is to store hashes in binary form (as returned by PHP's md5 function, with second argument set to true). So instead of CHAR(32) you can use BINARY(16) field - 16 bytes per row saved, but you must be a bit more careful with setting your connection encoding.

 

P.S. And I supose you do know, that INT(7) makes no sense?

[rwwd]

mysql_query("SELECT * FROM `table` WHERE `id` = " .$id. "  AND `hash` = '".$hash."' ");

 

If id is a numerical value, don't put the quotes around it, else this will cause issues, only quote chars.  Also it's worth building the sql outside the function so that debugging it as a string is an easier task to to!

 

Rw

[mdewyer]

Thanks for the replies and input guys! I just wanted to clarify that the code I showed was "semi-pseudo" and I was just trying to better illustrate the question (faster to select through MySQL, or get PHP involved too). I wasn't too worried about the correctness of it, but I do appreciate the corrections anyhow And yeah, I understand that the "7" part of the INT has to do with the "display width" of the field with zero's and what not. Sorry for including that.

 

I think with your feedback and some good planning, a db table should be able to handle this quite easily all by itself, and that's what I needed to know!

 

Thanks!

[BlueSkyIS]

 

If id is a numerical value, don't put the quotes around it, else this will cause issues, only quote chars.

 

Rw

 

I disagree with "sometimes use quotes, sometimes do not". I say: ALWAYS use quotes.

 

What problems are caused by quoting numbers??

 

UPDATE: THIS IS BAD ADVICE. SEE DISCUSSION BELOW.

[PFMaBiSmAd]

Mysql converts a quoted number to a FLOAT. If the number happens to be a decimal, you can end up with an unintended floating point conversion error. It also causes more processor cycles due to the first conversion to a float, then the conversion to the actual data type of the field.

[BlueSkyIS]

Mysql converts a quoted number to a FLOAT. If the number happens to be a decimal, you can end up with an unintended floating point conversion error. It also causes more processor cycles due to the first conversion to a float, then to the actual data type of the field.

 

Now I know. Thank you for clarifying this, and I will change my habits accordingly!

[rwwd]

@PFMaBiSmAd cheers for the explanation. I knew I was correct with what I had said.

 

Rw

[BlueSkyIS]

Thank you for pointing out the problem, rwwd. I learn so much here, often ways to break bad habits that I didn't know I have.

 

I guess it is unfortunate that every bit of code I have written for the last 10+/- years has quoted numeric values...