What is the most efficient way to store a large JSON in MySQL database using PHP?
As I understand it my options are:
-
Process it in JS – make an
AJAXcall toPOSTdata (through a php script) in the database for every every key/value I want to store (can’t pass the JSON directly to php – throws object size error and I don’t want to mess with php.ini) -
Pass it as text to PHP , decode it and then do a
mysqli_querryin a loop for every key/value pair I want to store. - Pass it as text to MySQL and handle it there with MySQL built-in JSON functions.
What option would be more perfomant?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Generally, you need to decide which fields of the JSON string that you will be searching on via SQL. Put those fields into their own columns. Then the JSON (with or without copies of those fields) is simply an opaque TEXT or JSON column. It will be written and read as a unit; the client will unpack as needed.
PHP has decent functions for handling JSON. I suggest sticking with UTF-8 encoding of the JSON and using JSON_UNESCAPED_UNICODE. If stored into TEXT, be sure to have utf8mb4; the collation does not matter. (The MySQL/MariaDB details vary with the exact version you are using.)
You ask for “performant”. By breaking out the columns needed for searching, MySQL indexing will work much faster than dynamically picking apart JSON. (Again the version matters when considering indexing JSON fields and/or using GENERATED columns.)
“Key-value” (EAV) schema is clumsy and bad for performance. So I hope you don’t need to create arbitrary tests across multiple key-value pairs. I discuss that further here: http://mysql.rjweb.org/doc.php/eav
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0