|
Dobrica Pavlinušić's random unstructured stuff
MySQL: Revision 10
I don't like MySQL for various resons, most important one being that I saw PostgreSQL early on and never looked back. Here are few quick commands to ease my pain: Number of rows in tableselect count(*) from table_name is terrably slow in MySQL, so use information schema to quickly have report... mysql> select table_name,table_rows,avg_row_length,data_length from information_schema.tables where table_type = 'BASE TABLE' order by table_rows desc limit 10 ; +-----------------+------------+----------------+-------------+ | table_name | table_rows | avg_row_length | data_length | +-----------------+------------+----------------+-------------+ | zebraqueue | 2309022 | 87 | 200998912 | | sessions | 1744788 | 675 | 1178402816 | | items | 302540 | 126 | 38338560 | | biblio | 268391 | 166 | 44646400 | | action_logs | 225797 | 341 | 77168640 | | biblioitems | 220004 | 8159 | 1795080192 | | items_print_log | 205559 | 104 | 21528576 | | statistics | 130166 | 76 | 9977856 | | old_issues | 43852 | 84 | 3686400 | | import_biblios | 19352 | 136 | 2637824 | +-----------------+------------+----------------+-------------+ 10 rows in set (1.90 sec) Create grepable dumpPut every value in it's own line mysqldump -u user -p database --extended-insert=0 > dump.sql Poor man's graphs
mysql> select dateenrolled,count(*),rpad('',count(*) / 10,'*') from borrowers group by dateenrolled;
+--------------+----------+----------------------------------------------+
| dateenrolled | count(*) | rpad('',count(*) / 10,'*') |
+--------------+----------+----------------------------------------------+
| NULL | 18 | ** |
| 2008-04-03 | 1 | |
| 2008-04-17 | 1 | |
| 2008-05-28 | 1 | |
| 2008-06-05 | 1 | |
| 2008-06-12 | 3 | |
| 2008-07-28 | 1 | |
| 2008-10-17 | 1 | |
| 2008-10-20 | 1 | |
| 2008-10-21 | 1 | |
| 2008-10-27 | 11 | * |
| 2008-10-28 | 2 | |
| 2008-10-29 | 4 | |
| 2008-10-30 | 2 | |
| 2008-11-03 | 1 | |
| 2008-11-04 | 9 | * |
| 2008-11-06 | 7 | * |
| 2008-11-10 | 16 | ** |
| 2008-11-11 | 1 | |
| 2008-11-13 | 1 | |
| 2008-11-14 | 4 | |
| 2008-11-17 | 1 | |
| 2008-11-20 | 1 | |
| 2008-11-26 | 1 | |
| 2008-12-02 | 1 | |
| 2008-12-19 | 2 | |
| 2009-01-07 | 2 | |
| 2009-03-16 | 301 | ****************************** |
| 2009-03-17 | 365 | ************************************* |
| 2009-03-18 | 439 | ******************************************** |
| 2009-03-19 | 324 | ******************************** |
| 2009-03-20 | 226 | *********************** |
| 2009-03-23 | 105 | *********** |
+--------------+----------+----------------------------------------------+
33 rows in set (0.00 sec)
MySQL Koha utf-8 encodingconnect
use DBI();
my $dbh = DBI->connect ('dbi:mysql:test_db', $username, $password,
{mysql_enable_utf8 => 1}
);
my.cnf performance optimization
./mk-query-digest --processlist localhost --interval 0.01 |