How to setup a database table?

[chrisburton]

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

[__]

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.

[chrisburton]

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

[__]

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.

[chrisburton]

@traq What’s the difference between

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

[__]

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

[chrisburton]

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

[__]

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

[chrisburton]

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

[__]

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.

[chrisburton]

@traq That’s reassuring.

[__]

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

: p

[chrisburton]

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

[__]

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

[chrisburton]

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

[Author]

Posts