P Marksson wrote:Wow! Nice improvements!
One question though: Shouldn't URL.Category be a VARCHAR? How can an INT reference a VARCHAR?
Claude Moore wrote:You are indexing VARCHAR columns;if you are going to run selects with LIKE clause on such columns, you may not found any benefit at all - query may result in a full table scan.
Brian Tkatch wrote:
Claude Moore wrote:You are indexing VARCHAR columns;if you are going to run selects with LIKE clause on such columns, you may not found any benefit at all - query may result in a full table scan.
Why? As long as the characters before the wildcard are specified, the index should be used. If not, there's nothing you can do anyway. Regardless, the index cost is minimal.
Stefan Evans wrote:Just to put the opposing view, I am one of those who would use numbers to generate the link between these two tables.
Probably as a result of all of that third normal form database design that got drilled into my head along the way.
So I would give the category table an id field, and the foreign key reference from the URL table would reference that.
Reasons (not that they are necessarily good ones)
- Integer comparisions are easier/faster than string comparisions (premature optimization)
- The category/group name is only stored in the database once, rather than repeated for every URL. If you ever want to change it (not in your use cases I agree) there would be only one row to change in the database.
P Marksson wrote:Yeah I noticed that to. Having URL.Category as a varchar means the string for a given group/category is defined/exists on more than one place. Thoughts on that?
Brian Tkatch wrote:
Another example is people who think (in Oracle) that rebuilding indexes makes the database faster. This is based on people not understanding how caching works and misapply an idea used by large tables (millions of records) that have been heavily modified (over 50% of their data deleted or updated).
Brian Tkatch wrote:
The same is true for ids. Somebody had the notion that numbers would save space or be faster. While it may save space against (larger) varchar columns (that actually use the space), there is no shortage of space these days where that is going to make a difference. Some people think numbers are faster. I have not seen any proof of this. Who knows, maybe letters are faster.
Dave Tolls wrote:This was always one of Tom Kytes bugbears, IIRC.
I think he used to call it lobotomising the database.
Brian Tkatch wrote:Some people think numbers are faster. I have not seen any proof of this. Who knows, maybe letters are faster.
The secret of how to be miserable is to constantly expect things are going to happen the way that they are "supposed" to happen.
You can have faith, which carries the understanding that you may be disappointed. Then there's being a willfully-blind idiot, which virtually guarantees it.
Tim Holloway wrote:I don't like URLs as primary keys, because not only are they prone to mutation, but also often aliased and/or piggybacked.
The secret of how to be miserable is to constantly expect things are going to happen the way that they are "supposed" to happen.
You can have faith, which carries the understanding that you may be disappointed. Then there's being a willfully-blind idiot, which virtually guarantees it.
Tim Holloway wrote:"That will never change" are famous last words.
Tim Holloway wrote:I twitch a little at the idea that a service - or a server - might not be renamed.
Tim Holloway wrote:I twitch a LOT at the idea that a string (key or otherwise) might end up being internationalized. Or re-capitalized. Or re-punctuated.
Tim Holloway wrote:Codes in place of "meaningful" text pre-date computers. They defend against ambiguity and often have self-checking attributes.
P Marksson wrote: I read on stackoverflow that VARCHAR as primary key is significantly slower.
P Marksson wrote:Seems like MySQL loses its intelligence once a VARCHAR is used as foreign key.
P Marksson wrote:
P Marksson wrote:Forgot to mention earlier; all the URLs within a given Category must be unique.
P Marksson wrote:This insertion will work fine even if there is no Category called HEY.
If the foreign keys were integers, we wouldn't have this problem.
Please explain what you meant. That example is changed for Oracle. Note, my earlier code spelled "address" with only one "d". Oops.Category_Adress.Category can still reference a non-existing Category though.
Dave Tolls wrote:Got to say I have never encountered this with MySQL.
I've had lookup tables with VARCHAR primary keys and the FK mappings work as expected.
Dave Tolls wrote:I'm not in a MySQL shop, but does 'references' act the same as:
CONSTRAINT 'fk_some_fk' FOREIGN KEY ('some_col') REFERENCES 'tabel2' ('other_col')
or whatever the syntax is?
Dave Tolls wrote:I always stick the constraints as a separate thing. Habit.
Dave Tolls wrote:In fact, some Googling (which also brought up this ancient bug, referenced by this SO thread) seems to imply that inlined use of 'references' doesn't work.
Dave Tolls wrote:It isn't mentioned as valid in the docs anymore, that's for sure. Which explains why I've never used it.
Dave Tolls wrote:See the ancient bug.
They weren't getting an error either.
Does it work properly with the separate CONSTRAINT line?
Dave Tolls wrote:See the ancient bug.
They weren't getting an error either.
Does it work properly with the separate CONSTRAINT line?
P Marksson wrote:
Brian Tkatch wrote:
FWIW, leaving off the optional CONSTRAINT keyword (and the name) should work as well.
Did you see how Paul cut 87% off of his electric heat bill with 82 watts of micro heaters? |