Jump to content

PHP or MySQL faster?


mdewyer

Recommended Posts

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!

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.