CODEWITHSUNDEEP
mysql
ccdavies
ccdavies

Reputation: 1606

How best to store records in SQL for access/performance querying

I have been researching to understand how best to store my data in my SQL table.

I expect to have around 50,000 entries, of which I need to store 4 judge_id 'scores' for.

The first option I thought of, is to simply enter 4 rows per entry, like so:

id      entry_id        round_id        judge_id        score       created_at      updated_at
1       1234            1               1               10          1560690340      null    

id      entry_id        round_id        judge_id        score       created_at      updated_at
2       1234            1               2               7           1560690340      null    

id      entry_id        round_id        judge_id        score       created_at      updated_at
3       1234            1               3               9           1560690340      null    

id      entry_id        round_id        judge_id        score       created_at      updated_at
4       1234            1               4               3           1560690340      null

As I understand it, this will allow easy access for queries, but it would result in 200,000 rows, instead of 50,000. Would this cause performance issues?

The second option I thought of, was to use a single row, storing the scoring data (judge_id (key), score, created_at, updated_at) within an array.

As I understand it, I would not be able to query the data within the array until I return the results, which would render it useless.

id      entry_id        round_id        score
1       1234            1               $score

$score = array('1' => array('score' => '10', 'created_at' => '1560690340', 'updated_at' => 'null'), '2' => array('score' => '7', 'created_at' => '1560690340', updated_at' => 'null'), '3' => array('score' => '9', 'created_at' => '1560690340', 'updated_at' => 'null'), '4' => array('score' => '3', 'created_at' => '1560690340', 'updated_at' => 'null'));

Is anyone able to offer any suggestions on how best to store this data?

Upvotes: 0

Views: 38

Answers (1)

Anton
Anton

Reputation: 86

It would be nice if you will add examples of search queries which you want to use. Or at least which data will be used for search. Then it will be much easier to find optimal solution for you case.

First of all the column "id" from you example looks useless, do you need it actually? You can make primary key using two columns "entry_id and "round_id"(if all other data will be saved as json for example). I work mostly with PostgreSQL and we use for such cases jsonb column type, I saw also json column in MySQL and you can also save you data as a json instead of array, search in this json using SQL queries.

Another option is to save data in traditional way with database normal forms in several tables, using joins for search. 200.000 rows it's not really too much for MySQL.

Alternative: Also you can use search engine like elastic search, index all you data and search in this engine using a lot of search options, elastic search will also give you some data back.

Upvotes: 1

Related Questions