Implementation Always Bites
That lovely new data structure for ExpressionEngine's custom field data hit a rather nasty snag today while Jones was working to implement it in the code. The beauty about having all custom field data in one table with a single row for every entry and a column for every custom field is that to do a search you really only need to do a single JOIN on that table when looking up entries. In the scheme of things (a pun!), a single JOIN allowing you to search all the possible custom data is amazingly fast and easy for MySQL. Not only that, it allows you to easily order all of your data by those custom fields with no additional JOINs or any manipulation at the code level. Those two features are one of the major reasons that despite other problems we have not moved away from this data structure. The old sacrifice X for Y scenario. Weigh the benefits and cons of each approach and choose the best.
This new data structure though has a new row created for every custom field for each entry. And each possible data type gets its own column in the table. There are realistically a limit on the number of data types that we will ever put into ExpressionEngine, so there is a finite number of columns we will ever have, which solves an annoying MySQL limit we were running into on larger, more complex sites. Further, this structure gives a chance to add a new feature and cuts down on unused data columns being called for entries. Finally, it brings our database structure that much closer to be truly normalized.
The downside is that to do searching on multiple fields we end up doing a JOIN for every field that needs to be searched. Someone starts searching seven custom fields and you have seven JOINs. Further, and this is slightly more nasty in my mind, if you want to order your entries by a custom field, we have to do a JOIN for that as well, unless it was JOIN'ed previously for a search. Ugh ugh ugh.
At the very least, we can still support all of the current features. Took a few hours of testing in MySQL but we are sure we can reprouce them all. Just happens to be slower and a bit more MySQL intensive than we had hoped. Jones is going to create a test site and we are going to do some benchmarking and see how MySQL likes a more complex entry retrieval query. On Monday though as I think a break is in order.
...
Cameron and I went to Hopworks last night for dinner and thanks to a sampler, we discovered we like nearly every single beer they offer. The exception was the extremely hoppy Crosstown Pale. I prefer my beers rather dark and flavorful, but I do not mind other, less opaque beers. However, when all you can really taste and, heck, smell is hop, there is no joy in drinking it for me. The pizza by the slice was good and cheap. Going by the high quality of the beer though, I was hoping for something more on the level of Pizzicato quality pizza. Especially since pizza takes up a solid third of their dinner menu.
...
And now, I am going to bump off work half an hour early. It is Friday and the sun is finally shining after a week of cold rain and brutal wind. Need to go for a run and burn off last night's indulgences. Taking the train up to Seattle for an eagerly awaited visit, so I will probably not have another chance for a run until next week. Summer is coming up, and I need to get back into hiking, backpacking, biking, and climbing shape.
This new data structure though has a new row created for every custom field for each entry. And each possible data type gets its own column in the table. There are realistically a limit on the number of data types that we will ever put into ExpressionEngine, so there is a finite number of columns we will ever have, which solves an annoying MySQL limit we were running into on larger, more complex sites. Further, this structure gives a chance to add a new feature and cuts down on unused data columns being called for entries. Finally, it brings our database structure that much closer to be truly normalized.
The downside is that to do searching on multiple fields we end up doing a JOIN for every field that needs to be searched. Someone starts searching seven custom fields and you have seven JOINs. Further, and this is slightly more nasty in my mind, if you want to order your entries by a custom field, we have to do a JOIN for that as well, unless it was JOIN'ed previously for a search. Ugh ugh ugh.
At the very least, we can still support all of the current features. Took a few hours of testing in MySQL but we are sure we can reprouce them all. Just happens to be slower and a bit more MySQL intensive than we had hoped. Jones is going to create a test site and we are going to do some benchmarking and see how MySQL likes a more complex entry retrieval query. On Monday though as I think a break is in order.
...
Cameron and I went to Hopworks last night for dinner and thanks to a sampler, we discovered we like nearly every single beer they offer. The exception was the extremely hoppy Crosstown Pale. I prefer my beers rather dark and flavorful, but I do not mind other, less opaque beers. However, when all you can really taste and, heck, smell is hop, there is no joy in drinking it for me. The pizza by the slice was good and cheap. Going by the high quality of the beer though, I was hoping for something more on the level of Pizzicato quality pizza. Especially since pizza takes up a solid third of their dinner menu.
...
And now, I am going to bump off work half an hour early. It is Friday and the sun is finally shining after a week of cold rain and brutal wind. Need to go for a run and burn off last night's indulgences. Taking the train up to Seattle for an eagerly awaited visit, so I will probably not have another chance for a run until next week. Summer is coming up, and I need to get back into hiking, backpacking, biking, and climbing shape.
– Friday, 2008 April 25 @ 3:50 PM | No Comments -