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.