CODEWITHSUNDEEP
mysql
Yeti
Yeti

Reputation: 5818

Use multiple tables or just one for performance

I have 2 tables:

DB_ACCEPTED_USERS:
+-------+-----------------+---------+
| id    | photo_id        | user    |
+-------+-----------------+---------+
| 1     | 1234            | foo1    |
| 2     | 5678            | foo2    |
+-------+-----------------+---------+

DB_REJECTED_USERS:
+-------+-----------------+---------+-----------+
| id    | photo_id        | user    | reason    |
+-------+-----------------+---------+-----------+
| 1     | 4321            | foo3    | too fat   |
| 2     | 8765            | foo4    | too thin  |
+-------+-----------------+---------+-----------+

DB_ACCEPTED_USERS is the only one that's live. Currently hundreds of queries are made on it every second. DB_REJECTED_USERS is only accessed by the admin.

I'm considering changing the structure to this: 

DB_USERS:
+-------+-----------------+---------+---------+-----------+
| id    | photo_id        | user    |rejected | reason    |
+-------+-----------------+---------+---------+-----------+
| 1     | 1234            | foo1    | 0       | null      |
| 2     | 5678            | foo2    | 0       | null      |
| 3     | 4321            | foo3    | 1       | too fat   |
| 4     | 8765            | foo4    | 1       | too thin  |
+-------+-----------------+---------+---------+-----------+

Will this have any affect on the query time or performance? It's nice to have only one table, but it will also have a lot of data that's NEVER queried online. Only photo_id and user are required online.

In terms of performance, will keeping 2 tables make queries to DB_ACCEPTED_USERS any better than if there's only 1 table?

Upvotes: 0

Views: 57

Answers (3)

Terradon
Terradon

Reputation: 858

First: do you have performance problems? if not, why change? Does it work now ?

If You have performance problems? Investigate your problem and identify the cause, then fix the cause.

However, I do not know your other tables and relation between them, but these both tables looks to me as the same kind of data, so they belong in the same table. Dont worry about performance problems by this, if your db-schema is OK, you can have many millions of rows in your table, witout noticing it.

Upvotes: 1

DreadfulWeather
DreadfulWeather

Reputation: 738

Having many null values is not a good thing. I believe you should use one table for users and another table for reason. With this way i believe it should be easier to query the db. More the db gets normalized it is better. In your first approach it is hard to make users unique. I mean what if another guy gets foo3 nickname and gets accepted. In my opinion below should be better in terms of querying but about speed i have no idea.

DB_USERS:
+-------+-----------------+---------+
| id    | photo_id        | user    |
+-------+-----------------+---------+
| 1     | 1234            | foo1    |
| 2     | 5678            | foo2    |
+-------+-----------------+---------+
DB_REJECT_REASONS:
+-------+------------+---------+
| id    | user_id    |  reason |
+-------+------------+---------+
| 1     | 2          |too fat  |
| 2     | 4          |too thin |
+-------+------------+---------+

Upvotes: 0

Baine Sumpin
Baine Sumpin

Reputation: 142

Probably not worth combining as the reject is rarely used. Combining would slow your queries somewhat, depending on how much extra data it is dealing with it could be a lot. Also you would have to add a test to the query to not include rejected users, which would also add some microsecond to the query.

Upvotes: 1

Related Questions