mysql - Is it better to have many columns, or many tables? -

-

imagine hypothetical database, storing products. each product have have 100 attributes, although given product have values set ~50 of these. can see 3 ways store data:

  1. a single table 100 columns,

  2. a single table few (say 10 columns have value every product), , table columns (product_id, attribute, value). i.e, eav data store.

  3. a separate table every columns. core products table might have 2 columns, , there 98 other tables, each 2 columns (product_id, value).

setting aside shades of grey between these extremes, pure efficiency standpoint, best use? assume depends on types of queries being run, i.e. if queries several attributes of product, or value of single attribute several products. how affect efficiency?

assume mysql database using innodb, , tables have appropriate foreign keys, , index on product_id. imagine attribute names , values strings, , not indexed.

in general sense, asking whether accessing big table takes more or less time query many joins.

i found similar question here: best have hundreds of columns or split multiple tables?

the difference is, question asking specific case, , doesn't tell me efficiency in general case. other similar questions talking best way organize data, want know how different organizational systems impact speed of queries.

in general sense, asking whether accessing big table takes more or less time query many joins.

join slower.

however, if query specific subset of columns, , subset "vertically partitioned" own separate table, querying such "lean" table typically quicker querying "fat" table columns.

but specific , fragile (easy break-apart system evolves) situation , should test before going down path. default starting position should 1 table.


Comments

Post a Comment

Popular posts from this blog

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more