mysql - workaround for "Too many keys specified; max 64 keys allowed" error in MariaDB / TokuDB? -


i need create more 64 indexes on table running "too many keys specified; max 64 keys allowed" error. there workaround allow me increase limit beyond 1000 mariadb / tokudb? or there reason why limit necessary?

(i've seen question asked/answered mysql - answers being either pass --with-max-indexes=256 ./configure, or modify max_key in 1 of header files @ compile time. unfortunately, these answers don't appear work mariadb)

ps. since typical response "if need many indexes you're doing wrong", i'll explain why want this, , appreciate advice on modifying design if that's best "workaround".

my data stored in 2 tables:

table1 stores 5 columns: (unique key x_position int, column1 string, column2 float, column3 int, column4 tinyint) - can large 100 million rows

table2 conceptually can represented 4 columns: (foreign key x_position int, sample_id string, value1 tinyint, value2 float) - since there 5000 unique sample_id values, , different value1 exist each (x_position, sample_id) pair, max number of rows 100 million x 5000 = 500 billion rows

the queries need like: 

select  column1, column2, column3... sample_id,         group_concat(value1)      table1, table2      column1 = string1       ,  column2 < float2       ,  ( (sample_id = string1  ,  value1=1)          or  (sample_id = string2  ,  value1=0)          or  (sample_id = string3  ,  value1=1)            )       ,  value2 < float1     group  sample_id;

instead, thinking more efficient pivot table2 it's columns are: (foreign key x_position, sample_id1_value1 tinyint, sample_id1_value2 float, sample_id2_value1 tinyint, sample_id2_value2 float, ...)

and create composite indexes on small subsets of (sample_id1_value1, sample_id1_value2, .. ) columns based on domain-specific details of of these columns queried together. table have 100 million rows x 10,000 columns (split across several tables avoid column limit) seems better 500 billion rows. eliminate need "or" , "group by" clauses in queries, allowing queries rewritten like:

select  column1, column2, column3... sample_id,         sample_id1_value1,         sample_id1_value2      table1, table2      column1 = string1       ,  column2 < float2       ,  sample_id1_value1=1       ,  sample_id2_value1=0       ,  sample_id3_value1=1       ,  sample_id1_value2 < float1       ,  sample_id2_value2 < float1       ,  sample_id3_value2 < float1;

unfortunately "too many keys" error getting in way of this.


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more