Or store the ip address as int64 with index, or better both solutions together
A terrible schema from a clueless programmer
151–160 of 493 posts
Re: A terrible schema from a clueless programmer
#152The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…
Yep. Folks are getting lost in the weeds discussing indexing of database tables. That's _totally_ beside the point here. The thing is, the first implementation was a perfectly fine "straight line" approach to solve the problem at hand. One table, a few columns, computers are pretty fast at searching for stuff... why not? In many scenarios, one would never see a problem with that schema. Unfortunately, "operating in a…
I’d be a lot more sympathetic if the major RDMSes didn’t have outstanding and thorough reference manuals or that there weren’t a mountain of books on the subject that cover, among other things, the topic of indexing and its importance. MySQL’s manual, for example, has covered this subject from the very beginning: https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html (I don’t have the 3.x manuals handy but it was there back then too).
It’s not clear from the article whether the author spent any time studying the problem before implementing it. If she failed to do so, it is both problematic and way more common than it ought to be.
“Ready, fire, aim”
But you know what they say: good judgment comes from experiences and experience comes from bad judgment.
Compounding the problem here is that the author now has much more experience and in a reflective blog post, still got the wrong answer.
IMO the better lesson to take away here would have been to take the time getting to know the technology before putting it into production instead of jumping head first into it. That would be bar raising advice. The current advice doesn’t advise caution; instead it perpetuates the status quo and gives “feel good” advice.
Re: A terrible schema from a clueless programmer
#153> The first time you encounter something, you're probably going to make some mistakes. There's a post going around tonight about how someone forgot to put an index on some database thing and wound up doing full table scans (or something like that). Which post is referenced here? I also believe this to be a tooling issue. It's often opaque what ends up being run after I've done something in some framework (java jpa, d…
Pretty sure it's this one https://news.ycombinator.com/item?id=29132572
Re: A terrible schema from a clueless programmer
#154> The whole system was reworked to have a table for each set of values. There was now a table of IP addresses, a table of HELO strings, a table of FROM addresses, and a table of TO addresses.
Lisp interning, in database tables. A.k.a. Flygweight Pattern.
Re: A terrible schema from a clueless programmer
#155One of the most useful lessons I ever learned about designing database schemas was the utter uselessness of indexing and searching on datetime fields. Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all. It was a revelation to me when I decided to experiment with having a indexed date-ONLY field and using th…
there's more index types than only hashes (which do indeed only support equality) e.g. btrees allow greater than/less than comparisons postgres supports these and many more: https://www.postgresql.org/docs/9.5/indexes-types.html
I was using a BTREE index and we were doing greater/less than queries. It was not a hash index.
Re: A terrible schema from a clueless programmer
#156Earlier quoted context omitted.
Clearly it worked. However... Consider the rows `a b c d e` and `f b h i j`. How many times will `b` be stored in the two formulations? 2 and 1, right? The data volume has a cost. Consider the number of cycles to compare a string versus an integer. A string is, of course, a sequence of numbers. Given the alphabet has a small set of symbols you will necessarily have a character repeated as soon as you store more value…
> Clearly it worked. No it didn't. Someone else put an index on the main table and that worked.
The article declared that it was running in production and satisfying the requirement. Do you have a different definition of working? I say that to clarify what I was attempting to communicate by "it worked".
I think you're remarking about the relationship between normalized tables and indexes. That relationship does exist in some databases.
Please say more if I'm missing your point.
Re: A terrible schema from a clueless programmer
#157Why not store the IP Address as a 32-bit number (IPv4 addresses)? Why store it as a string in the first place? This is something I did not quite get. Also, wouldn't it be better to split out the domain from the email address for ease of filtering?
Also, how does performing joins give a performance advantage here. I'm assuming there would be queries to get at the IDs of at least one, but going up to 4, to get at the IDs of the items in the quad. Then there would be a lookup in the mapping table.
I have worked for some time in this industry, but I have never had to deal with relational databases (directly; query tuning and such were typically the domain of expert db people). It would be interesting to see an explanation of this aspect.
EDIT: To people who may want to comment, "You missed the point of the article!": no, I did not, but I want to focus on the technical things I can learn from this. I agree that ageism is a serious problem in this industry.
Re: A terrible schema from a clueless programmer
#158There is a time and a place for a denormalized schema. If you are trying to search a large data-set a denormalized schema in a single table with some good indexing is much preferable to joining across 30 tables.
Whether you choose normalized or denormalized depends very much on a) what you need to do now b) what you may need to do in the future. Both are considerations.
Re: A terrible schema from a clueless programmer
#159
Actually, the Correct Answer is a bloom filter.
(And, yes, we had math in the early 2000s.)
Snark aside, I'm frustrated for the author. Her completely-reasonable schema wasn't "terrible" (even in archaic MySQL)—it just needed an index.
There's always more than one way to do something. It's a folly of the less experienced to think that there's only One Correct Way, and it discourages teammates when there's a threat of labeling a solution as "terrible."
Not to say there aren't infinite terrible approaches to any give problem: but the way you guide someone to detect why a give solution may not be optimal, and how you iterate to something better, is how you grow your team.
Re: A terrible schema from a clueless programmer
#160While the article is not really about the database schema, could you do a table with just two fields?
1) hash of concat(ip & helo & m_from & m_to) 2) time