Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a clustered index on a table expecting it to make the queries with ranges perform better, but, different values in the where clause can produce differente query plans, one uses the clustered index and one does not.

My question is: What can I do to make the DBMS use the better query plan? Or better yet, should I change my schema to something better?

Details:

  • I'm using Amazon's RDS (Server version: 5.5.31-log)
  • I executed optimize table on each table (expecting it to "rebuild" the clustered index and reset the statistics), sometimes it does not change anything, sometimes the DBMS will use worse query plans, sometimes makes it faster because it will use the clustered index.
  • explain extended followed by a show warnings did not produce anyting interesting/useful
  • I'm aware of index hinting. I tested it and the query plan used the primary index but I don't know if it always works, also, I'm using django and django's ORM does not support index hinting, so a solution that did not require it would be nice.

Tables:

-- DDL
create table api_route (
   id int(11) not null auto_increment primary key,
   origin_id int(11) not null,
   destination_id int(11) not null,
   group_id int(11) not null,
   foreign key (origin_id) references api_area (id),
   foreign key (destination_id) references api_area (id),
   foreign key (group_id) references api_group (id)
) engine=innodb, collate=utf8;

create table api_area (
  id int(11) not null auto_increment primary key,
  name varchar(50) not null
) engine=innodb, collate=utf8;

create table api_location (
  id int(11) not null auto_increment primary key,
  area_id int(11),
  foreign key (area_id) references api_area (id)
) engine=innodb, collate=utf8;

create table api_locationzip (
   location_ptr_id int(11) not null,
   zip_start int(11) not null,
   zip_end int(11) not null,
   foreign key locationzip_to_location (location_ptr_id) references api_location (id),
   constraint primary key using btree (zip_start, zip_end, location_ptr_id)
) engine=innodb, collate=utf8;

create table api_locationkey (
  location_ptr_id int(11) not null,
  key varchar(10) not null,
  foreign key locationkey_to_location (location_ptr_id) references api_location (id)
) engine=innodb, collate=utf8;

Query:

An area has many locations, every location has either a zip or key.

select * from
  api_route,
  api_area origin,
  api_area destination,
  api_location location_origin,
  api_location location_destination,
  api_locationzip origin_zip,
  api_locationzip destination_zip
where
  api_route.group_id IN (1,2,3,...) and
  -- filter origin by zip code
  api_route.origin_id = origin.id and
  origin.id = location_origin.area_id and
  location_origin.id = origin_zip.location_ptr_id and
  origin_zip.zip_start <= <zipcode_origin> and
  origin_zip.zip_end >= <zipcode_origin> and
  -- filter destination by zip code
  api_route.destination_id = destination.id and
  destination.id = location_destination.area_id and
  location_destination.id = destination_zip.location_ptr_id and
  destination_zip.zip_start <= <zipcode_destination> and
  destination_zip.zip_end >= <zipcode_destination>
limit 100

Execution plans:

Here is an explain of a slow query (~1.6s):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: destination
         type: index
possible_keys: PRIMARY
          key: api_area_group_id_599f0627e68b9613_uniq
      key_len: 156
          ref: NULL
         rows: 3794
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: api_route
         type: ref
possible_keys: api_route_0261d0a2,api_route_8de262d6
          key: api_route_8de262d6
      key_len: 4
          ref: master.T6.id
         rows: 9
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: origin
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: master.api_route.origin_id
         rows: 1
        Extra: 
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: location_origin
         type: ref
possible_keys: PRIMARY,api_location_a4563695
          key: api_location_a4563695
      key_len: 4
          ref: master.origin.id
         rows: 39
        Extra: Using where; Using index
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: origin_zip
         type: ref
possible_keys: PRIMARY,locationzip_to_location 
          key: locationzip_to_location 
      key_len: 4
          ref: master.location_origin.id
         rows: 1
        Extra: Using where; Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: location_destination
         type: ref
possible_keys: PRIMARY,api_location_a4563695
          key: api_location_a4563695
      key_len: 4
          ref: master.destination.id
         rows: 39
        Extra: Using index
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: destination_zip
         type: ref
possible_keys: PRIMARY,locationzip_to_location 
          key: locationzip_to_location 
      key_len: 4
          ref: master.location_destination.id
         rows: 1
        Extra: Using where; Using index
7 rows in set (0.00 sec)

Here is the explain of a fast query (~100ms):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: destination_zip
         type: range
possible_keys: PRIMARY,locationzip_to_location 
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 119268
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: location_destination
         type: eq_ref
possible_keys: PRIMARY,api_location_a4563695
          key: PRIMARY
      key_len: 4
          ref: master.destination_zip.location_ptr_id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: api_route
         type: ref
possible_keys: api_route_0261d0a2,api_route_8de262d6
          key: api_route_8de262d6
      key_len: 4
          ref: master.location_destination.area_id
         rows: 9
        Extra: 
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: origin
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: master.api_route.origin_id
         rows: 1
        Extra: 
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: location_origin
         type: ref
possible_keys: PRIMARY,api_location_a4563695
          key: api_location_a4563695
      key_len: 4
          ref: master.origin.id
         rows: 39
        Extra: Using where; Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: origin_zip
         type: ref
possible_keys: PRIMARY,locationzip_to_location 
          key: locationzip_to_location 
      key_len: 4
          ref: master.location_origin.id
         rows: 1
        Extra: Using where; Using index
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: destination
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: master.location_destination.area_id
         rows: 1
        Extra: 
7 rows in set (0.00 sec)

Edit: Added create table code and full query

share|improve this question
1  
Can you have many price ranges per product? (or just 2, one for buy and one for sell)? If you have just these two, the primary index could be defined as (type, product_id) –  ypercube Aug 20 '13 at 23:40
1  
And how do you expect anyone to help when the description and the EXPLAIN plan use different names for the tables and different query? –  ypercube Aug 20 '13 at 23:47
    
In your slow example, type:index combined with Extra:using index means a full index scan; the only thing worse than this is a full table scan. Unfortunately, without some understanding of your WHERE clause, table structures, and join conditions, it's impossible to provide much more, though I expect your clustered index strategy is more of a hindrance than a help, especially when compared to a small primary key and multiple secondary indexes with different column orders for the optimizer to choose from... but that's impossible to say without more detail. –  Michael - sqlbot Aug 21 '13 at 0:52
    
@ypercube Sorry, didn't want to just throw my stuff on the question so I came up with a similar example. Added the real schema and query –  hack.augusto Aug 21 '13 at 3:25

1 Answer 1

I got some performance improvements by mapping all the ranges into a series of integers. Ranges that includes all 0-9 numbers are encoded with the letter a, the resulting number is used in base 11, so, the range 100-199 is encoded as 1aa (which in base 10 is 241). With the ranges in place, a search for 150 does a query with IN (150, 15a, 1aa, aaa). I didn't manage to remove the range join, but because now it is a single integer column, it has a smaller index that performs better. An alternative that I did not experiment was using a R-tree index, it might be another improvement.

I also got a working hack to add the index hint into Django's QuerySets, not the best way to solve my problem, but it made a huge difference.

def get_compiler(qs, using=None, connection=None):
    '''
    Method that gets the QuerySet's sql compiler. It monkey patches the
    QuerySet so that the compiler state can be changed. Keep in mind
    that most of the QuerySet's methods returns a new one (which is
    not gonna be monkey patched)

    QuerySet's methods that return a new instance:
        - https://docs.djangoproject.com/en/1.5/ref/models/querysets/#methods-that-return-new-querysets
    '''
    if using is None and connection is None:
        compiler = qs.query.get_compiler(using=qs.db)
    else:
        compiler = qs.query.get_compiler(using=using, connection=connection)

    qs.query.get_compiler = lambda *args, **kwargs: compiler

    return compiler

def primary_index_hint(table, qs, index=-1):
    '''
    Method that adds index hinting. Table is the name in the database 
    (not the name used one the ORM)

    index hint: https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

    Django 1.5 - 21/08/13
    '''
    query, compiler = qs.query, get_compiler(qs)
    alias = query.table_map[table][index]

    join = list(query.alias_map[alias])
    join[1] = '{} use index (primary)'.format(alias)

    query.alias_map[alias] = JoinInfo(*join)
    compiler.quote_cache[join[1]] = alias

    return qs
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.