mysql - What Are Good Solutions for a Database Table that Gets to Long? -


i describe problem using specific scenario:

imagine create website towhich users can register, , after register, can send private messages each other.

this website enables every user maintain own friends list, , maintain blocked users list, prefers not messages.

now problem:

imagine website getting several millions of users, , imagine every user has 10 friends in friend table, , 10 blocked users in blocked users table.

the friends list table, , blocked users table, become long, worse that, every time when wants send message person "x", need go on whole blocked users table, , records user "x" defined - people blocked.

this "scanning" of long database table seems bit inefficient me.

so have 2 questions it:

1) possible solutions problem? not afraid of long database tables, afraid of database tables contain data many users, means whole table needs scanned every time, pull out few records specific user.

2) specific solution have in mind, , ask about:

one solution have in mind problem, every user registers website, have own "mini-database" created him, way friends table, blocked users table, contain only records him.

this makes scanning table easy, because records him.

does idea exist in databases ms-sql server, or mysql? , if yes, solution described problem? (each user have own small database created him, , of course there main (common) database other data not user specific)

thank all

i wait on partitioning , on creating mini-database idea. database installed data, log , temp files on different raid drives? have clustered indexes on tables , indexes on search , join columns?

have tried kind of reading query plans see how , slowdowns occurring? don't add memory or try advanced features blindly before doing basics.

creating separate databases become maintenance nightmare , challenging type of queries (for users....) in future.

partitioning wonderful feature of sql server , while in 2014 can have thousands of partitions (unless put each partition on separate drive) won't see big performance bump looking for.

sql server has fast response time tables (especially tables 10s of millions of rows (in case user table)). don't let main table wide , response time extremely fast.


Comments

Post a Comment

Popular posts from this blog

cakephp - simple blog with croogo -

i not problem, made simple blog in croogo, problem is not adding or modifying or delete, because arabic language ?? function add in controller `public function admin_add() { $this->set('title_for_layout', __('add part')); if (!empty($this->request->data)) { $this->part->create(); if ($this->part->save($this->request->data)) { $this->session->setflash(__('the part has been saved'), 'default', array('class' => 'success')); $this->redirect(array('action' => 'index'));//, $this->part->id)); } else { $this->session->setflash(__('the part not saved. please, try again.'), 'default', array('class' => 'error')); } } }` => model `class part extends appmodel { public $name = 'part'; var $belongst...
Read more

How to group boxplot outliers in gnuplot -

i have large set of data points. try plot them boxplot, of outliers exact same value , represented on line beside each other. found how set horizontal distance between outliers in gnuplot boxplot , doesn't much, apparently not possible. is possible group outliers together, print 1 point , print number in brackets beside indicate how many points there are? think make more readable in graph. for information, have 3 boxplots 1 x value , times 6 in 1 graph. using gnuplot 5 , played around pointsize, doesn't reduce distance anymore. hope can help! edit: set terminal pdf set output 'dat.pdf' file0 = 'dat1.dat' file1 = 'dat2.dat' file2 = 'dat3.dat' set pointsize 0.2 set notitle set xlabel 'x' set ylabel 'y' header = system('head -1 '.file0); n = words(header) set xtics ('' 1) set [i=1:n] xtics add (word(header, i) i) set style data boxplot plot file0 using (1-0.25):1:(0.2) boxplot lw 2 lc rgb '#8b0000...
Read more

bash - Performing variable substitution in a string -

i have string contains use of variable, , i'd substitute variable's value string. right best have is #!/bin/bash foo='$name; echo bar' name="the name" expanded="$(eval echo "$foo")" echo "$expanded" which has obvious defects: prints the name bar while i'd print the name; echo bar instead of eval can bash's regex matching bash's string replacement: foo='$name; echo bar' name="the name" [[ "$foo" =~ \$([[:alnum:]]+) ]] && s="${!bash_rematch[1]}" && expanded="${foo/\$${bash_rematch[1]}/$s}" echo "$expanded" name; echo bar
Read more