mysql - Index IN and a range -

-

i need find best index query:

select c.id id, type content c use index (type_proc_last_cat) left join battles b on c.id = b.id type = 1     , processing_status = 1     , category in (13, 19)     , status = 4 order last_change desc limit 100";

the tables this:

mysql> describe content; +-------------------+---------------------+------+-----+---------+-------+ | field             | type                | null | key | default | | +-------------------+---------------------+------+-----+---------+-------+ | id                | bigint(20) unsigned | no   | pri | null    |       | | type              | tinyint(3) unsigned | no   | mul | null    |       | | category          | bigint(20) unsigned | no   |     | null    |       | | processing_status | tinyint(3) unsigned | no   |     | null    |       | | last_change       | int(10) unsigned    | no   |     | null    |       | +-------------------+---------------------+------+-----+---------+-------+  mysql> show indexes content; +---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | table   | non_unique | key_name            | seq_in_index | column_name       | collation | cardinality | sub_part | packed | null | index_type | comment | +---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | content |          0 | primary             |            1 | id                |         |        4115 |     null | null   |      | btree      |         | | content |          1 | type_proc_last_cat  |            1 | type              |         |           4 |     null | null   |      | btree      |         | | content |          1 | type_proc_last_cat  |            2 | processing_status |         |          20 |     null | null   |      | btree      |         | | content |          1 | type_proc_last_cat  |            3 | last_change       |         |        4115 |     null | null   |      | btree      |         | | content |          1 | type_proc_last_cat  |            4 | category          |         |        4115 |     null | null   |      | btree      |         | +---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+   mysql> describe battles; +---------------------+---------------------+------+-----+---------+-------+ | field               | type                | null | key | default | | +---------------------+---------------------+------+-----+---------+-------+ | id                  | bigint(20) unsigned | no   | pri | null    |       | | status              | tinyint(4) unsigned | no   |     | null    |       | | status_last_changed | int(11) unsigned    | no   |     | null    |       | +---------------------+---------------------+------+-----+---------+-------+  mysql> show indexes battles; +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | table   | non_unique | key_name  | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | battles |          0 | primary   |            1 | id          |         |        1215 |     null | null   |      | btree      |         | | battles |          0 | id_status |            1 | id          |         |        1215 |     null | null   |      | btree      |         | | battles |          0 | id_status |            2 | status      |         |        1215 |     null | null   |      | btree      |         | +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

and output this:

mysql> explain     -> select c.id id, type     -> content c use index (type_proc_last_cat)     -> left join battles b use index (id_status) on c.id = b.id     -> type = 1     ->     , processing_status = 1     ->     , category in (13, 19)     ->     , status = 4     -> order last_change desc     -> limit 100; +----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+--------------------------+ | id | select_type | table | type   | possible_keys      | key                | key_len | ref                   | rows |                    | +----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+--------------------------+ |  1 | simple      | c     | ref    | type_proc_last_cat | type_proc_last_cat | 2       | const,const           | 1352 | using where; using index | |  1 | simple      | b     | eq_ref | id_status          | id_status          | 9       | wtm_master.c.id,const |    1 | using where; using index | +----+-------------+-------+--------+--------------------+--------------------+---------+-----------------------+------+--------------------------+

the trouble rows count content table. appears mysql unable use both last_change , category in type_proc_last_cat index. if switch order of last_change , category, fewer rows selected results in filesort order by, meaning pulls matching rows database. worse, since there 100,000+ rows in both tables.

tables both innodb, keep in mind primary key appended every other index. index index type_proc_last_cat above behaves likes it's on (type, processing_status, last_change, category, id). aware change primary key battles (id, status) , drop id_status index (and may that).

edit: value type, category, processing_status, , status less 20% of total values. last_change , status_last_change unix timestamps.

edit: if use different index category , last_change in reverse order, this:

mysql> show indexes content; +---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | table   | non_unique | key_name            | seq_in_index | column_name       | collation | cardinality | sub_part | packed | null | index_type | comment | +---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | content |          0 | primary             |            1 | id                |         |        4115 |     null | null   |      | btree      |         | | content |          1 | type_proc_cat_last  |            1 | type              |         |           6 |     null | null   |      | btree      |         | | content |          1 | type_proc_cat_last  |            2 | processing_status |         |          26 |     null | null   |      | btree      |         | | content |          1 | type_proc_cat_last  |            3 | category          |         |         228 |     null | null   |      | btree      |         | | content |          1 | type_proc_cat_last  |            4 | last_change       |         |        4115 |     null | null   |      | btree      |         | +---------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+   mysql> explain select c.id id, type content c use index (type_proc_cat_last) left join battles b  use index (id_status) on c.id = b.id type = 1     , processing_status = 1     , category in (13, 19)     , status = 4 order last_change desc limit 100; +----+-------------+-------+-------+--------------------+--------------------+---------+-----------------------+------+------------------------------------------+ | id | select_type | table | type  | possible_keys      | key                | key_len | ref                   | rows |                                    | +----+-------------+-------+-------+--------------------+--------------------+---------+-----------------------+------+------------------------------------------+ |  1 | simple      | c     | range | type_proc_cat_last | type_proc_cat_last | 10      | null                  |  165 | using where; using index; using filesort | |  1 | simple      | b     | ref   | id_status          | id_status          | 9       | wtm_master.c.id,const |    1 | using where; using index                 | +----+-------------+-------+-------+--------------------+--------------------+---------+-----------------------+------+------------------------------------------+

the filesort worries me tells me mysql pulls matching rows first, before sorting. big problem when there 100,000+.

rows field in explain doesn't reflect number of rows read. reflects number of rows possible affected. doesn't rely on limit, because limit applied after plan has been calculated.

so don't need worry it.

also suggest swap last_change , category in type_proc_last_cat mysql can try use last index part (last_change) sorting purposes.


Comments

Post a Comment

Popular posts from this blog

c# - How to set Z index when using WPF DrawingContext? -

-
how set z-index drawing object when using drawingcontext.drawxxx() methods? the object drawn last have higher z index. can't change index of drawn objects. way draw in order. if using wpf (as placed tag), can use, example, canvas control. create shapes need like polyline obj = new polyline(); //... // ... set properties of obj and add them canvas uielementcollection: yourcanvasname.children.add(obj); //or yourcanvasname.children.insert(i, obj); first items of collection have higher z index. advantages in way: no need redraw on window changes, can anytime move objects , change order.
Read more

razor - Is this a bug in WebMatrix PageData? -

-
i think may have found bug in webmatrix's pagedata, not sure. concerns how pass data partial page calling page. in webmatrix documentation (tutorials, e.g. " 3 - creating consistent look ", , example code), pagedata recommended mechanism pass data between pages (e.g. content page layout page, or partial page). however have found not work other way, pass data partial page calling page. modifying or adding entries in pagedata in partial page, not seem calling page. cutting right down simplest possible example, in test page may have this: @{ pagedata["test"] = "initial entry"; } <p>before calling partial page, test value @pagedata["test"]</p> @renderpage("_testpartial.cshtml") <p>after returning calling page, test value @pagedata["test"]</p> and in _testpartial.cshtml page might have this: @{ pagedata["test"] = "modified entry"; } <p>in partial page...
Read more

android - layout with fragment and framelayout replaced by another fragment and framelayout -

-
edit: so after comments below, revisted , realized hanging me up. imagine client list , client details activity started : public class clientsmainactivity extends fragmentactivity { @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); //studiotabopenhelper db; setcontentview(r.layout.main_client_activity); } } so works great, starts main_client_activity (defined in layout below, , call activity when button on main screen clicked): intent intent = new intent(getactivity(), clientsmainactivity.class); startactivity(intent); easy issue is, clientsmainactivity not call oncreateview or anything, sets layout layout defines fragment, , listfragment. fine cause not trying pass clientsmainactivity , if have hypothetical activity like: sessionmainsactivity called when click on session edit of client, not calling sessionsmainactivity same way (starts activity sets alayout), want layout set defines...
Read more