MySQL Forums
Forum List  »  MyISAM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
One table vs. multiple tables? (SELECT/WHERE effeciency)
3542
July 15, 2006 03:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.