Search code examples
phpmysqlcookiescomparison

PHP if comparison vs MySQL Where (Which is more efficient)

My situation: My website will look at a cookie for a remember me token and a user ID. If the cookie exists it will unhash it and look up the user ID and compare the token. with a "WHERE userid = '' and rememberme = ''".

My question is: Will MySQL optimize this query on the unique userid so that the query does not scan the entire database for this 20+ character token? Or instead should I just select the token from the database and then use a php if comparison to check if the tokens are the same?

In short (tl;dr): Would it be better to check if a token matches in with a MySQL select query, or to grab all the tokens from a databases database and compare the values with a php if conditional?

Thanks!

Solution

  • Simple answer:

    YES, the database will definitely optimism your search AS LONG AS THE variable you are searching in the WHERE ... portion is indexed! You definitely should not retrieve all the information via SQL and then do a PHP conditional if you are worried about performance.

    So if the id column in your table is not indexed, you should index it. If you have let say... 1 million rows already in your table and run a command like SELECT * FROM user WHERE id = 994321, you would see a definite increase in performance.

    Elaborating:

    A database (like MySQL) is made to be much faster at executing queries/commands than you would expect that to happen in php for instance. In your specific situation, lets say you are executing this SQL statement:

    $sql = "SELECT * FROM users WHERE id = 4";

    If you have 1 million users, and the id column is not indexed, MySQL will look through all 1 million users to find all the rows with id = 4. However, if it is indexed, there is something called a b tree that MySQL makes (behind the scenes) which works similarly to how the indexing of a dictionary work.

    If you try to find the world slowly in a dictionary, you might open the book in the middle, find words that start with the letter M and then look in the middle again of the pages on your right side hoping to find a letter closer to S. This method of looking for a word is much faster than looking at each single page from the beginning 1 by 1.

    For that very reason, MySQL has created indexes to help performance and this feature should definitely be taken advantage of to help increase the speed of your queries.