Forums

The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Other How to setup a database table?

  • This topic is empty.
Viewing 15 posts - 16 through 30 (of 56 total)
  • Author
    Posts
  • #141086
    chrisburton
    Participant

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

    #141088
    __
    Participant

    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.

    #141093
    chrisburton
    Participant

    @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`)?

    #141095
    __
    Participant

    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.

    #141096
    chrisburton
    Participant

    @traq What’s the difference between

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

    #141097
    __
    Participant

    `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.

    #141098
    chrisburton
    Participant

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

    #141099
    __
    Participant

    >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.

    #141100
    chrisburton
    Participant

    @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.

    #141101
    __
    Participant

    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.

    #141103
    chrisburton
    Participant

    @traq That’s reassuring.

    #141104
    __
    Participant

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

    : p

    #141105
    chrisburton
    Participant

    @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.

    #141106
    __
    Participant

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

    #141107
    chrisburton
    Participant

    @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)
  • The forum ‘Other’ is closed to new topics and replies.