4

This is my table definition, without any special index for now:

CREATE TABLE `filter` (
  `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `position` smallint(6) NOT NULL,
  `options` longtext COLLATE utf8_unicode_ci COMMENT '(DC2Type:json_array)',
  PRIMARY KEY (`field_name`,`category_id`),
  KEY `IDX_702C956612469DE2` (`category_id`),
  CONSTRAINT `FK_702C956612469DE2` 
      FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Sample data:

+------------+-----------------------+-------------+----------+
| field_name |        options        | category_id | position |
+------------+-----------------------+-------------+----------+
| color      | {"label": "Color"}    |        1895 |        1 |
| material   | {"label": "Material"} |        1895 |        2 |
| color      | {"label": "Color"}    |        1896 |        1 |
| color      | {"label": "Color"}    |        1897 |        1 |
+------------+-----------------------+-------------+----------+

Most of the time I do a query with WHERE IN and ORDER BY position and selecting only field_name and options:

EXPLAIN SELECT 
  field_name
  options
FROM 
  filter
WHERE 
  category_id IN ('1895', '1896', '1897') 
ORDER BY 
  position ASC

This gives me Using index condition; Using filesort, quite bad uh?

{
   "query_block":{
      "select_id":1,
      "ordering_operation":{
         "using_filesort":true,
         "table":{
            "table_name":"filter",
            "access_type":"range",
            "possible_keys":[
               "IDX_702C956612469DE2"
            ],
            "key":"IDX_702C956612469DE2",
            "used_key_parts":[
               "category_id"
            ],
            "key_length":"98",
            "rows":5,
            "filtered":100,
            "index_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
         }
      }
   }
}

With an index on category_id, position Using where; Using index; Using filesort:

{
   "query_block":{
      "select_id":1,
      "ordering_operation":{
         "using_filesort":true,
         "table":{
            "table_name":"filter",
            "access_type":"range",
            "possible_keys":[
               "IDX_702C956612469DE2",
               "IDX_CATEGORY_POSITION"
            ],
            "key":"IDX_CATEGORY_POSITION",
            "used_key_parts":[
               "category_id"
            ],
            "key_length":"98",
            "rows":5,
            "filtered":100,
            "using_index":true,
            "attached_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
         }
      }
   }
}

So which explain is better? I would say the first because of using index condition (there is no "where" part). Is there any way to avoid the filesort?

5
  • 1
    One question - how many rows do you expect to get in average? In your plans there are 5 rows expected - filesort on 5 rows would be trivial if only longtext could be sorted in memory, but it has to go to disk according to manual. You could extract longtext to a different table to get over this, but hard to say if it is viable. Your order is actually sort of undefined as multiple categories can have a field with the same position - what about sorting by (category_id,position) ? sqlfiddle.com/#!9/aabaa/7 - solves it
    – jkavalik
    Commented Jul 26, 2015 at 21:05
  • Just noticed that your plans are probably a bit skewed, because your query selects only one field, there shoud be a comma after field_name - as it is options is an alias for field_name - thats why you get Using indexin the second plan.
    – jkavalik
    Commented Jul 26, 2015 at 21:08
  • @jkavalik I expect at least ~1000 rows. About longtext, I could trim it down to a limited varchar it this would solve the "problem". For sorting by category_id,position your are right: it should be by both fields, my mistake, but I still get using where, using index (not like your fiddle).
    – gremo
    Commented Jul 26, 2015 at 21:53
  • 1
    "Using index" means it is doing index-only scan - so you are probably not selecting options - check my second comment about a comma and the fiddle - and what does your query return without explain (because "using index" is not possible for text/blob columns at all). The index on (category_id, position) seems perfect for where in by first and order by both.
    – jkavalik
    Commented Jul 27, 2015 at 6:06
  • When I started writing it, it did not seem yet like a viable solution, just some notes and data gathering. Will make it an answer.
    – jkavalik
    Commented Jul 27, 2015 at 17:25

1 Answer 1

2

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on those columns to get it without filesort. http://sqlfiddle.com/#!9/aabaa/7

Because of the longtext column it is not possible to turn this into an index-only scan (text columns can be only indexed on prefix, not on entire value, as index key length is limited).

Using varchar fields as primary key has some drawbacks, but it is not "wrong" by itself so just a little suggestion - would it make sense in your desing to use some integer ids instead?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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