0

Having a bit of a dilemma over whether or not to break into 2 tables a large (few hundred K records) table that contains a text column.

The table in question stores news articles:

CREATE TABLE `article` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `articleType` varchar(7) DEFAULT NULL,
  `dateCreated` datetime NOT NULL,
  `label` varchar(75) NOT NULL,
  `lastUpdated` datetime NOT NULL,
  `reporter` mediumint(8) unsigned NOT NULL,
  `text` text NOT NULL
  PRIMARY KEY (`id`),
  KEY `reporter-fk` (`reporter`),
  CONSTRAINT `reporter-fk` FOREIGN KEY (`reporter`) REFERENCES `reporter` (`id`)
)

So, big deal, in straight SQL when you want to get the headlines (latest news) you would grab the columns you want (id, label, dateCreated) and exclude the ones you don't want (particularly the bloated text column)

When working with an ORM, however, an object is fetched that contains all of the columns, so grabbing 50 of the most recent articles is going to incur some overhead, perhaps not hugely so, but enough to make me cringe a bit as I would never grab all fields in this case when writing straight SQL.

Given the ORM reality, should I break the text column out into a separate, related table, or not bother, just go with ORM grab-the-whole-enchilada convention and worry about it when site traffic demands the more efficient, 2 table solution?

virtualeyes
  • 10,859
  • 6
  • 49
  • 89

2 Answers2

1

Normally, I say don't prematurely optimize.

However, in this case, I'd break it out now, as there is already an argument to do this. You probably are displaying a list of article headers without displaying the bodies. Why not put the bodies in their own table/class associated with the article?

It seems that an article header and an article body are already two separate things.

Marcus Adams
  • 49,523
  • 8
  • 81
  • 132
  • in production there's just a single table with no ORM, string-based SQL; in development, I have the tables broken up as I couldn't deal with the (perfectionist) thought of including article texts on every query. This post is about going with ORM convention or going with SQL convention, my preference is with the latter as that is what I know best... – virtualeyes Apr 18 '12 at 15:06
1

Are you using a particular ORM, or a home-grown one? In the event you're using Propel, set the column to be lazy-loaded - it will then load upon explicit request, rather than with the rest of the object. If not, then see if your ORM supports this, or build it in.

halfer
  • 18,701
  • 13
  • 79
  • 158
  • I'm using ScalaQuery, technically not an ORM, more of a functional JDBC wrapper with LINQ-to-SQL-like abilities. That is cool re: lazy loading particular column(s), but unlikely ScalaQuery provides such a mapping out of the box. I should point out that I am not opposed to the returned object(s) from the query (quite nice in fact), but rather the heft/weight of the object when the kitchen sink is thrown in. I'm leaning toward splitting up the tables, will likely save headaches down the road... – virtualeyes Apr 18 '12 at 15:13
  • Ah, beg pardon - thought I saw PHP in your tag-list. I've got PHP on the brain! A quick search suggests that ScalaQuery doesn't have lazy loading, but here's some info on [this feature in Hibernate](http://stackoverflow.com/questions/2192242/what-is-lazy-loading-in-hibernate). – halfer Apr 18 '12 at 15:32
  • Well, in ScalaQuery I could specify the columns (I lied a bit), but coding-wise it's nicer to work with objects, and, in a statically typed language like Scala, it saves me some boilerplate (as select foo, bar, baz means I have specify each column's type [(String, Int, Date)] and then pass around this tuple, which is way less convenient than just working with a List of Foos that I can get IDE auto-complete goodness on). Anyway, thankfully not PHP, spent 10 years LAMP stackin', done with the spaghetti ;-) – virtualeyes Apr 18 '12 at 15:50
  • Lazy loading should still give you auto-completing - the getter triggers an extra select. Re PHP, yep, it's spaghetti at work for me. But, my own projects are on symfony 1.4 and Propel 1.6 - an awesome combo and no tangles in sight! I'm sure Symfony2 is even better, but no real need to switch `:-)` – halfer Apr 18 '12 at 16:51