5 ms·
Is this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?
by havkd 5y ago
Is this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?
- loeg 5y agoThe same O(N) -> O(log N) improvement for queries, yes. The constant factor on the separate tables might be better. It’s also a more complicated design.
- adfgaertyqer 5y agoSure would! I think it would be marginally better, in fact, because you would just need to look at the index rather than five tables. Access would be more local.
- kofejnik 5y agoIt would be faster, I believe. As we’re only looking for existence, a single index traversal is all io we would need to do
- mst 5y agoGiven this is a "from the start of her career" story, I'm guessing she was running similar versions of mysql to the versions I started with, and if my guess is correct then probably not. On anything you're likely to be deploying today, just throwing a compound index at it is likely quite sufficient though.
- taeric 5y agoAn index is often easily viewed as another table. So, should. (Some subtleties on projected values and such, but the point stands.)
- havkd 5y agoThen this post is very misleading.
- taeric 5y agoI wouldn't get too vehement on it. Knowing how to normalize isn't a bad thing. And, if you intricately know your write and read needs, you may be able to assist the work more appropriately.
- jameshart 5y agoYes, the proposed ‘better’ structure basically amounts to building your own indexes. Normally, I’d assume it is better to use the RDBMS’s own engine to do that, don’t roll your own. There may well be some subtlety to the indexing capabilities of MySQL I’m unaware of though - could easily imagine myself making rookie mistakes like assuming that it has same indexing capabilities. So, to the post’s point - if I were working on a MySQL db I would probably benefit from an old hand’s advice to warn me away from dangerous assumptions. On the other hand I also remember an extremely experienced MS SQL Server DBA giving me some terrible advice because what he had learned as a best practice on SQL Server 7 turned out to be a great way to not get any of the benefits of a new feature in SQL Server 2005. Basically, we’re all beginners half the time.
- rrrrrrrrrrrryan 5y agoAs a former SQL Server DBA, most SQL Server DBAs are absolute gurus on 1 or 2 specific versions of SQL server, and massively ignorant about anything newer. It's a strange role where, in order to do your job well, you kind of have to hyper-specialize in the specific version of the tech that your MegaCorp employer uses, which is usually a bit older, because upgrading databases can be extremely costly and difficult.
- rustc 5y agoMoving the data into another table would still require indexes: one on the original table's column (which now stores the new id) and one on the new table's primary key. In most cases I'd expect just adding an index to the original table to be more efficient, but it depends on the type of the original column and if some data could be de-duplicated by the normalization.