Dobrica Pavlinušić's random unstructured stuff

MySQL: Revision 5

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:

{toc: }

^ Number of rows in table

`select count(*) from table_name` is terrably slow in MySQL, so use "information schema"<http://dev.mysql.com/doc/refman/5.1/en/tables-table.html> to quickly have report...

.pre
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)
.pre

^ Create grepable dump

Put every value in it's own line

.pre
mysqldump -u user -p database --extended-insert=0 > dump.sql
.pre

^ Poor man's graphs

.pre
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)
.pre

^ MySQL Koha utf-8 encoding

^^ connect

.pre
use DBI();

my $dbh = DBI->connect ('dbi:mysql:test_db', $username, $password,
{mysql_enable_utf8 => 1}
);
.pre