[MDEV-20774] 10.4.8 refuses to use an index, even with FORCE INDEX Created: 2019-10-08  Updated: 2021-03-19

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.8
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Gordan Bobic Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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.

 Comments   
Comment by Gordan Bobic [ 2019-10-08 ]

It looks like something changed significantly in 10.4.x. In 10.0.x it was possible to use a regular b-tree index for the above query. In 10.4.x, the index has to be changed to a spatial r-tree index for the optimizer to consider it even with FORCE INDEX.

Comment by Varun Gupta (Inactive) [ 2019-10-08 ]

Hi Gordon,

Can you help to clarify did changing optimizer flags to match 10.0 help or not.
Can you add the DDL for the table (both for 10.0 and 10.4)

  • show create table om_listings_search
  • show index from om_listings_search
    Can you share the optimizer trace output for the query from 10.4

  set optimizer_trace=1
 
  explain query
 
  select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Comment by Gordan Bobic [ 2019-10-14 ]

The table structure is here:

CREATE TABLE `om_listings_search` (
`omlid` int(11) NOT NULL,
`zboardid` tinyint(4) NOT NULL,
`latlng_point` point NOT NULL,
`saletypeid` tinyint(4) NOT NULL,
`bedrooms` tinyint(4) NOT NULL,
`bathrooms` tinyint(4) NOT NULL,
`lp_typeid` tinyint(4) NOT NULL,
`min_sqft` smallint(6) NOT NULL,
`max_sqft` smallint(6) NOT NULL,
`list_price` int(11) NOT NULL,
`sold_date_int` int(11) NOT NULL,
`statusid` tinyint(4) NOT NULL,
PRIMARY KEY (`omlid`),
SPATIAL KEY `latlng` (`latlng_point`),
) ENGINE=InnoDB;

On 10.0.x the latlng key was a regular B-Tree key and it worked.
On 10.4.x the laglng key had to be converted to a spatial key before it the query planner would use it even when hinted with force index.

Generated at Thu Feb 08 09:02:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.