sql - ADD INDEX crashes MySQL -


i trying optimize queries when try add indexes query:

alter table `user` add index `last_activity_date` (`last_activity_date`);

... crashes mysql server. mean websites not responding anymore , queries not executed. solution have restart mysql. it's annoying because have app hundreds of users can't use when it's crashed.

i have mysql log file don't see error in it. in opinion can problem here? because users interacting db while i'm adding indexes? use innodb.

i have more 100.000 records in user table looks :

create table `user` (  `id` int(11) unsigned not null auto_increment,  `encrypt_id` varchar(255) default null,  `register_date` datetime default null,  `last_login_date` datetime default null,  `username` varchar(255) default null,  `password` varchar(255) default null,  `email` varchar(255) default null,  `banned` int(11) default null,  `banned_reason` text,  `first_step_form` int(11) default '0',  `status` int(11) default null,  `referer` varchar(255) default null,  `rank` int(11) default null,  `fb_id` bigint(20) default null,  `last_activity_date` datetime default null,  primary key (`id`),  key `last_activity_date` (`last_activity_date`) ) engine=innodb default charset=latin1

presuming website pretty active, have 100000 users, , last_activity_date field updated every time user (anything), applying index on field wreak havoc. many rows take few minutes apply, , if not lock users out, index never created have keep updating before completed while users changed field (it locks users out prevent this).

the reason index field last_activity_date run reporting against (things such did user in last 30 days), or regular maintenance (disabling users haven't done in last 30 days). if such case, might better off creating secondary table , loading user table data, , applying index on table. reports won't against live data--but reports shouldn't need instantaneous results. updating secondary table once day or wouldn't take long--perhaps few minutes--and won't lock out users in process.


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