indexing - index a bit field in MYSQL -

-

updated question:
suppose data i'm interested in field=1 , actual ratio of data wheere field 1 vs 0 small(eg. 1%) in case, index field benefit select field =1 query?

original question:
have int field have either 0 or 1 value, indexing field speed select queries such as:

 select * xxx field=1;

generally speaking, no. bi-state field doesn't speed queries when indexed because have @ half rows on average. want index entries selective - given entry in index should represent small percentage of possible values (say, less 10%, preferably fractions of percent). using index ignores of data in table, gives performance benefit.

some dbms support bitmap indexes. can help, still run problem of selectivity.

the updated question says number of values value 1 small (less 1 percent); index give benefit now?

the answer is:

  • for queries specify value 1, yes, index on column provide benefit, provided optimizer makes use of index. may need tweak dbms make realize index skewed in favour of using queries value 1; tends dbms-specific, updating statistics in various guises name of game, possibly using hints in sql queries too. of course, if optimizer never uses index, still provides no benefit - , optimizer may decide other indexes more in way.

  • for queries value 0, index should not used. chances are, though, the dbms continue maintain index 0 values - though should never use them. unusual dbms commanded 'only index column values other zero', though beneficial.

so - depends. depends on queries, , depends on optimizer.

note composite index - on other customarily used columns , bit-field may provide benefit. so, if select on date range, composite index on date , bit-field columns (probably in order) should provide index.


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