PHP: MySQL Select Query vs String Operation

Question

I have a database and a string with about 100,000 key / value-pair records and I want to create a function to find the value.

Is it better to use a string or a database, considering performance (page load time) and crash safety? Here are my two code examples:

1.

echo find("Mohammad");

function find($value){
    $sql = mysql_query("SELECT * FROM `table` WHERE `name`='$value' LIMIT 1");
    $count = mysql_num_rows($sql);
    if($count > 0){
        $row = mysql_fetch_array($sql);
        return $row["family"];
    } else {
        return 'NULL';
    }
}

2.

$string = "Ali:Golam,Mohammad:Offer,Reza:Now,Saber:Yes";

echo find($string,"Mohammad");

function find($string,$value){
    $array = explode(",",$string);
    foreach($array as $into) {
        $new = explode(":",$into);
        if($new[0] == $value) {
            return $new[1];
            break;
        }
    }
}

But a string of 100,000 names in memory is going to be painfully difficult to change (especially in code), and take a large chunk of PHP memory as well.... so while it might seem faster, it's going to cause a lot of problems elsewhere in your system.... if you need to change details of the names in your string, or add new names/delete old ones, then you should be using a database — Mark Baker, Mar 18 '15 at 09:22
But stop using the old, deprecated MySQL extension, and start using MySQLi or PDO with prepared statements/bind variables — Mark Baker, Mar 18 '15 at 09:24
I think, you need to put your question here : http://codereview.stackexchange.com/ — Jenis Patel, Mar 18 '15 at 09:24
@JenisPatel no, i think this is a legitimate coding question. the question is perfomance and crash safety of databases vs string operations — Roman Holzner, Mar 18 '15 at 10:32
For MySql is just a trivial task, while for php you are close to machine's limit, if not over it. In your 'table' you should index column 'name', even if it's not the primary index of the table. — Greg Kelesidis, Mar 18 '15 at 10:53
@JenisPatel This seems to be just example code, which is off-topic for Code Review. If this would be posted to Code Review, we would need to know the real code and the real context behind it. — Simon Forsberg, Mar 18 '15 at 10:58

score 3 · Accepted Answer · edited May 23 '17 at 11:57

The database is pretty sure a good idea.

Databases are fast, maybe they are not as fast as basic String operations in PHP, but if there is a lot of data, databases will probably be faster. A basic select Query takes (on my current default Desktop Hardware) about 15ms, cached less than 1ms, and this is pretty much independend of the number of names in your table, if the indexes are correct. So your site will always be fast.
Databases won't cause a StackOverflow or an out of memory error and crash your site (this is very depending on your PHP-Settings and Hardware)
Databases are more flexible, imagine you want to add / remove / edit names after creating the first Data-Set. It's very simple with "INSERT INTO", "DELETE FROM" and "UPDATE" to modify the data, better than editing a string somewhere in your code with about 100.000 entries.

Your Code

You definitly need to use MySQLi or PDO instead, code maybe like this:

$mysqli = new mysqli("host", "username", "password", "dbname");
$stmt = $mysqli->prepare(
    'SELECT string FROM table WHERE name = ? LIMIT 1'
);
$stmt->bind_param("s", $value);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($string);
$stmt->fetch();
$stmt->close();

This uses MySQLi and Prepared Statements (for security), instead of the depracated MySQL extension (in PHP 5.5)

Plus the exploding string to array in OPs second method will be a noticeable overhead with 100,000 names; so it will slow as it grows, whereas a (properly indexed) database won't — Mark Baker, Mar 18 '15 at 09:28
Thank your, but if i want use while and get function for 1000 number , database is goo ?! simple code : `for($i = 0;$i < 1000; $i++) { find($i); }` — Mohammad, Mar 18 '15 at 11:12
I don't exactly understand your question, what do you mean by "database is goo"? — Roman Holzner, Mar 18 '15 at 11:14
I just wondered why no question is accepted and realized @Mohammad has only 45 reputation... — Roman Holzner, Mar 19 '15 at 14:28

PHP: MySQL Select Query vs String Operation

1 Answers1

The database is pretty sure a good idea.

Your Code