![]() ![]() For some reason, the query optimizer is treating tableA as the inner table and tableB as the outer table.Ĭan someone please explain why that happens and what error am I making in my thought process? Also, is there a way to forcefully supersede the decision of query optimizer and dictate it to treat tableB as inner table? I am just curious to see how do the two different executions of the same query compare to each other. ![]() You should put the joins in the order that makes the most sense for reading and maintaining the query. If there is no suitable index on SALEDATE for a pipelined order by, the database must read and sort the entire table. This order is determined based on information about the sizes of the tables and other factors, such as the presence of indexes. The filtering is being used to determine whether a specific row from the right (nullable) table should be matched up or not. And there exists a non-clustered index on columnA.Īccording to my thinking, the query optimizer should treat tableB as the inner table while performing the join, because it has a lesser number of rows, and treat tableA as the outer table, as a lot of rows need to be filtered from tableA based on the value of the tableB.id column.īut, the exact opposite of this actually happens. The documentation for MySQL states 'The join optimizer calculates the order in which tables should be joined'. Note that, with LEFT JOINs, some filtering in the ON clause may make sense. tableB.id is a primary key, hence clustered. Now, tableA contains 29000+ rows, whereas tableB contains just 11000+ rows. Join method - nested loop, hash join, merge join (aka: parallel walk or sort join), others Indexed. Upon doing a simple inner join of these tables, SELECT * Join order - which table to examine first, next, etc. A left join to another table will always return every row from visits. So, I have these two tables: tableA and tableB. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |