2

I have a table in my MySQL (InnoDB) full with user items. Basically each row has a user_id field and other item properties like color. Then there is one more field called a link which holds the id of some other user's item, but in most cases (90 %) there is no item linked and thus the field is set to NULL.

I was wondering would it be more efficient to make a new table which would hold the link information than having 90 % of 6 million rows to have the field link set to NULL?

I'm using Hibernate.

Rihards
  • 10,241
  • 14
  • 58
  • 78
  • post your structure, so people can suggest normalizing or other improvements. mostly you can make an attributes table and some MN relationships that will hold info whenever is the case only. This though increases time when building up the user. – Elzo Valugi Jun 21 '11 at 19:03
  • This is a difficult question to answer without knowing your basic schema and database. For example, SQL Server stores NULL's at basically no additional disk cost above creating the column in the table. Of course, you should probably design your solution to be database agnostic but how many times does that happen in real life? – Perception Jun 21 '11 at 19:10
  • @Elzo, how to print table structure nicely so I could post it here? – Rihards Jun 21 '11 at 19:10
  • use databse; show tables; describe table_x; – Elzo Valugi Jun 21 '11 at 19:13
  • @Perception, The question was based on MySQL. How do you design a MySQL database to be database agnostic? – kakridge Jun 21 '11 at 19:21
  • Indeed, OP did ask his question relative to MySQL. My mistake. – Perception Jun 21 '11 at 19:33

4 Answers4

2

Yes, it would be more efficient. It would make a very small difference.

Best is to do whatever is easiest for you and then change it when it becomes a real problem.

Sjoerd
  • 74,049
  • 16
  • 131
  • 175
1

Yes, it would be more efficient and more normalized. Whenever I see a table with lots of nulls like this, I consider it a candidate for normalization. In this example, you could remove that column from the table entirely and it would be much more cleaner and easier to maintain. You would just create a junction table with a two user_ids that are foreign keys on the user items table.

kakridge
  • 2,153
  • 1
  • 17
  • 27
  • Unless the records are very small I doubt this is going to be more efficient. – Karoly Horvath Jun 22 '11 at 10:58
  • @yi_H, please elaborate and try to upgrade your doubt to a proof. – kakridge Jun 22 '11 at 19:38
  • *grin* It's you who haven't given any argument to your opinion, see my answer to the question and check @Richard's reply. Also be aware that denormalization is often the way to achieve better performance. – Karoly Horvath Jun 23 '11 at 10:48
  • I actually agree with *most* of what you are saying. My primary concern was that your logic looked like a slippery slope, ie, that the database would be a massive single table because "join is bad". In an OLTP environment, I would first make the choice I suggested. In an OLAP environment, you are absolutely correct. – kakridge Jun 23 '11 at 17:50
0

as far as your logic is concerned, just consider storing only the tables with link data, and the code that calls if can do an "if not null" or equivalent to always know what to get. Don't store all those nulls if you can make better assumptions

CQM
  • 42,592
  • 75
  • 224
  • 366
0

It's going to occupy less space. But if you do a (left) JOIN for each query the performance is going to be worse.. especially if you have many rows and the table doesn't fit in memory. Then you need two disk seeks to fetch one record.

Update:

  • JOIN takes some additional processing. It's going to be fast if you have indexes but still, you have to look up another record. And if you use InnoDB to support transactions the database has to maintain a version for the joined record.
  • JOIN is bad for memory locality, now you have to look up a record which is at an entirely different memory location.
  • As I mentioned if the data is not in the memory you need an additional disk seek. This is really bad.
Karoly Horvath
  • 94,607
  • 11
  • 117
  • 176
  • Just checked and I wouldn't need to use joins. I would just need to check if the user's item has a linked item with some other user's item, but that is done less than just getting the item where the link doesn't matter. Thanks! – Rihards Jun 21 '11 at 19:18
  • It wouldn't be a LEFT JOIN it would be an INNER JOIN. The new table would just be a junction between two user ids. – kakridge Jun 21 '11 at 19:19