mdewyer Posted November 9, 2010 Share Posted November 9, 2010 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! Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted November 9, 2010 Share Posted November 9, 2010 1 Quote Link to comment Share on other sites More sharing options...
MadTechie Posted November 9, 2010 Share Posted November 9, 2010 or 3 list ever record and have PHP compare both fields! Na.. that would be slow.. first option Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted November 9, 2010 Share Posted November 9, 2010 yeah, both fields are indexed. mysql would not be looping over every record. Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 9, 2010 Share Posted November 9, 2010 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? Quote Link to comment Share on other sites More sharing options...
rwwd Posted November 9, 2010 Share Posted November 9, 2010 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 Quote Link to comment Share on other sites More sharing options...
mdewyer Posted November 9, 2010 Author Share Posted November 9, 2010 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! Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted November 9, 2010 Share Posted November 9, 2010 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. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted November 9, 2010 Share Posted November 9, 2010 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. Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted November 9, 2010 Share Posted November 9, 2010 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! Quote Link to comment Share on other sites More sharing options...
rwwd Posted November 9, 2010 Share Posted November 9, 2010 @PFMaBiSmAd cheers for the explanation. I knew I was correct with what I had said. Rw Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted November 9, 2010 Share Posted November 9, 2010 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... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.