The best way to store immutable data

Question

When I work on my games, I use a lot of MySQL databases.

But lately, I have put a lot of SELECT results in Memcache, because they are unlikely to have changed.

Which makes me wonder if it is better to just have a PHP file that defines data arrays? Of course, this means that I have to load the entire array only to access one row, but I believe that this is insignificant compared to the time that MySQL does not need to wait.

Am I really on this path of thinking? Should I transfer my immutable game data (such as basic character statistics, compatibility diagrams, element data, etc.) to PHP files? Or should I use MySQL + Memcache?

Which is better and why?

Answer 1

It would seem that some benchmarks are in order.

I have compiled several scripts for testing data storage in the form of a PHP array, both a CSV file and a MySQL DB, accessed through PDO.

Array test

Here are the scripts to test the array:

array inc.php:

 $data = array( array( "key" => "something", "value" => "something else" ), array( "key" => "something", "value" => "something else" ), array( "key" => "something", "value" => "something else" ), array( "key" => "something", "value" => "something else" ), array( "key" => "something", "value" => "something else" ) ); 

array test.php

 $start = microtime( TRUE ); for( $i = 0; $i < 10000; ++$i ) { include( "array-inc.php" ); $retrieve = $data[2]; } $finish = microtime( TRUE ); print $finish - $start; 

CSV test

Here are the scripts for the CSV test:

values.csv:

 key,value something,something else something,something else something,something else something,something else something,something else 

csv.php:

 $start = microtime( TRUE ); for( $i = 0; $i < 10000; ++$i ) { $fp = fopen( "values.csv", "r" ); $data = array(); while(( $line = fgetcsv( $fp )) !== FALSE ) { $data[] = $line; } $retrieve = $data[2]; } $finish = microtime( TRUE ); print $finish - $start; 

MySQL test

And here is the script for the MySQL test (the table has an id, key and value column with the same five rows and values ​​as above):

mysql.php:

 $start = microtime( TRUE ); for( $i = 0; $i < 10000; ++$i ) { $query = "SELECT * FROM `values` WHERE id = :id"; $result = $pdo->prepare( $query ); $result->execute( array( ":id" => 2 )); $retrieve = $result->fetch( PDO::FETCH_ASSOC ); } $finish = microtime( TRUE ); print $finish - $start; 

Each of them is configured to access one element from the stored data and cycle 100,000 times so that time can be more accurately measured. Here are the results:

I ran each of these tests three times and got the following values:

• Array:
  • 1.050, 1.020, 1.114 seconds
• CSV:
  • 1.264, 1.232, 1.105 seconds
• MySQL:
  • 1.038, 1.149, 1.171 seconds

CSV seems to be a clear loser, but Array and MySQL are very close to the fact that Array may be a bit ahead of performance. Feel free to tweak the code above to provide results that are more relevant to your environment.

Answer 2

The answer to your question is a specific problem. This is a compromise between performance, memory, and maintainability. Even if you want to name the shortcomings and advantages for the decisions of candidates, you need to accept some parameters. Here are the options that influence the decision you make:

• Data Size: what is the total size of the data you have. those. the size of your database file.

• The average memory size required for data download requests: given the above parameter and the average number of simultaneous requests, how much memory is required for them.

• The average ratio of the total amount of data required for each request: each time you download data, how many of them you will work with and how easily you can select this part of the data.

• How long does the processor take to extract the target part of the data from the general set: it can vary significantly from a simple PHP array to a MySQL SELECT query.

Knowing these parameters, you can ask whether one solution is better or another. In any case, your decision will be one of the following:

• Included in the code.
• Configuration files ( XML , INI , CSV , JSON , etc.).
• Database Server ( MySQL ).
• Local database ( SQLite ).
• Cache (cache servers such as memcached , or PHP shared memory ).

I know that I did not answer your question, but this is because it is impossible to answer it if you do not specify the specified parameters.