Category Archives: Database

JPA inheritance performance improvement

This article is about improving JPA's performance when working with large/thick hierarchies.

Let's suppose that for some reason you have a similar (or larger) hierarchy to one I encountered in one of my projects:
- InheritanceType.JOINED also using DiscriminatorColumn
- 4 levels hierarchy (including root entity)
- 24 entities actually making the hierarchy
- 7 properties as an average for each entity (excluding @Id)
- other entities are referenced by those actually making the hierarchy
- using hibernate JPA 2.1 api (though this might not be important)

When retrieving an entity I face the situation of about 70 tables being joined (24 from the hierarchy and 46 other referenced entities) and the performance is pathetic. Of course I have the appropriate indexes painfully chosen by testing every query with EXPLAIN ANALYZE.

Part of the problem is that I also have a large table for the hierarchy's root (26 million of rows). The other part is the way JPA is working: I mean when I try to get the root entities filtered by few discriminator types then JPA creates a 70 tables SELECT instead of using only the tables making sense for those specific discriminators. 

Let's name RootEntity the root entity of my JPA hierarchy. Let's also suppose I have Level4Entity (with 4a as discriminator) which inherits from Level3Entity which inherits from Level2Entity which inherits RootEntity. When querying for RootEntity using 4a and 4b discriminator types as criteria then JPA is using the same query as when querying for RootEntity's entire hierarchy (70 tables as a total). Should JPA only use the tables required by Level4Entity's specific sub-hierarchy (e.g. 12 tables: 4 for hierarchy and other 8 referenced) then the performance would be much better. Even when simply retrieving a RootEntity entity by its @Id the timing is 12 seconds!

The client cares only for the performance (while using same hardware) asks: can you do better? Well, let's now just imagine the totally crazy idea of using InheritanceType.SINGLE_TABLE instead of InheritanceType.JOINED. For me this would mean an 177 columns table for RootEntity's and liquibase scripts in order to copy the columns and data from previous tables to the RootEntity's table, refactor FKs and indexes. But would this effort worth? Well, if your database supports materialized views you could test this approach before changing your code and database. You could simply create a materialized view based on the actual 70 tables SELECT generated by JPA then you could test the retrieving of one row by pk (somehow equivalent to the previously retrieving by @Id which took 12 seconds). If you really want the best comparison than you should include in the materialized view only the tables used directly by the hierarchy (24 tables) which is not so straight forward but still easy to do. For me this approach allowed for a 450 ms when retrieving an entity instead of 12 s as before - quite a huge difference. 

So yes, changing the hierarchy to InheritanceType.SINGLE_TABLE and using a many-columns table with many possible null columns was by far faster then using InheritanceType.JOINED approach. And not only the retrieve was faster but also the INSERT (I don't remember exactly by how much but at least 5 times faster). 

So, what are the pro and cons of the approach?

PROS: 
- speed: both for query and insert
- sql simplicity: I find easier to read a many columns SELECT instead of many JOIN SELECT

CONS: 
- database structure: you'll have 1 many columns table versus many tables
- database consistency: columns which were NOT NULL in their dedicated table should now accept NULL in order to account for the situation when their row might not exists for a specific sub-hierarchy

Observation: with Oracle table partitioning on the join columns of the hierarchy I guess (but never tested) you should gain same performance benefit while still using InheritanceType.JOINED

Ubuntu and Oracle

# see also https://wiki.centos.org/HowTos/Oracle12onCentos7
# see also https://adrhc.go.ro/wordpress/centos-and-oracle/

# Follow this (works with Ubuntu 16.04 too):
# http://www.techienote.com/install-oracle-12c-on-ubuntu/

# systemd oracle.service (working when only one db is automatically started with /etc/oratab)
[Unit]
Description=Oracle 12c
After=local-fs.target
Wants=local-fs.target

[Service]
Type=forking

User=oracle
Group=oinstall

RuntimeDirectory=oracle
PIDFile=/run/oracle/oracle.pid

Restart=on-failure
RestartSec=3

TimeoutSec=0

ExecStart=/u01/app/oracle/product/12.1.0/dbhome_1/bin/dbstart /u01/app/oracle/product/12.1.0/dbhome_1
ExecStop=/u01/app/oracle/product/12.1.0/dbhome_1/bin/dbshut /u01/app/oracle/product/12.1.0/dbhome_1

[Install]
WantedBy=multi-user.target

# modify dbstart and dbshut in order to create /run/oracle/oracle.pid needed by oracle.service
# /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbstart
startinst() {
...
      if [ $? -eq 0 ] ; then
        echo "" 
		OS_PID=$(sqlplus -S / AS SYSDBA <<EOF
select SPID from v\$process where PNAME = 'PMON';
EOF
)
		OS_PID=$(echo "$OS_PID" | /bin/grep -P "\d+")
        echo "$0: ${INST} \"${ORACLE_SID}\" warm started (PID $OS_PID)." 
		if [ -d /run/oracle ]; then
			echo "$OS_PID" > /run/oracle/oracle.pid
			echo "created /run/oracle/oracle.pid"
		fi
      else
        $LOGMSG "" 
        $LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started." 
      fi
# /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbshut
  if test $? -eq 0 ; then
	if [ -f /run/oracle/oracle.pid ]; then
		# see /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbstart
		rm -fv /run/oracle/oracle.pid
	fi
    echo "${INST} \"${ORACLE_SID}\" shut down."
  else
    echo "${INST} \"${ORACLE_SID}\" not shut down."
  fi

JPA performance

http://java-persistence-performance.blogspot.ro/2011/06/how-to-improve-jpa-performance-by-1825.html#
http://spitballer.blogspot.ro/2010/04/jpa-persisting-vs-merging-entites.html

Use byte code weaving
EclipseLink implements LAZY for OneToOne and ManyToOne relationships using byte code weaving

Pagination
Use setFirstResult, setMaxResults of javax.persistence.Query.

Caching
See https://docs.oracle.com/javaee/7/tutorial/persistence-cache001.htm#GKJIO.
See <shared-cache-mode> in persistence.xml or javax.persistence.sharedCache.mode property when creating the EntityManagerFactory.
See javax.persistence.Cacheable used to annotate an entity.

JDBC batching (insert/update only)
See http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#batch.
hibernate.jdbc.batch_size: a non-zero value enables use of JDBC2 batch updates by Hibernate. e.g. recommended values between 5 and 30

JDBC fetch size (retrieve only)
hibernate.jdbc.fetch_size: a non-zero value determines the JDBC fetch size (calls Statement.setFetchSize())
Statement.setFetchSize: gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.
In my opinion the best explanation is here: https://docs.oracle.com/cd/E11882_01/java.112/e16548/resltset.htm#JJDBC28621
Excerpt: Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size.

Hibernate batch size (@BatchSize)
http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#performance-fetching
@BatchSize specifies a "batch size" for fetching instances of this class by identifier. Not yet loaded instances are loaded batch-size at a time (default 1).

Hibernate fetching strategies (@Fetch)
http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#performance-fetching
http://www.mkyong.com/hibernate/hibernate-fetching-strategies-examples/
Fetching strategies: join fetching, select fetching, subselect fetching, batch fetching.

other
http://www.jroller.com/eyallupu/entry/solving_the_simultaneously_fetch_multiple1

hibernate.default_batch_fetch_size
http://stackoverflow.com/questions/21162172/default-batch-fetch-size-recommended-values
http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch03.html
see also hibernate.batch_fetch_style at http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#performance-fetching
https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/loader/BatchFetchStyle.java specifies LEGACY as the default for hibernate.batch_fetch_style

@MapKey vs @MapKeyColumn

// @MapKey refers the field/property (unique one) in the Entity witch is the value of a Map field/property
// @MapKeyColumn refers the column in an @ElementCollection

@Entity
public class Person6 {
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "person6")
    @MapKey(name = "address") // is the Address6's address property
    public Map<String, Address6> getAddress6Map() {
        return address6Map;
    }
...

@Entity
public class Address6 {
    private Integer id;
    private String address;
    private Person6 person6;
...

Ubuntu: phpMyAdmin setup

wget https://files.phpmyadmin.net/phpMyAdmin/4.5.0.2/phpMyAdmin-4.5.0.2-english.tar.gz
tar xvzf phpMyAdmin-4.5.0.2-english.tar.gz
mv phpMyAdmin-4.5.0.2-english myadmin
mkdir myadmin/config
chown -R ************ myadmin/config

/etc/php5/fpm/php.ini
session.gc_maxlifetime = 3600
sudo service php5-fpm restart

#save configuration created using https://adrhc.go.ro/myadmin/setup
mv -v myadmin/config/config.inc.php myadmin/config.inc.php
sudo chown ************ myadmin/config.inc.php
rmdir myadmin/config
sudo service php5-fpm restart

#pma tables (phpMyAdmin configuration storage)
#pma = php My Admin
#Use phpMyAdmin in order to execute the sql content of myadmin/sql/create_tables.sql.
#Disable the phpmyadmin-DB creation if you want the pma tables to be included in the current database.

#config.inc.php
/* Servers configuration */
$i = 0;

/* Server: localhost [1] */
$i++;
$cfg['Servers'][$i]['verbose'] = 'mysql asrock';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['auth_http_realm'] = 'phpMyAdmin';

/* don't know the purpose of these commented & not required settings
$cfg['Servers'][$i]['user'] = 'db user name';
$cfg['Servers'][$i]['password'] = 'db password';
*/

$cfg['Servers'][$i]['controluser'] = 'db user name for accessing the pmadb below named phpmyadmin';
$cfg['Servers'][$i]['controlpass'] = 'db password for controluser';
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';
/* End of servers configuration */

Create mysql system db

# see also https://adrhc.go.ro/wordpress/mysql-command-line/

#CREATE SYSTEM DB (first step and mandatory before using mysql)
export SRVPATH=/ffp/opt/srv
mkdir -p $SRVPATH/mysql/innodb/
mkdir -p $SRVPATH/mysql/innodblogdir/
mkdir -p $SRVPATH/mysql/binlog/
mkdir -p $SRVPATH/mysql/log/
mkdir -p $SRVPATH/mysql/tmp/
mkdir -p $SRVPATH/mysql/data
cd /ffp
# use your my.cnf otherwise mysql won't create some innodb tables (see http://bugs.mysql.com/bug.php?id=67179&files=1):
scripts/mysql_install_db --user=root --datadir=/ffp/opt/srv/mysql/data --defaults-file=/ffp/etc/my.cnf
# if you don't have a my.cnf than run:
#scripts/mysql_install_db --user=root --datadir=/ffp/opt/srv/mysql/data
#Later (see mysql-five-tables-5.6.25.sql below) you'll create innodb missing tables.
cd ~ && /ffp/start/mysqld.sh start
mysql -p -> the default password is nothing
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'xxx321' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'xxx321' WITH GRANT OPTION;
FLUSH PRIVILEGES;
mysqladmin -u root password "xxx321"

#1. create databases and grant privileges
mysql -p
CREATE DATABASE exifweb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE wordpress CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE owncloud702 CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE ghost CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE pydio CHARACTER SET utf8 COLLATE utf8_unicode_ci;
GRANT ALL ON exifweb.* TO 'exifweb'@'%' IDENTIFIED BY 'exifweb' WITH GRANT OPTION;
GRANT ALL ON wordpress.* TO 'wordpress'@'%' IDENTIFIED BY 'wordpress' WITH GRANT OPTION;
GRANT ALL ON owncloud702.* TO 'owncloud702'@'%' IDENTIFIED BY 'owncloud702' WITH GRANT OPTION;
GRANT ALL ON ghost.* TO 'ghost'@'%' IDENTIFIED BY 'ghost' WITH GRANT OPTION;
GRANT ALL ON pydio.* TO 'pydio'@'%' IDENTIFIED BY 'pydio' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#2. prepare sql for databases to restore
cp /i-data/md0/seagate-ext4/ProjectsNew/nsa310-config/trunk/mysql-db-design/*.sql.gz $HOME/temp/mysql-restore
cd $HOME/temp/mysql-restore
gunzip exifweb.sql.gz
gunzip wordpress.sql.gz
gunzip owncloud702.sql.gz
gunzip ghost.sql.gz
gunzip pydio.sql.gz
ls -l *.sql

#3. restore DBs
# The config variable max_allowed_packet must be larger than the imported sql script!
#see http://bugs.mysql.com/bug.php?id=67179&files=1
# Take five-tables.sql from me (it's a copy of the original):
wget https://adrhc.go.ro/public/mysql-five-tables-5.6.25.sql
# or take it from the original post:
#wget http://bugs.mysql.com/file.php?id=19725&bug_id=67179
mv 'file.php?id=19725&bug_id=67179' mysql-five-tables-5.6.25.sql
# this solves the innodb missing tables problem:
mysql -p mysql < mysql-five-tables-5.6.25.sql
# now import your databases:
mysql -u root -p exifweb < nsa310-config-trunk/mysql-db-design/exifweb.sql
mysql -u root -p wordpress < nsa310-config-trunk/mysql-db-design/wordpress.sql
mysql -u root -p owncloud702 < nsa310-config-trunk/mysql-db-design/owncloud702.sql
mysql -u root -p ghost < ghost.sql
mysql -u root -p pydio < pydio.sql
mysqlcheck -A --password=xxx321 --auto-repair

#UPGRADE DB schema
#If you use InnoDB, consider setting innodb_fast_shutdown to 0 before shutting down and upgrading your server.
/ffp/start/mysqld.sh stop
sed -i s/"innodb_fast_shutdown\s*=\s*1"/"innodb_fast_shutdown = 0"/ /ffp/etc/my.cnf
grep innodb_fast_shutdown /ffp/etc/my.cnf
/ffp/start/mysqld.sh start
/ffp/start/mysqld.sh stop
/ffp/start/mysqld.sh start
mysql_upgrade -u root -p --socket=/ffp/var/run/mysql/mysql.sock -v
/ffp/start/mysqld.sh stop
sed -i s/"innodb_fast_shutdown\s*=\s*0"/"innodb_fast_shutdown = 1"/ /ffp/etc/my.cnf
grep innodb_fast_shutdown /ffp/etc/my.cnf
/ffp/start/mysqld.sh start

Compiling mysql 5.6.24

#See https://adrhc.go.ro/wordpress/common-commands-when-building/ for building environment, x.sh script and other things not defined here.
#Before starting declare the environment variables specified at the link above.

NEW_BUILD_NAME=mysql
#NEW_BUILD_VER=5.6.24.large
NEW_BUILD_VER=5.6.24.small+innodb
NEW_BUILD_NAME_AND_VER=$NEW_BUILD_NAME-$NEW_BUILD_VER
ls -l /tmp/$NEW_BUILD_NAME-*
ls -l ~/ffp_0.7_armv5/packages/$NEW_BUILD_NAME-*
NEW_BUILD_NR=0

cd $HOME/compile
rm -r $HOME/compile/$NEW_BUILD_NAME_AND_VER
tar -xzvf mysql-5.6.24.tar.gz
cd $HOME/compile/$NEW_BUILD_NAME_AND_VER
~/x.sh ffpg

#all params with help text
#cmake . -LAH > cmake.help.txt

#Clear/reset previously run configure & generate:
make clean
rm CMakeCache.txt

#large = embedded + archive + federated + blackhole + innodb
#cmake . -DCMAKE_BUILD_TYPE=RELEASE -DCMAKE_INSTALL_PREFIX=/ffp -DFEATURE_SET=large -DMYSQL_DATADIR=/ffp/opt/srv/mysql/data -DSYSCONFDIR=/ffp/etc -DTMPDIR=/ffp/opt/srv/mysql/tmp -DWITH_DEBUG=OFF -DCMAKE_VERBOSE_MAKEFILE=ON -DCMAKE_EXPORT_COMPILE_COMMANDS=ON -DHAVE_LLVM_LIBCPP=1 -DCMAKE_CXX_FLAGS_RELEASE="$BUILD_FLAGS" -DCMAKE_C_FLAGS_RELEASE="$BUILD_FLAGS" -DHAVE_GCC_ATOMIC_BUILTINS="" -LAH

#small = embedded
#below we have small + innodb
cmake . -DCMAKE_BUILD_TYPE=RELEASE -DCMAKE_INSTALL_PREFIX=/ffp -DFEATURE_SET=small -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/ffp/opt/srv/mysql/data -DSYSCONFDIR=/ffp/etc -DTMPDIR=/ffp/opt/srv/mysql/tmp -DWITH_DEBUG=OFF -DCMAKE_VERBOSE_MAKEFILE=ON -DCMAKE_EXPORT_COMPILE_COMMANDS=ON -DHAVE_LLVM_LIBCPP=1 -DCMAKE_CXX_FLAGS_RELEASE="$BUILD_FLAGS" -DCMAKE_C_FLAGS_RELEASE="$BUILD_FLAGS" -DHAVE_GCC_ATOMIC_BUILTINS="" -LAH

#ERROR (FEATURE_SET=large)
	/ffp/home/root/compile/mysql-5.6.24/sql/sql_table.cc:2020:7: error: 'old_part_info' was not declared in this scope
	   if (old_part_info)
		   ^
	make[2]: *** [sql/CMakeFiles/sql.dir/sql_table.cc.o] Error 1
#SOLUTION
	#Modify /ffp/home/root/compile/mysql-5.6.24/sql/sql_table.cc to this:
	end:
	#ifdef WITH_PARTITION_STORAGE_ENGINE
	  if (old_part_info)
	  {
		lpt->table->file->set_part_info(old_part_info, false);
	  }
	#endif
	DBUG_RETURN(error);

nohup /ffp/bin/make -C . &
renice -11 `pidof cc1plus` -p `pidof make` -p `pidof as` -p `pidof ld` -p `pidof configure` -p `pidof cc1` -p `pidof ccmake`
tail -f nohup.out

rm -v /tmp/$NEW_BUILD_NAME-*.txz
rm -r $HOME/temp/$NEW_BUILD_NAME_AND_VER
make install DESTDIR=$HOME/temp/$NEW_BUILD_NAME_AND_VER
cd $HOME/temp/$NEW_BUILD_NAME_AND_VER
makepkg $NEW_BUILD_NAME $NEW_BUILD_VER $NEW_BUILD_NR
cp -v /tmp/$NEW_BUILD_NAME-$NEW_BUILD_VER-arm-$NEW_BUILD_NR.txz ~/ffp_0.7_armv5/packages/
funpkg -q $NEW_BUILD_NAME
funpkg -i ~/ffp_0.7_armv5/packages/$NEW_BUILD_NAME-$NEW_BUILD_VER-arm-$NEW_BUILD_NR.txz

# see also CREATE SYSTEM DB (first step and mandatory before using mysql)
# search my blog for: CREATE SYSTEM DB

Tomcat datasource in context.xml

<Resource name="exifweb"
		  auth="Container"
		  type="javax.sql.DataSource"
		  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
		  testWhileIdle="true"
		  testOnBorrow="true"
		  testOnReturn="false"
		  validationQuery="SELECT 1"
		  validationInterval="30000"
		  timeBetweenEvictionRunsMillis="30000"
		  maxActive="10"
		  minIdle="2"
		  maxIdle="3"
		  maxWait="5000"
		  initialSize="1"
		  removeAbandonedTimeout="60"
		  removeAbandoned="false"
		  logAbandoned="false"
		  minEvictableIdleTimeMillis="30000"
		  jmxEnabled="false"
		  jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState"
		  username="exifweb"
		  password="exifweb"
		  driverClassName="com.mysql.jdbc.Driver"
		  url="jdbc:mysql://localhost:3306/exifweb"/>

Mysql command line

# connecting to exifweb database:
mysql -u exifweb -p
use exifweb
insert into AppConfig values (null, 'false', 'cpu summary: use sum on ps aux command', now());
exit or CTRL+D

# https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysql-utils-intro-connspec-mylogin.cnf.html
# avoid warning for commands like:
mysql -u root -pmysqlpassword -e 'FLUSH QUERY CACHE'

# see /etc/mysql/mysql.conf.d/mysqld.cnf for --socket
# see $HOME/.mylogin.cnf
mysql_config_editor set --login-path=local --host=127.0.0.1 --user=root --socket=/var/run/mysqld/mysqld.sock --password
# or using the --port option:
mysql_config_editor set --login-path=local --host=127.0.0.1 --user=root --port=3306 --password
mysql_config_editor print --login-path=local

# The result of the command below:
mysqlserverinfo --server=local --format=vertical
# is strange: 
# ERROR: Access denied for user 'root'@'127.0.0.1' using password: YES

# the below command works fine so I guess mysqlserverinfo don't work well with mysql_config_editor
mysqlserverinfo --server=root:xxx321@127.0.0.1 --format=vertical

# remove with:
mysql_config_editor remove --login-path=local

# now you have no warnings:
mysql --login-path=local -e 'FLUSH QUERY CACHE'

# other example, e.g. connect to db1 then show tables:
mysql --login-path=local db1
show tables;

Mysql backup

mysqldump --ignore-table=exifweb.Image --databases exifweb --skip-comments -u root --password > 2014-08-28\ exifweb.sql
mysqldump -u root --password exifweb Image > 2014-08-28\ exifweb\ Image\ only.sql
mysqldump -u root --password --databases exifweb > 2014-08-28\ exifweb.sql
mysqldump -u root --password exifweb | gzip > exifweb.sql.gz
mysqldump -u root --password wordpress | gzip > wordpress.sql.gz

sau backup al fisierelor de date:
cp -uiv /ffp/opt/srv/mysql/innodb/* /e-data/3ebe05e9-b9db-8dd1-a9c7-69017484513c/MySqlBackup/

SET unique_checks = 0;
SET foreign_key_checks = 0;
delete from Album;
INSERT INTO `Album` ...
SET foreign_key_checks = 0;
SET unique_checks = 0;

Mysql EXPLAIN select

EXPLAIN EXTENDED select 
    albumcover0_.id as id1_5_,
    albumcover0_.dirty as dirty2_5_,
    albumcover0_.imageHeight as imageHei3_5_,
    albumcover0_.imageWidth as imageWid4_5_,
    albumcover0_.imgName as imgName5_5_,
    albumcover0_.name as name6_5_
from
    v_album_cover albumcover0_
order by albumcover0_.name DESC