Another post in my Development Walk-Through Series, following me through the development of my own application to share my thoughts and observations. Please consider subscribing to my feed if you find this information helpful.
Now that you’re working on your Models in Phase Two of your development process, you have a second opportunity to trim up your database. Once you start putting together CakePHP queries, it quickly becomes obvious that CakePHP will arrange your queries according to it’s own sense of MySQL rather than what you might have originally thought. This means that the database development you did in Phase One will have to be altered a bit.
But the way you put together queries is also important. For example, when assembling the [‘conditions’] array within your query, the order in which you specify the conditions is directly reflected in the way the conditions are built into the query.
Why is this important? Well for a start, because if you’re using any multi-column indexes, those indexes rely on the order of columns in the query being in the same order as they appear in the index. Without the correct order, they simply cannot be used. I don’t have any proof to back this up, but it stands to reason that this fact may point the way to another tweaking possibility: that the order of columns probably matters regardless of whether or not you use multi-column indexes.
The take away from this is that when you create queries in the CakePHP manner, it’s important to look at the resultant query and make sure it’s behaving the way you expected it to. If you can tweak the query, do it. If you cannot, it’s probably time to rethink some of your optimization strategy to better reflect the reality of the system in which you are working.