One table vs. multiple tables? (SELECT/WHERE effeciency)
Posted by:
packetme
Date: July 15, 2006 03:49PM
I'm new to these forums, and I've looked over the many sections available. I feel this question is likely related to my chosen database format, MyISAM, as I've got an effeciency question!
I had a ton of descriptive text for the database here, but it'll be easier to answer this if I just cut to the chase!
Okay, two scenarios, distilled to their essential question:
1) "SELECT * FROM tbl_permissions WHERE target_type='BOT' AND target_id=2"
In this case, the column "target_type" is an ENUM('BOT', 'BOTGROUP', 'CHAN', 'CHANGROUP'......) (yes, this is an Internet Relay Chat-related database!). This table would contain thousands of entries.
2) "SELECT * FROM tbl_permissions_bot_group WHERE target_id=2"
As you can see, it's basically using an ENUM type in place of multiple tables in example #1. For what I'm doing, this seems very logical from a design perspective. Less tables, logical column types, and it's all accessible in one place.
However, the main question is, is #1 or #2 more effecient? It seems as if #1 would be less effecient than #2, but hopefully due to intelligent pattern recognition and magic caching, MyISAM (or...could it really be MySQL?) would cause option #1 to be just as fast as table retrieval after several SELECTs. I feel that even if #1 is slower, it's worth it to save the time and headache of multiple tables. I just want to make sure it's not a hideous design idea.
This also brings about a smaller question I have: Is there any performance difference when trying to find a column of type ENUM versus INTEGER?
Likewise, when storing, is an ENUM just as size-effecient as an INTEGER? That is, does an ENUM store actual strings (ineffeciently) like a TEXT or VARCHAR would, or does it use numbered references behind-the-scene to save some disk/memory space?
Thanks a lot for any help you can provide!
Edited 1 time(s). Last edit at 07/15/2006 03:49PM by packetme.