database - Partitions by null values with MySQL -

-

i have table:

create table `newtable` (     `iblock_element_id`  int(11) not null ,     `property_1836`  int(11) null default null ,     `description_1836`  varchar(255) character set cp1251 collate cp1251_general_ci null default null ,     `property_1837`  int(11) null default 0 ,     `description_1837`  varchar(255) character set cp1251 collate cp1251_general_ci null default null ,     `property_1838`  decimal(18,4) null default null ,     `description_1838`  varchar(255) character set cp1251 collate cp1251_general_ci null default null ,     `property_3139`  int(11) null default 0 ,     `description_3139`  varchar(255) character set cp1251 collate cp1251_general_ci null default null ,     `property_3173`  decimal(18,4) null default null ,     `description_3173`  varchar(255) character set cp1251 collate cp1251_general_ci null default null ,     primary key (`iblock_element_id`),     index `ix_perf_b_iblock_element_pr_1` (`property_1837`) using btree ,     index `ix_perf_b_iblock_element_pr_2` (`property_1836`) using btree ,     index `ix_perf_b_iblock_element_pr_3` (`property_3139`) using btree  ) engine=innodb default character set=cp1251 collate=cp1251_general_ci row_format=compact;

and query condition:

where property_3139 null

i can't change table or query. know, if i'll split table 2 partitions - queries selecting nullable values work faster.

what kind of trick can use this? null , not null not range , can't use list of values.

partition probably won't help.

null separate value in index. think of nulls being stored in index before other values. hence, is null , is not null can treated 'range' optimization purposes.

however... if more 20% (10%-30%, depending on phase of moon) of table in desired range, optimizer decide faster full table scan instead of bouncing , forth between index , data.

back probably...

  • if small number of rows have null, index fine; partitioning won't much.
  • if medium number of rows have null, partitioning noticeably.
  • if rows have null, full table scan scanning of 1 partition.

note: cannot partition on more 1 column. so, if partition on property_3139, rest of properties out of luck.


Comments

Post a Comment

Popular posts from this blog

How to run C# code using mono without Xamarin in Android? -

-
i want create program uses mono runtime run c# code (binary not application). i copied libmono.so unity app , i'm write code using documentation: http://www.mono-project.com/docs/advanced/embedding/ and it's working have problems references can access classes mscorlib.dll , have problems system.reflection.assembly class throwing exception, when print console see: system.missingmethodexception: method not found: 'system.reflection.assembly.op_inequality' can fix it? can use mono runtime in c binary on android? ps. i'm using android tests. edit: found solution loading libraries, need set environment variable mono_path libraries path. but have still problems assembly class. the system.missingmethodexception error have seems .net profile mismatch error, 1 explained here . can use mono runtime in c binary on android? yes, using ndk guess. there alternatives can use instead of rolling own, such xmadevlab .
Read more

html - grunt SVG to webfont -

-
i'm trying generate webfont svgs using grunt-webfont . the issue need task valid on windows , linux machines. in order achieve need use node engines doesn’t work svg files . i know if there way convert svgs proper svg format supported grunt plugin or alternatively there better grunt plugin/s achieve this i have task able take svgs (created in illustrator) , output webfont files (.eot,.svg,.svg,...) , css file (like icomoon export) thanks. perhaps artboard small. recommend checking have 512pt x 512pt artboard each icon. stumbled upon gem ( https://www.fourdigits.nl/blog/using-a-webfont-to-display-icons ) when grunt-webfont documentation left me alone in forest.
Read more

python - Specify path of savefig with pylab or matplotlib -

-
i struggling on how find correct way specify save path (or repository) when calling function savefig in matplotlib or pylab. i tried several syntaxes, everytime python console returns : filenotfounderror: [errno 2] no such file or directory: '../mydocs/resource/frames/myimage.png' currently, have written following : pylab.savefig('../mydocs/resource/frames/myimage.png') does know how it? thanks in advance! the tilde operator , variable $home entered strings , therefore not function while saving. have provide either relative path (as did) or provide full path. eg. pylab.savefig("/home/username/desktop/myfig.png") .
Read more