A quick note, I've read Tom's email about using a hanger table and simply don't agree.
From: olinux <[EMAIL PROTECTED]>
> I have a table with a large number of news articles.
For my curiosity, how many article records is "a large number"? A few thousand, or
tens of millions?
> The question is whether to
> 1) add a field to the database that most records will not use
This is the option that will create the fewest records. If you need to make a single
query and be finished, this is the way I'd go.
> 2) add a table to hold image path names and require a JOIN in order
> to retrieve.
Let's figure what type of JOIN you mean. The MySQL docs seem to indicate that a LEFT
JOIN isn't all that bad to run, but my benchmarking doesn't agree. So let's assume you
want a straight join. If you were doing that, then you'd only get back articles which
have an image path defined because of this type of a joining predicate:
articles.id = article_images.article_id
In that case, why are you taking the overhead of scanning two tables? Why not scan one
table with a predicate like:
AND article_type = 'feature'
I'm betting that typical usage for you will be a single query something like this:
SELECT *
FROM articles
WHERE keywords like '%good article%'
Then you'll display all the records that you've gotten. If this assumption is correct,
then I'd strongly suggest compacting your data as much as possible. That is, use one
table for data that is on a one-to-one relationship and not related to anything else.
Of course, the best thing to do after all of the discussion is to simply try it out. I
had a very simular situation a month or two back where I was told that the JOINs
weren't a problem. I redisigned the schema in the way that I've indicated and got an
improvement of many times (5-12 times faster). In short, we can add up what the docs
say all we like, but nothing beats actuall clinical testing.
---
Rodney Broom
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
