Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.4.8, 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
-
None
-
CentOS 7
Description
MariaDB 10.0.34:
explain select omlid from om_listings_search where saletypeid='1' and bathrooms between 2-1 and 2+1 and min_sqft>=1200*0.9 and max_sqft<=1200*1.2 and bedrooms >=4 and lp_typeid=3 and MBRContains(GeomFromText('POLYGON((49.004442954955 -122.34298052941, 49.094533045045 -122.34298052941, 49.094533045045 -122.22533347059, 49.004442954955 -122.22533347059, 49.004442954955 -122.34298052941 ))'), latlng_point) and statusid=1 order by sold_date_int desc limit 6;
|
+------+-------------+--------------------+-------+---------------+--------+---------+------+------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------+-------+---------------+--------+---------+------+------+-----------------------------+
|
| 1 | SIMPLE | om_listings_search | range | latlng | latlng | 27 | NULL | 1 | Using where; Using filesort |
|
+------+-------------+--------------------+-------+---------------+--------+---------+------+------+-----------------------------+
|
1 row in set (0.00 sec)
|
10.4.8:
{
|
"query_block": {
|
"select_id": 1,
|
"read_sorted_file": {
|
"filesort": {
|
"sort_key": "om_listings_search.sold_date_int desc",
|
"table": {
|
"table_name": "om_listings_search",
|
"access_type": "ALL",
|
"possible_keys": ["latlng"],
|
"rows": 4004430,
|
"filtered": 100,
|
"attached_condition": "om_listings_search.lp_typeid = 3 and om_listings_search.statusid = 1 and om_listings_search.saletypeid = '1' and om_listings_search.bathrooms between <cache>(2 - 1) and <cache>(2 + 1) and om_listings_search.min_sqft >= <cache>(1200 * 0.9) and om_listings_search.max_sqft <= <cache>(1200 * 1.2) and om_listings_search.bedrooms >= 4 and mbrcontains(<cache>(st_geometryfromtext('POLYGON((49.004442954955 -122.34298052941, 49.094533045045 -122.34298052941, 49.094533045045 -122.22533347059, 49.004442954955 -122.22533347059, 49.004442954955 -122.34298052941 ))')),om_listings_search.latlng_point)"
|
}
|
}
|
}
|
}
|
}
|
I changed the following to match 10.0.34:
optimizer_switch
optimizer_use_condition_selectivity
histogram_size
histogram_type
use_stat_tables
This results in the query taking seconds rather than milliseconds. A hugely crippling bug.
MariaDB 10.4.8 insists on doing a full table scan and refuses to use an index.
Attachments
Issue Links
- relates to
-
-
- Confirmed
-