Forums

Give help. Get help.

  • # June 30, 2013 at 7:41 pm

    @AlenAbdula Ok thanks. Now the fun starts. Inserting the array strings into the database.

    __
    # June 30, 2013 at 7:56 pm

    Just as extra food for thought –

    Is every URL you store going to be unique? If not, then will every URL+title combination be unique?

    What I’m getting at is that, while every table should have an index, that index doesn’t _need_ to be artificial. “Natural” indexes are preferable, in fact, while also bringing the performance benefits @CrocoDillon mentioned.

    **If** every URL+title combination will be unique, do
    `PRIMARY KEY( url,title )`
    and drop the `id` column.

    You _might_ want to index the `title` column (separately) also, if you ever plan on searching by title without the URL.

    Otherwise, **if** every URL will be unique, do
    `PRIMARY KEY( url )`
    and drop the `id` column.

    You _might_ want to index the `title` column also, if you ever plan on searching by title.

    Otherwise, keep your `id` column as the PK.

    # June 30, 2013 at 8:34 pm

    @traq

    Every `URL` and `Title` will be unique. The only way that they wouldn’t be is if I read the same article (title) from the same website (url) continuously. But Readability does not allow duplicate articles so that would be impossible.

    I don’t ever plan on using any of the data to be searched. I only plan to list it as shown here.

    So should I keep my table as is (including `id`)?

    __
    # June 30, 2013 at 11:15 pm

    that’s what I thought – if `url`, `title`, or combination are unique values, then use one, the other, or both as your PK*. You don’t need the `id` column. It’s just extra overhead to maintain.

    *I’d go with both, since you’re likely to be querying for both (correct?). If you do
    `PRIMARY KEY( url,title )`

    and make sure you always query like
    `SELECT url,title …`

    (meaning, _not like_ `SELECT title,url …`), then you’ll be able to read from the index (_FAST!!!_) and never have to touch the table itself.

    # June 30, 2013 at 11:31 pm

    @traq What’s the difference between

    `SELECT url, title…` and `SELECT title, url…`?

    __
    # June 30, 2013 at 11:35 pm

    `SELECT title,url` can’t use a PK index created on `url,title`. It will have to actually access and read the rows in the table itself (which is much slower than just reading the index).

    `SELECT url,title …` (or even just `SELECT url …` (but not `SELECT title …`)) can get everything it needs from that index and bypass reading the table entirely.

    # June 30, 2013 at 11:38 pm

    @traq Even if I set the primary key to both?

    __
    # June 30, 2013 at 11:44 pm

    >Even if I set the primary key to both?

    yes, order is important with compound keys.

    _(I edited my post above to clarify.)_

    You _could_ index each column separately (`PRIMARY` on one, `UNIQUE` on the other) if you wanted to, but if you’ll [almost] always be reading _both_, I’d recommend the compound PK.

    # June 30, 2013 at 11:47 pm

    @traq Ha. Quite hard to grasp when you’re just learning this. I think I’ll have to do some research to understand some of it.

    __
    # June 30, 2013 at 11:55 pm

    I “plateaued” for a long time with SQL. Like, _five years_ of mediocre SELECT statements and not much else. It’s only in the last six months, really, that I’ve had an explosion of understanding… and I still have a long ways to go.

    # July 1, 2013 at 12:26 am

    @traq That’s reassuring.

    __
    # July 1, 2013 at 12:36 am

    @chrisburton Not sure if you’re being sarcastic or not.

    : p

    # July 1, 2013 at 12:38 am

    @traq Haha. I’m being very sarcastic. If it took you that long to grasp SELECT statements, it could only mean that it will take me just as long or even longer.

    __
    # July 1, 2013 at 12:45 am

    Nah, I _grasped_ them, I just couldn’t seem to grow _beyond_ them. But everyone’s different.

    # July 1, 2013 at 12:49 am

    @traq Oops. Never meant to demean your knowledge on the subject. Sorry about that. Regardless, I really appreciate all the insight you’ve given me on this.

Viewing 15 posts - 16 through 30 (of 56 total)

You must be logged in to reply to this topic.

icon-anchoricon-closeicon-emailicon-linkicon-logo-staricon-menuicon-nav-guideicon-searchicon-staricon-tag