mysqltips
 

\s
?
\c
\Pless

----------------------------------------

select count(*) as count,subtype from table where description='within_species_paralog' group by subtype order by count;

----------------------------------------

# kill a process
show processlist;
kill id;

----------------------------------------

This is the command I am using (not sure it is the best one, actually it
crashes sometimes...)

mysqldump -u ensro -h
ia64g --extended-insert --compress --delayed-insert --databases DBNAME |
mysql -u ensro -h method1

If you want to change the dbname:

mysqldump -u ensro -h ia64g OLD_DBNAME | mysql -u ensro -h
method1 NEW_DBNAME


If you want to resume a copy (for some tables only, after a crash):

mysqldump -u ensro -h ia64g --extended-insert --compress --delayed-insert
DBNAME TABLE1 TABLE2 TABLE3 | mysql -u ensro -h method1 DBNAME


- String functions in mysql
  SELECT LEFT('foobarbar', 5);
          -> 'fooba'
  SELECT SUBSTRING('Quadratically',5);
          -> 'ratically'
  SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
          -> 'ki'    
  SELECT SUBSTRING('foobarbar' FROM 4);
          -> 'barbar'
  CONCAT(biotype,'new')

########################################

"show table status"

########################################

mysql -hserver -uensro project_method_46 -e "select concat(substring(name FROM 1 FOR 1),substring(name FROM (1+position(' ' in name)) FOR 3)) as Name from genome_db"

########################################

CREATE TABLE dice (
  d_id int(11) NOT NULL auto_increment,
  roll int,
  PRIMARY KEY  (d_id)
);

insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);


select roll from dice order by rand() limit 1;

########################################

# this is a left join but on a specific type of tag in the second table, not a "IS NULL" left join

mysql -hmethod2 -uensro login_method_problem_49 -N -e "select distinct ptn.node_id from protein_tree_node ptn LEFT JOIN protein_tree_tag ptt ON ptn.node_id=ptt.node_id WHERE ptn.root_id=ptn.parent_id and ptn.node_id not in (0,1) group by ptn.node_id having group_concat(ptt.tag) not like \"%cluster_had_to_be_broken_down%\""

########################################

# rlike
mysql -hmethod2 -uensro login_method_problem_49 -e "select count(*) as pos3_4, count(*)/s.length as prop, concat(\"http://www.project.org/human/geneview?gene=\",m2.stable_id), m2.display_label, avg(sa.omega_lower) as avgl, avg(sa.omega_upper) as avgu, std(sa.omega_upper) as stdu from sitewise_member sm, sitewise_aln sa, member m1, member m2, sequence s where s.sequence_id=m1.sequence_id and sm.member_id=m1.member_id and m1.gene_member_id=m2.member_id and sa.sitewise_id=sm.sitewise_id and sa.type rlike (\"positive[2-4]\") and m2.display_label like \"%PR%\" group by m2.stable_id order by m2.display_label limit 50


-- by Justin Silverton

The following are five ways to improve queries involving table inserts:

1) use LOAD DATA INFILE when loading data from a text file

This is around 20 times faster than using insert statements.

2) use INSERT statements with multiple VALUES lists to insert several rows at a time

This is many times faster than using separate single-row insert statements. Tuning the bulk_insert_buffer_size variable can also make inserts (to tables that contain rows) even faster.

3) enable concurrent inserts for myisam tables

The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to 1. If concurrent_inserts is set to 0, concurrent inserts are disabled. If the variable is set to 2, concurrent inserts at the end of the table are allowed even for tables that have deleted rows.

4) use insert delayed

This is useful if you have clients that cannot or need not wait for the insert to complete. This is a common situation when you use MySQL for logging and you also periodically run select and update statements that take a long time to complete. When a client uses insert delayed, the server returns right away, and the row is queued to be inserted when the table is not in use by any other thread. Another benefit of using insert delayed is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

5) lock your tables before inserting (for non-transactional tables)

This benefits performance because the index buffer is flushed to disk only once, after all insert statements have completed. Normally, there would be as many index buffer flushes as there are insert statements. Explicit locking statements are not needed if you can insert all rows with a single insert.

To obtain faster insertions for transactional tables, you should use start transaction and commit instead of lock tables.

# remove duplicate entries
mysql -hmethod2 -uensro login_method_problem_49 -e "ALTER IGNORE TABLE sitewise_aln ADD UNIQUE INDEX(aln_position,node_id)"

########################################

# group_concat in groups of 100

mysql -hmethod2 -uensro login_method_problem_49 -N -e "SET @var = -1; SELECT group_concat(problem_id), FLOOR((@var := @var + 1)/ 100) AS grouper FROM problem where method_link_species_set_id=20834 GROUP BY grouper" > problem_id.txt

########################################

# Proccesslist sorted by time taken by query

mysql -hmethod2 -uensro  -e "show full processlist" | awk '{print $6, $5, $4, $3, $2, $1, $7, $8, $9, $10}' | sort -rn -k 2 | less

# Derived tables

mysql> SELECT * FROM ( select cast(trim(TRAILING "\, 'clusterset_id'\=\>1\}" FROM trim(LEADING "\{'protein_tree_id'\=\>" FROM aj.input_id)) AS UNSIGNED INT) as root_id, aj.analysis_job_id from analysis_job aj where aj.analysis_id=14) AS aj JOIN protein_tree_tag p ON p.node_id=aj.root_id limit 10;
+---------+-----------------+---------+-------------------------------+-------+
| root_id | analysis_job_id | node_id | tag                           | value |
+---------+-----------------+---------+-------------------------------+-------+
|   20199 |        26845774 |   20199 | gene_count                    | 831   |
|   20199 |        26845774 |   20199 | include_brh                   | 1     |
|   20199 |        26845774 |   20199 | bsr_threshold                 | 0.33  |
|   20199 |        26845774 |   20199 | cluster_had_to_be_broken_down | 1     |
|   25020 |        26845938 |   25020 | gene_count                    | 1147  |
|   25020 |        26845938 |   25020 | include_brh                   | 1     |
|   25020 |        26845938 |   25020 | bsr_threshold                 | 0.33  |
|   25020 |        26845938 |   25020 | cluster_had_to_be_broken_down | 1     |
|   30050 |        26846055 |   30050 | gene_count                    | 860   |
|   30050 |        26846055 |   30050 | include_brh                   | 1     |
+---------+-----------------+---------+-------------------------------+-------+

########################################

echo -e `mysql -umethod2 -uensro -p -N -e "select concat('Foo',CHAR(10),'Bar')"` | egrep -v blah

conditional group by

select count(if(paf.hit_rank=1,1,NULL)) as firstrank, count(if(paf.hit_rank=1,NULL,1)) as otherrank, g.name from peptide_align_feature_caenorhabditis_elegans_68 paf, genome_db g where paf.hgenome_db_id=g.genome_db_id group by g.name

LEFT JOIN 3 tables

select * from (table1 left join table2 on table1.id = table2.id) left join table3 on table1.id = table3.id;

mysql -hmethod2 -uensro login_method_problem_hc52 -e "SELECT count(*) FROM (member m LEFT JOIN protein_tree_member ptm ON m.member_id=ptm.member_id) LEFT JOIN subset_member sm ON m.member_id=sm.member_id WHERE ptm.member_id iS NULL AND m.source_name=\"PROJECTPEP\" AND m.genome_db_id=22"

UPDATE JOIN

mysql -hmethod2 -uanonymous -pxxxx login_method_problem_hc52 -e "update analysis_job aj1 join analysis_job aj2 on aj2.prev_analysis_job_id=aj1.analysis_job_id set aj1.status=\"DONE\" where aj1.analysis_id=13 and aj2.analysis_id=14"