Details
Minor
Description
See testcase and details in MDEV-2608
I'm not sure if this is actually a bug (i.e. here we have a situation where the optimizer had sufficient info to make the right decision but didn't make it). This needs to be investigated.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: SJ-Materialization picks scan-strategy which is 2x slower than SJ-Materialization lookup
== Semi-join plan analysis ==
MariaDB [bug806894]> show table_statistics;
---------------------------------
| Table_schema | Table_name | Rows_read |
---------------------------------
| bug806894 | City | 1676 |
| bug806894 | Country | 114 |
---------------------------------
MariaDB [bug806894]> show index_statistics;;
-------------------------------------------+
| Table_schema | Table_name | Index_name | Rows_read |
-------------------------------------------+
| bug806894 | City | Country | 1676 |
| bug806894 | Country | PRIMARY | 114 |
-------------------------------------------+
This means that
Country: 114 rows expected, 114 rows read
City: 18*114=2052 rows expected, 1676 rows read
CountryLanguage: 18*114*1=2052 rows expected, 0 rows actually read
Per-table counters count sucessful read ops. Key lookups that found nothing are
not counted. Let's also check status increments:
---------------------------------+
| Handler_read_key | 1791 |
| Handler_read_next | 1790 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 0 |
---------------------------------+
.. which are caused by:
table | Handler_read_key | Handler_read_next
Country | 1 | 113
City | 114 lookups | min. 1676
CountryLanguage | =1790-115=1676 | 0, its eq_ref
Re: SJ-Materialization picks scan-strategy which is 2x slower than SJ-Materialization lookup
Indeed, let's take another look at the query:
select *
from
CountryLanguage
where (Language, Country) IN (SELECT City.Name, Country.Code FROM ...)
Apparently, one will never get matches when they look for City.Name= CountryLanguage.Language.
On the other hand, in the subquery's join
SELECT City.Name, Country.Code FROM City, Country WHERE Country = Code and Code > 'LLL'
either table will have matches. Condition "City.Country=Country.Code" is the join intended by the dataset, each city lies within some existing country, and each country has some cities.
Re: SJ-Materialization picks scan-strategy which is 2x slower than SJ-Materialization lookup
Due to the above, I'm making the conclusion:
this particular slowdown is expected. The fact that it worked before is pure luck, because previous plan has put table that has no matches in other tables into the front of the query plan, while now it is at the end of it.
Re: SJ-Materialization picks scan-strategy which is 2x slower than SJ-Materialization lookup
Also filed in JIRA as MDEV-194
Launchpad bug id: 806894
Re: SJ-Materialization picks scan-strategy which is 2x slower than SJ-Materialization lookup
I too am seeing a lot of issues where semijoin is detrimental to use. For my application, subqueries are absolutely essential so I have been running maria 5.3 in production since at least september.
I occasionally run into areas where maria attempts to semijoin and it will cause the query to be 1 or more orders of magnitude slower. Unfortunately, about 1/10 as often I find that semijoin makes some painful queries wickedly fast, so I am forced to leave it on in some cases.
You have this marked as low priority but I would really like to see this fixed for maria 5.3. I do consider this a major bug but semijoin is too optimistic about it's chances to handle some things and can wreak havoc on queries even mysql 5.5 can do faster.