![]() It all goes back to ensuring that you actually have the right table as the base for your query. Again, this is a very common problem with Drupal Views queries. We can’t execute this GROUP BY via an index when it’s not in the base pool, and thus the resulting pool, after the join, will need some water treatment – a temporary table sort. This time our only filter is on node that’s good, but then we are going to GROUP BY a column in taxonomy. Again, we have node as our base pool, and we are going to join against taxonomy. Drupal – in particular Views – sometimes makes this very difficult. You always want to have your major conditions (the ones that limit your returned data the most) in the base table. But in reality, it would be far better to start with the taxonomy table and only join the filtered rows that match our condition. The issue here seems conceptually simple: to fulfill this query, we need to join every node against every taxonomy term. The node table being the base table, and all the filtering/conditions being on sub-tables, is a very common problem in Drupal. How do we limit the water flowing from the node pool into the taxonomy pool? We don’t. We have no filter on the node table, and our only real filter – the one that defines our dataset – is on the taxonomy table. We then join against a taxonomy table (pool) and filter on a taxonomy type. Our biggest pool is the Drupal node table. The goal is to limit the size of the waterfalls and ensure that the resulting pool doesn’t need water treatment (temporary tables used to service a GROUP BY, ORDER BY, or DISTINCT). The largest pool at the top of the hill is the base table for the join. Each pool has a little waterfall that flows into the pool below. In keeping with our nature metaphor, we are going to visualize indexes as trees and tables as pools of water: specifically, you can think of a table as a large pool of water at the top of a hill, with smaller pools of water under it as you progress down the hill. Now that you have a basic understanding of how indexes work, you need an equally basic understanding of joins. For example, if you have five queries that run against a table and three of them have WHERE conditions on columnB alone, that column should come first in the index so that it can be used. This leads to some interesting decisions. How would you get to the columnB values in this index? You must have a way to traverse the columnA branches to get to the columnB values. ![]() For example, if you have a query with a WHERE condition on columnB, and not columnA, it can’t use the index on (columnA, columnB). If you have a query that has a WHERE condition on these two columns, MySQL will go through this tree looking for the correct columnA value first, and then go into the leaves of that object, and find the correct columnB value.ĭue to this tree organization, there are some limitations. Thus, if you have an example index test that covers (columnA, columnB), you literally have a tree of columnA values, with columnB values in the leaves. (B+Trees, specifically for more information, see. It is best to think of an index as a tree, largely because they are trees in most DB systems. The main issue is the mystical belief that the MySQL optimizer should be able to quickly run a query if an index so much as touches the columns in question. Index BasicsĮven though indexes are very important for database performance, they are not completely understood by many developers, which often leads to easily-avoidable problems. To get you started, we will cover some very basic optimization, index usage, and join optimization techniques. Not completely knowing how to use this layer and how to optimize it is very limiting. I highly recommend that you get a book on this subject for any Drupal developer, it is well worth learning.Īs a web developer using a CMS, you are only slightly removed from the SQL layer. Obviously query optimization is a large subject, and can’t possibly be covered in full in a single article. While tuning is important, it often has nowhere near the impact of actually fixing a poorly performing query. A large part of MySQL optimization lies in improving poorly performing SQL queries.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |