CODEWITHSUNDEEP
phpmysqldatabaseperformance
Mohsen Rasouli
Mohsen Rasouli

Reputation: 352

how to pass and process data (php) to database (mysql) for best performance?

I created a php system that generates 150 "Serial Number" for each product. Now I want to store these serial numbers in a database. There are some ways that I found to do this:

  1. Create a table name products_serialNumber and store each serialnumber to 1 record and relate it to products using product_id key.

If I do this, I will have lots of data that may no body use that. But fetching data in this way is more easier and faster.

  1. Create a table name product_serialNumbers and store array of serialnumbers for each products and relate is using "product_id" key again.

If I do like this way, I will have a serial numbers record for each products, but I should use more foreach and for loop functions to extract array of data and variables to use where that I need, and use lots of memory. I'm limited to 64m using memory.

Upvotes: 0

Views: 100

Answers (3)

Mehdi Hashemi
Mehdi Hashemi

Reputation: 36

You Can Store JSON …

Normalization is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value — which is broken by storing multi-value JSON documents.

Create a Table with a JSON Field

CREATE TABLE `product_serialNumbers` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `serialNumbers` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Adding JSON Data

INSERT INTO `product_serialNumbers` (`title`, `serialNumbers`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["ac111", "ab177", "ac14521"]'
);

JSON can also be created with the:

//returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

//returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);

//returns ["a", 1, {"key": "value"}]:
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

Searching JSON Data

//all product_serialNumbers with the 'ac14521' serialNumbers:
SELECT * FROM `product_serialNumbers` 
WHERE JSON_CONTAINS(serialNumbers, '["ac14521"]');

Upvotes: 2

Shozab H.
Shozab H.

Reputation: 575

Solution 1 is good because in both cases, data space will be taken.

If you go for Solution 2 or save in json, every time you retrieve data will require to parse the json which will be relatively slow and consume memory as well. So, take advantage of SQL Joins.

Solution 1 will be faster and cheap in terms of memory consumption. And don't worry about space, nowadays space is not a challenge but the efficiency. And if you think the data is useless, why are you storing it?

Upvotes: 1

M Gholami
M Gholami

Reputation: 971

If you are using ORM databases like Mysql , The best choice is using solution 1 because storing and fetching related data is MOST important reason of using ORM databases. But solution 2 is good when you are using NoSql databases like MongoDB or other document base databases.

Upvotes: 1

Related Questions