================================================================== MySQL Notes ~ ================================================================== References: Good Collection of MySQL Queries: http://www.artfulsoftware.com/infotree/queries.php Reference Links to MySQL Manual ~ Server Options: http://dev.mysql.com/doc/refman/5.5/en/server-options.html Contents ~ MySQL Install Tar on Unix ~ MySQL Install on Oracle Enterprise Linux ~ How to install mysql RPM on Ubuntu ~ Installation Checklist ~ How to reset root password ~ Windows Install ~ InnoDB engine loading in 5.1 ~ How to create new mysql users ? ~ Environment Variables ~ Config Parameters ~ How to use MySQL Profiling ~ Explain basic server options ~ MySQL Release History ~ MySQL Locking ~ What are Temporal Data Types in MySQL? ~ MySQL server sql_mode ~ MySQL Transactions ~ Storage Engines ~ Query Caching ~ Optimization ~ Optimization Tricks ~ Workbench Notes ~ Privileges ~ Index Of Key Terms ~ Manual ~ Important Functions ~ MySQL Tips ~ Todo ~ Additional Links ~ ======================================================================== ================================================================== MySQL Install Tar on Unix ~ ================================================================== To install and use a MySQL tar binary distribution, the basic command sequence looks like this: Ofcourse untar/unzip the file. shell> groupadd mysql shell> useradd -r -g mysql mysql shell> cd /usr/local shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz shell> ln -s full-path-to-mysql-VERSION-OS mysql shell> cd mysql shell> chown -R mysql . shell> chgrp -R mysql . shell> scripts/mysql_install_db --user=mysql Note: For non-root install, do the following: ./scripts/mysql_install_db --no-defaults --datadir=$PWD/data --basedir=$PWD shell> chown -R root . shell> chown -R mysql data # Next command is optional shell> cp support-files/my-medium.cnf /etc/my.cnf shell> bin/mysqld_safe --user=mysql & # this is unix mysql user # Next command is optional shell> cp support-files/mysql.server /etc/init.d/mysql.server For non-root, suggest cp /etc/my.cnf /home/user/.my.cnf and change [mysqld] with basedir= datadir= ================================================================== MySQL Install on Oracle Enterprise Linux ~ ================================================================== First Setup yum repository properly: See http://public-yum.oracle.com/ Oracle Linux 5 # cd /etc/yum.repos.d # wget http://public-yum.oracle.com/public-yum-el5.repo Oracle Linux 6 # cd /etc/yum.repos.d # wget http://public-yum.oracle.com/public-yum-ol6.repo Enable the appropriate repository by editing the yum configuration file Open the yum configuration file in a text editor Locate the section in the file for the repository you plan to update from, e.g. [el4_u6_base] Change enabled=0 to enabled=1 Begin using yum, for example: yum list yum install firefox You may be prompted to confirm the import of the Oracle OSS Group GPG key. To import the mysql build key into your personal public GPG keyring, use gpg --import. gpg --keyserver keys.gnupg.net --recv-keys 5072E1F5 gpg --export -a 5072e1f5 > mysql.asc rpm --import mysql.asc Then follow mysql installation instruction on web: For each package, first remove and then install: rpm -e pkg # erase package rpm -i pkg.rpm # install package =================================================================== How to install mysql RPM on Ubuntu ~ =================================================================== You get debian/ubuntu packages, so you don't have to do the following. But anyway, if you have only RPMs, but you want to install on Ubuntu, then do the following : sudo apt-get install alien sudo alien -i MySQL-server-5.5.14-1.linux2.6.x86_64.rpm --scripts sudo alien -i MySQL-client-5.5.14-1.linux2.6.x86_64.rpm --scripts sudo apt-get install libaio1 libaio-dev sudo /etc/init.d/mysql start ================================================================== Installation Checklist ~ ================================================================== Should I protect mysql root user with password ? ------------------------------------------------------------------ By default, the mysql root user may be unprotected. If you really want to protect do the following: /usr/bin/mysql_secure_installation If you don't know mysql root password, see below later. Have you initialized your data directory ? ------------------------------------------------------------------ Package install by root, does this as part of installation. Otherwise you will have to do: mysql_install_db --no-defaults --datadir=$PWD/data --basedir=$PWD If you are root, then you may want to add --user mysql to initialize files as unix mysql user. =================================================================== How to reset root password ~ ================================================================== /etc/init.d/mysql.server stop /usr/bin/mysqld --user=mysql --skip-grant-tables & Following can be used *only* if you know the current password: mysqladmin -u root password "newpassword" mysqladmin flush-privileges Following method will work even if you have *forgotten* the password: mysql --user=root mysql < for MyISAM sequential scan purpose only. innodb_buffer_pool_size = For innodb. innodb_buffer_pool_size = Default: 128MB; Max cache for all data+indexes. innodb_flush_log_at_trx_commit = 0 => log written/flushed once per sec; 1 => synchronous flush on commit; 2 => log written to fd on commit; but flushed only once per sec; ================================================================== InnoDB engine loading in 5.1 ~ ================================================================== To ignore built-in InnoDB and to load innodb plugin, in 5.1 version, do the following. Please note that, in 5.5 InnoDB is default engine and no need to load. The default plugin dir is MYSQL_HOME/lib/mysql/plugin/ but you can specify explicitly. [mysqld] ignore-builtin-innodb plugin_dir=/usr/local/mysql/lib/mysql/plugin plugin-load=innodb=ha_innodb_plugin.so ## All in single line. ;innodb_trx=ha_innodb_plugin.so ;innodb_locks=ha_innodb_plugin.so ;innodb_lock_waits=ha_innodb_plugin.so ;innodb_cmp=ha_innodb_plugin.so ;innodb_cmp_reset=ha_innodb_plugin.so ;innodb_cmpmem=ha_innodb_plugin.so ;innodb_cmpmem_reset=ha_innodb_plugin.so innodb_file_per_table Note: The loose- prefix may be necessary for following parameters. innodb_data_home_dir = /ibdata innodb_data_file_path = ibdata1:32M;ibdata2:32M:autoextend For complete info about innodb parameters, See http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/ http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ It has too many config parameters. innodb_buffer_pool_size=3000M # Set it to 70-80% of memory. innodb_log_file_size=256M # When log files fillup, checkpoint # is forced. Bigger logfiles, better perf # slower recovery time. # See also innodb_log_files_in_group innodb_log_buffer_size=8M # Used to write this chunk into logfiles. # Even if it is big, log write is forced # on commit. If it is small, logs may be # written out, even before txn commits!!! innodb_flush_log_at_trx_commit=1 # Set it to 2, to flush atmost once/sec. # But what if log lost and datapages # written out ? innodb_thread_concurrency = 8 # Default 8 is pretty good. Innodb_flush_method=O_DIRECT # Avoid double buffering. But you may # like double buffering if there is also # lot of reads. Other option O_DSYNC. # Benchmark and select. innodb_file_per_table # Good option to use if you can afford. # There is no uncontrolled main db growth # that you can't reclaim. innodb_additional_mem_pool_size=2M Tuning your VM to be less eager to swap things by echo 0 > /proc/sys/vm/swappiness is another trick in linux. Note: Implicit temporary tables are created as MyISAM tables! Note: Use mysqltuner script to evaluate your mysql installation! mysqltuner --user=root --pass=password # Just 900 lines perlscript! The loose- prefix makes it possible to specify server unknown variables until the plugin loads and then the plugin uses it. You can also just start server with --ignore-builtin-innodb option; then you can load InnoDB from sql script: mysql> INSTALL PLUGIN InnoDB SONAME 'ha_innodb_plugin.so'; mysql> INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so'; mysql> INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so'; ================================================================== Interesting Various Queries ~ ================================================================== select version(), current_date, now(); DROP TABLE IF EXISTS `time_zone_leap_second`; LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; Default separator is tab and newline. If file is at server, the duplicate entries will result in error; if it is in client, it will skip them. LOAD DATA file syntax allows REPLACE keyword, that will replace records. -- The data file contains tab seperated values. \N is used for null : -- column1 column2 \N column3 -- more complicated example : LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' Consider you want to load data from a file even_more_countries.dat into the Country table. The contents of that file look like this, where whitespace between values represents tab characters: Unusable_data Code Name PopCity PopRural ...... ..... You want to skip the column that contains unusable data, and you want to combine the values of the PopCity and PopRural columns into a single value that should go to the Population column of the table. Also, the first line of the file should be skipped. What's the correct statement to accomplish this? LOAD DATA LOCAL INFILE 'even_more_countries.dat' INTO TABLE Country IGNORE 1 LINES (@unused, Code, Name, @PopCity, @PopRural) SET Population = @PopCity + @PopRural; Note: above stmt skips one line and does something before inserting. To create outfile like this: "70";"23" "90";"42" mysql> SELECT pid, unit -> INTO OUTFILE 'highpers.dat' -> FIELDS TERMINATED BY ';' -> ENCLOSED BY '"' -> LINES TERMINATED BY '\r\n' -> FROM personnel -> ORDER BY grade DESC -> LIMIT 5 IFNULL(@a, 0) <== returns 0 if @a has not been assigned before. ======================================================================== flush logs; closes bin log and starts a new binlog file: hostname-bin.n+1 ------------------------------------------------------------------ BENCHMARK(count,expr) The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how quickly MySQL processes the expression. The result value is always 0. The intended use is from within the mysql client, which reports query execution times: mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye')); +----------------------------------------------+ | BENCHMARK(1000000,ENCODE('hello','goodbye')) | +----------------------------------------------+ | 0 | +----------------------------------------------+ 1 row in set (4.74 sec) The time reported is elapsed time. Only scalar expressions can be used. Although the expression can be a subquery: BENCHMARK(10, (SELECT * FROM t LIMIT 1)) ------------------------------------------------------------------ FOUND_ROWS() For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause ------------------------------------------------------------------ ROW_COUNT() The number of rows updated Row SubQueries ~ SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10); SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10); Nontrivial UPDATE Statement ^ * UPDATE t SET f1 = (SELECT id FROM t2 WHERE t2.j = t.j) <== OK! * UPDATE line_reserve r INNER JOIN product p ON r.id_prod = p.id_prod SET r.unit_value = p.value_prod , r.vat_value = p.value_prod * round(p.vat_prod/100,2) , r.discount = if( r.disc_bool, /* condition * (some-discount-calculation), r.discount ) WHERE r.id_res = var_id_res; ======================================================================== NULLIF(expr1,expr2) (Bit like XOR) Same as: CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. IFNULL(expr1, expr2) It is expr1, in case if expr1 is null, return expr2 ======================================================================== 12.9. Full-Text Search Functions~ MATCH (col1,col2,...) AGAINST (expr [search_modifier]) search_modifier: { IN NATURAL LANGUAGE MODE | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | IN BOOLEAN MODE | WITH QUERY EXPANSION } MySQL has support for full-text indexing and searching: A full-text index in MySQL is an index of type FULLTEXT. Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns. To simply count matches, you could use a query like this: mysql> SELECT COUNT(*) FROM articles -> WHERE MATCH (title,body) -> AGAINST ('database' IN NATURAL LANGUAGE MODE); /* Uses index */ +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) However, you might find it quicker to rewrite the query as follows: mysql> SELECT -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) -> AS count -> FROM articles; /* Does not use index */ +-------+ | count | +-------+ | 2 | +-------+ 1 row in set (0.03 sec) The first query sorts the results by relevance whereas the second does not. However, the second query performs a full table scan and the first does not. The first may be faster if the search matches few rows; otherwise, the second may be faster because it would read many rows anyway. Boolean mode search ~ mysql> SELECT * FROM articles WHERE MATCH (title,body) -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); 12.9.3. Full-Text Searches with Query Expansion Full-text search supports query expansion (and in particular, its variant blind query expansion). This is generally useful when a search phrase is too short, which often means that the user is relying on implied knowledge that the full-text search engine lacks. For example, a user searching for database may really mean that MySQL, Oracle, DB2, and RDBMS all are phrases that should match databases and should be returned, too. This is implied knowledge. mysql> SELECT * FROM articles -> WHERE MATCH (title,body) -> AGAINST ('database' IN NATURAL LANGUAGE MODE); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 1 | MySQL Tutorial | DBMS stands for DataBase ... | +----+-------------------+------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM articles -> WHERE MATCH (title,body) -> AGAINST ('database' WITH QUERY EXPANSION); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 1 | MySQL Tutorial | DBMS stands for DataBase ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 3 | Optimizing MySQL | In this tutorial we will show ... | +----+-------------------+------------------------------------------+ 3 rows in set (0.00 sec) It searches in 2 passes, adding relevant phrases in second pass. Partitioned tables don't support full text. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow. The argument to AGAINST() must be a constant string. Indexed columns must <= 1000 byte encoding. BINARY str is shorthand for CAST(str AS BINARY). CAST(expr AS type) CONVERT(expr,type), CONVERT(expr USING transcoding_name) The type for the result can be one of the following values: BINARY[(N)] CHAR[(N)] DATE DATETIME DECIMAL[(M[,D])] SIGNED [INTEGER] TIME UNSIGNED [INTEGER] This statement converts the string 'abc' in the default character set to the corresponding string in the utf8 character set: SELECT CONVERT('abc' USING utf8); With ROLLUP ~ 12.16.2. GROUP BY Modifiers The GROUP BY clause permits a WITH ROLLUP modifier that causes extra rows to be added to the summary output. These rows represent higher-level (or super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. It can be used, for example, to provide support for OLAP (Online Analytical Processing) operations. Suppose that a table named sales has year, country, product, and profit columns for recording sales profitability: CREATE TABLE sales ( year INT NOT NULL, country VARCHAR(20) NOT NULL, product VARCHAR(32) NOT NULL, profit INT ); The table's contents can be summarized per year with a simple GROUP BY like this: mysql> SELECT year, SUM(profit) FROM sales GROUP BY year; +------+-------------+ | year | SUM(profit) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | +------+-------------+ This output shows the total profit for each year, but if you also want to determine the total profit summed over all years, you must add up the individual values yourself or run an additional query. Or you can use ROLLUP, which provides both levels of analysis with a single query. Adding a WITH ROLLUP modifier to the GROUP BY clause causes the query to produce another row that shows the grand total over all year values: mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP; +------+-------------+ | year | SUM(profit) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+-------------+ Note: ROLLUP affects the first aggregate output only ? If there are more GROUP BY components, that many rows are appended. ======================================================================== 2.16.3. GROUP BY and HAVING with Hidden Columns In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY: SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid; For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause. MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses. A similar MySQL extension applies to the HAVING clause. Standard SQL does not permit the HAVING clause to name any column not found in the GROUP BY clause unless it is enclosed in an aggregate function. MySQL permits the use of such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate. To disable the MySQL GROUP BY extension, enable the ONLY_FULL_GROUP_BY SQL mode. This enables standard SQL behavior: Columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause unless enclosed in an aggregate function. For example, the following query returns name values that occur only once in table orders: SELECT name, COUNT(name) FROM orders GROUP BY name HAVING COUNT(name) = 1; ======================================================================== SQL Statements ~ CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name ; ======================================================================== CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ... index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} Note: MyISAM, InnoDB BTREE only. Memory:Btree/Hash index_option: KEY_BLOCK_SIZE [=] value Hint. size in bytes. better leave 0 default. | index_type | WITH PARSER parser_name /* For fulltext parser plugin */ | COMMENT 'string' The MyISAM, InnoDB, NDB, and ARCHIVE storage engines support spatial columns such as (POINT and GEOMETRY) ======================================================================== 13.1.15. CREATE PROCEDURE and CREATE FUNCTION Syntax CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name any-valid-sql-type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement ======================================================================== To create UDF function: CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} SONAME shared_library_name www.mysqludf.org/lib_mysqludf_ta/index.php ======================================================================== CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test'); The data stored in the table can be used when creating a connection to a FEDERATED table: CREATE TABLE t (s1 INT) ENGINE=FEDERATED CONNECTION='s'; For more information, see Section 14.11, The FEDERATED Storage Engine. ======================================================================== CREATE TABLE ... (See qref*.txt file) For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length. BLOB and TEXT columns also can be indexed, but a prefix length must be given. Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. Only the MyISAM and InnoDB storage engines support indexing on BLOB and TEXT columns. For example: CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). ======================================================================== Partitioning ~ CREATE TABLE t1 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1); /* some hash algo used internally */ CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH ( YEAR(col3) ); CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY [LINEAR] KEY(col3) /* PARTITIONS 4; CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4; CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5; CREATE TABLE t1 ( year_col INT, some_data INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995) DATA DIRECTORY ="C:\\foo\\bar" <=== Note INDEX DIRECTORY ="C:\\foo\\baz", PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2002), PARTITION p4 VALUES LESS THAN (2006), PARTITION p5 VALUES LESS THAN MAXVALUE ); ======================================================================== CREATE TABLE rc ( a INT NOT NULL, b INT NOT NULL ) PARTITION BY RANGE COLUMNS(a,b) ( *multiple columns!* PARTITION p0 VALUES LESS THAN (10,5), PARTITION p1 VALUES LESS THAN (20,10), PARTITION p2 VALUES LESS THAN (MAXVALUE,15), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE) ); ======================================================================== CREATE TABLE client_firms ( id INT, name VARCHAR(35) ) PARTITION BY LIST (id) ( PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) ); ======================================================================== CREATE TABLE lc ( a INT NULL, b INT NULL ) PARTITION BY LIST COLUMNS(a,b) ( PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) ); ======================================================================== CREATE TABLE th (id INT, name VARCHAR(30), adate DATE) PARTITION BY LIST(YEAR(adate)) ( PARTITION p1999 VALUES IN (1995, 1999, 2003) DATA DIRECTORY = '/var/appdata/95/data' INDEX DIRECTORY = '/var/appdata/95/idx', PARTITION p2000 VALUES IN (1996, 2000, 2004) DATA DIRECTORY = '/var/appdata/96/data' INDEX DIRECTORY = '/var/appdata/96/idx', .... ); ======================================================================== 13.1.17.1. CREATE TABLE ... SELECT Syntax You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; You can specify *partial columns* so that remaining will be filled: mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> ENGINE=MyISAM SELECT b,c FROM test2; This creates a MyISAM table with three columns, a, b, and c. CREATE TABLE bar (m INT) SELECT n FROM foo; SELECT * FROM bar; +------+---+ | m | n | +------+---+ | NULL | 1 | +------+---+ If you want to have indexes in the created table, you should specify these before the SELECT statement: mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo; Silent Transformation ^ Suppose that you specify a table using this definition: CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary ); The resulting table has this definition: CREATE TABLE t ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary ); ======================================================================== 13.1.20. CREATE VIEW Syntax CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] A view definition is subject to the following restrictions: * The SELECT statement cannot contain a subquery in the FROM clause. * * The SELECT statement cannot refer to system or user variables. * * Within a stored program, the definition cannot refer to program parameters* * or local variables.* *The SELECT statement cannot refer to prepared statement parameters.* * Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement. * The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view. * Any tables named in the view definition must exist at definition time.* * You cannot associate a trigger with a view.* In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED. mysql> CREATE TABLE t1 (a INT); mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 -> WITH CHECK OPTION; mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 -> WITH LOCAL CHECK OPTION; mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 -> WITH CASCADED CHECK OPTION; Here the v2 and v3 views are defined in terms of another view, v1. v2 has a LOCAL check option, so inserts are tested only against the v2 check. v3 has a CASCADED check option, so inserts are tested not only against its own check, but against those of underlying views. The following statements illustrate these differences: mysql> INSERT INTO v2 VALUES (2); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO v3 VALUES (2); ERROR 1369 (HY000): CHECK OPTION failed 'test.v3' For v1, a must be < 2, but here a=2. ======================================================================== RENAME TABLE tbl_name TO new_tbl_name RENAME TABLE old_table TO backup_table, new_table TO old_table; TRUNCATE [TABLE] tbl_name CALL sp_name([parameter[,...]]) 13.2.2. DELETE Syntax ~ Single-table syntax: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] Multiple-table syntax: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] Or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition] DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; Or: DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; ======================================================================== MERGE Table example ^ create table t1 ....; create table t2 ....; mysql> CREATE TABLE total ( -> a INT NOT NULL AUTO_INCREMENT, -> message CHAR(20), INDEX(a)) -> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; ======================================================================== INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits=hits+15; ======================================================================== 13.2.5.1. INSERT ... SELECT Syntax INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables. For example: INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; ======================================================================== 13.2.5.2. INSERT DELAYED Syntax INSERT DELAYED ... /* Returns Immediately; Inserts later; */ /* Not supported by InnoDB */ The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very 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, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread. ======================================================================== 13.2.5.3. INSERT ... ON DUPLICATE KEY UPDATE Syntax ~ If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect: INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1; MEANS: Either insert Or perform this action on the original matching row. ^ ======================================================================== Two Phase Commit ~ Involves Tx Manager (TM) and Resource Managers(RM). Phase 1: TM tells RM: Prepare to commit Phase 2: If any RM says 'I can't commit', then TM aborts tx; Else TM tells all RM: Now you can really commit. ================================================================== Sysbench ~ ================================================================== $ mkdir sysb $ cd sysb Benchmarking Disk IO --------------------- $ sysbench --test=fileio prepare $ sysbench --test=fileio --file-test-mode=rndrw run Mainly reveals, for example: Total transferred 156.25Mb (3.4871Mb/sec) $ sysbench --test=fileio cleanup ------------------------------------------------------------------ Benchmarking CPU ------------------------------------------------------------------ $ sysbench --test=cpu run Maximum prime number checked in CPU test: 10000 Test execution summary: total time: 35.4654s ... avg: 3.54ms ... So if you want to compare across different servers, compare the average time 3.54ms above. ------------------------------------------------------------------ sysbench and mysql ------------------------------------------------------------------ sysbench [general-options]... --test= [test-options]... command General options: --num-threads=N --max-requests=N --max-time=N (secs) --thread-stack-size=SIZE [32K] --test=STRING test == fileio | cpu | memory | threads | mutex | oltp Commands: prepare run cleanup help version See 'sysbench --test= help' for a list of options for each test. sysbench --test=oltp --num-threads=4 --mysql-user=root --max-time=60 \ --max-requests=0 --oltp-read-only=on prepare # then run sysbench creates a table sbtest in database sbtest : -- -- Table structure for table `sbtest` -- CREATE TABLE IF NOT EXISTS `sbtest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10001 ; INSERT INTO `sbtest` (`id`, `k`, `c`, `pad`) VALUES (1, 0, '', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'), (2, 0, '', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'), (3, 0, '', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'), ... ... Prepared Statements ~ PREPARE s1 FROM 'SELECT 1'; PREPARE s2 FROM 'SELECT 2'; PREPARE s1 FROM 'SELECT Name, Population FROM Country WHERE Continent = ?' prepared statement is executed with EXECUTE and released with DROP/DEALLOCATE PREPARE. PREPARE spop FROM 'SELECT Name, Population FROM Country WHERE Continent = ? AND Population > ?'; SET @c = 'Asia', @p = 100000000; EXECUTE spop USING @c, @p; Note: @c is required -- constant literals are not allowed here. why ??? Not all SQL statements are preparable, but most of them are: SELECT statements INSERT, UPDATE, REPLACE, and DELETE statements SET and DO statements Many SHOW statements CREATE TABLE statements All above can be prepared. ======================================================================== What is DO statement ? ~ DO expr [, expr] ... It is like SELECT, but you don't care about return result: e.g. DO RELEASE_LOCK; Useful for calling functions which have side effects. ======================================================================== Stored Procedures ~ Advantages: Because you can use compound statements and flow-control constructs, stored routines are more flexible regarding SQL syntax than regular SQL statements. Better error handling Stored routines may reduce the complexity of application code. Helps reusability for applications. can you invoke a stored function using CALL ? delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END// CALL simpleproc(@a); SELECT @a ; CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Hello, ',s,'!'); Routine body can be simple one statement or in BEGIN END block. Stored procedure can not return an entire 'set' as return value. Multiple args in stored procedure is OK. From C API (or PHP?), to get the result of stored procedure do: - Set CLIENT_MULTI_RESULTS flag before you call mysql_real_connect(), mysql_query(mysql, "SET @increment = 10"); mysql_query(mysql, "CALL p(@version, @increment)"); mysql_query(mysql, "SELECT @version, @increment"); result = mysql_store_result(mysql); row = mysql_fetch_row(result); mysql_free_result(result); After the preceding code executes, row[0] and row[1] contain the values of @version and @increment, respectively. Rows affected: XX shows that were affected for the last SQL statement. ROW_COUNT() also can be used. CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ( IN | OUT | INOUT param_name type, ...) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name (param_name type, ...) RETURNS any_valid_SQL_type [characteristic ...] routine_body characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } What is DEFINER ? It is the author of the procedure. Root user can create it but can declare the author of this procedure is another user, say, 'mysql'. It is useful, because it is later accessed from SQL SECURITY clause. SQL SECURITY DEFINER => execute this with the author's privilege. SQL SECURITY INVOKER => execute this with the invoker's privilege. Note that there is separate another privilege EXECUTE STORED PROCEDURE (?) which allows any one to invoke any stored procedure on that database. Suppose you write a routine to help with deleting some table data. You don't want anyone to execute and delete the table data unless the user really has that privilege. Tip: It is based on SQL:2003 std, similar to DB2 implementation. CREATE PROCEDURE world.world_record_count => creates sp in world db. SQL SECURITY INVOKER ==> Makes the SP to execute as caller. During SP call: The routine's environment is set so that the database that it belongs to becomes its default database for the duration of its execution. 2. The sql_mode system variable value in effect when the routine executes is the value that was current when it was defined. my_block: BEGIN (compound statements) END my_block Note: MySQL *cursors are for read only* !!! not for writing. CONTINUE handler and its statement as empty block effectively ignores (error) condition. Supported: LOOP, REPEAT, and WHILE Not supported: FOR SHOW CREATE PROCEDURE world.world_record_count\G ALTER procedure|function canbe used only for changing comments/SQL SECURITY. What is wrong below ? mysql> delimiter // mysql> CREATE PROCEDURE incrementor (OUT i INT) -> BEGIN -> REPEAT -> SET i = i + 1; -> UNTIL i > 9 -> END REPEAT; -> END; -> // Note: i is accessed before getting written. i becomes NULL on enter, the loop goes to infinite loop. CREATE PROCEDURE sp2 (x VARCHAR(5)) BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; DECLARE done TINYINT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT xname, id FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; read_loop: LOOP FETCH FROM cur1 INTO newname, xid; IF done THEN LEAVE read_loop; END IF; SELECT newname; END LOOP; CLOSE cur1; END; The CREATE ROUTINE privilege is needed to create stored routines. The EXECUTE privilege is required to execute stored routines. Statements supported in mysql stored procedures: REPEAT ... UNTIL expr END REPEAT WHILE ...DO ... END WHILE LOOP ... END LOOP GOTO CASE variable1 WHEN 0 THEN ... ; ELSE ... END CASE IF .. THEN ...; ELSE ... ; END IF label1: ... label2: WHILE .... LEAVE label2; ... END WHILE [label2]; /*End label optional*/ label3: REPEAT .... LEAVE label3 ... UNTIL expr END REPEAT label3 LEAVE => Break the compound loop; Valid only if the label starts a LOOP. ITERATE label1 ; ==> like continue in C. label1 must start a loop. BEGIN DECLARE EXIT HANDLER FOR 1216 INSERT INTO error_log VALUES(...); ... END Above means, on error, it will insert row, and then exit! CONTINUE HANDLER means, it will continue from that point after insert. You can give an alias for SQL error code by using condition: DECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000'; DECLARE EXIT HANDLER FOR `Constraint Violation` ROLLBACK; Server Side Cursor Used With Stored Procedures ^ Note: Cursor is a generic term used in many different contexts. Here we refer to Cursor in SP only. Summary of things that we can do with cursors: DECLARE cursor-name CURSOR FOR SELECT ...; OPEN cursor-name; FETCH cursor-name INTO variable [, variable]; CLOSE cursor-name; MySQL Cursor Characteristics Summary: READ ONLY NOT SCROLLABLE ASENSITIVE cursors are READ ONLY. You cannot say: FETCH variable1 INTO cursor1; UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1; Cursors are also NOT SCROLLABLE. That is, you can only fetch the next row, you cannot move back and forth in the result set. You cannot say: FETCH PRIOR cursor1 INTO variable1; FETCH ABSOLUTE 55 cursor1 INTO variable1; And you should avoid doing updates on a table while you have a cursor open on the same table, because cursors are ASENSITIVE. That is, if you don't avoid doing updates, we don't guarantee what the results will be. Security Privileges CREATE ROUTINE EXECUTE INVOKERS AND DEFINERS CREATE FUNCTION CREATE FUNCTION factorial (n DECIMAL(3,0)) RETURNS DECIMAL(20,0) DETERMINISTIC BEGIN DECLARE factorial DECIMAL(20,0) DEFAULT 1; DECLARE counter DECIMAL(3,0); SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END // INSERT INTO t1 VALUES (factorial(5)); *you cannot access tables from within a function.* These are the only things allowed in functions: 'BEGIN END' DECLARE IF ITERATE LOOP REPEAT RETURN 'SET declared variable' WHILE SHOW PROCEDURE STATUS ======================================================================== Oracle Comparison Summary: - Oracle allows DECLARE after OPEN MySQL requires DECLARE be at the start Oracle allows CURSOR cursorname IS MySQL requires DECLARE cursorname CURSOR Oracle doesn't require '()' MySQL requires '()' Oracle allows accessing tables in functions MySQL does not allow accessing tables in functions Oracle supports packages. MySQL does not support packages. ======================================================================== Tips for migration ... Change assignment statements like a:=b to SET a=b. Change RETURN statements inside procedures with LEAVE label_at_start label_at_start is the first token in the stored procedure. For example: [Oracle procedure] CREATE PROCEDURE ... RETURN; ... [MySQL procedure] CREATE PROCEDURE () label_at_start: BEGIN ... LEAVE label_at_start; END This is only necessary with procedures, MySQL supports RETURN in a function. ======================================================================== Oracle MySQL CREATE PROCEDURE CREATE PROCEDURE sp_name sp_name AS variable1 INTEGER BEGIN variable1 := 55 DECLARE variable1 INTEGER; SET variable1 = 55; END END Note: session variables are available within stored routine. Use of SIGNAL ~ CREATE PROCEDURE p (pval INT) BEGIN DECLARE specialty CONDITION FOR SQLSTATE '45000'; IF pval = 0 THEN SIGNAL SQLSTATE '01000'; /* Like throw in C++ */ ELSEIF pval = 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred'; ELSEIF pval = 2 THEN SIGNAL specialty /* alias for SQLSTATE '45000' ... END How to implement foreign key logic in MyISAM? Create new procedure mytable_fk_insert() and check the reference logic there. Instead of insert(), people should call this.!!! Trigger is more appropriate for this !!! ======================================================================== Triggers ~ CREATE TRIGGER [DEFINER = { user | CURRENT_USER }] { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON FOR EACH ROW Examples: delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; /* NEW = New Row */ DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | In Triggers, the control blocks are allowed like in functions: Compound statements (BEGIN / END) are legal. Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are legal. Variable declaration (DECLARE) and assignment (SET) are legal. Condition declarations are legal. Handler declarations are legal. Referring to other tables are not allowed (except NEW and OLD rows) Can't have 2 triggers for same event on same table. like BEFORE INSERT on t1 there can be atmost 1 trigger. With out 'FOR EACH ROW', the trigger will happen once per statement. With INSERT, only NEW is legal. With DELETE, only OLD is legal. With UPDATE, both NEW and OLD are legal. Here is an example of an UPDATE trigger with both: CREATE TRIGGER t21_au BEFORE UPDATE ON t22 FOR EACH ROW BEGIN SET @old = OLD . s1; SET @new = NEW.s1; END;/ Trigger is DB object but attached to a table.; Note: To reject an update/delete you just access an unknown column from unknown table or something like that. An SQL error in the trigger will reject that update. Since SIGNAL statement is now available, that is more appropriate for this. ======================================================================== What is Cursor ? ~ ====================================================================== JDBC ==== From JDBC/Driver point of view, there is client-side cursor and server-side cursor depending on where you are buffering. Client Side Caching: It is the default. Server side Caching: ==================== To get "server-side cursors" working with MySQL-5.0 and Connector/J: * Add "useCursorFetch=true" to your JDBC URL * call stmt.setFetchSize(> 0) OR use defaultFetchSize Driver property * Then results are not returned whole but page by page. Note: Oracle behaviour is also same. It uses defaultRowPrefetch=NNN C Program ========= The default is server side caching: MYSQL_STMT *stmt; MYSQL_BIND bind[4]; MYSQL_RES *prepare_meta_result; /* Prepare a SELECT query to fetch data from test_table */ stmt = mysql_stmt_init(mysql); /* mysql is the connection var */ mysql_stmt_prepare(stmt, "SELECT ....", strlen(query_string)); /* Get the parameter count from the statement */ param_count= mysql_stmt_param_count(stmt); /* Fetch result set meta information */ prepare_meta_result = mysql_stmt_result_metadata(stmt); /* Get total columns in the query */ column_count= mysql_num_fields(prepare_meta_result); /* Execute the SELECT query */ mysql_stmt_execute(stmt); /* By default result is buffered at server*/ /* Bind the result buffers for all 4 columns before fetching them */ memset(bind, 0, sizeof(bind)); .... /* Bind the result buffers */ mysql_stmt_bind_result(stmt, bind); /* Now buffer all results to client (optional step) */ mysql_stmt_store_result(stmt); /* Fetch all rows */ row_count= 0; mysql_stmt_fetch(stmt); /* Values will be available in bind[n].var */ ... In general how to retrieve the result sets and use that to get the OUT parameters of procedure call also: mysql_query(mysql, "SET @increment = 10"); mysql_query(mysql, "CALL p(@version, @increment)"); mysql_query(mysql, "SELECT @version, @increment"); result = mysql_store_result(mysql); row = mysql_fetch_row(result); /* row[0] contains @version, etc. */ mysql_free_result(result); To get the OUT parameters of procedure call, the better API (from MySQL 5.5) would be: mysql_stmt_next_result() ======================================================================== PHP has similar interface. ======================================================================== UDF Vs Stored Routines ~ CREATE [AGGREGATE] FUNCTION MyTest RETURNS [INTEGER|STRING|REAL|DECIMAL] SONAME the_libraries_exact_name ======================================================================== Method Speed Language Development ======================================================================== Stored Procedures slow SQL ~minutes (for small functions) UDF fast C ~hour Native Function fast C major pain Writing UDF : See http://www.codeguru.com/cpp/data/mfc_database/misc/article.php/c12615/MySQL-UDFs.htm Can be aggregate or non-aggregate function. INFORMATION_SCHEMA ~ use information_schema; mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA <== Contains info about databases | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES <== Contains info about tables | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS <== Contains info about triggers, etc. | USER_PRIVILEGES | | VIEWS | +---------------------------------------+ Example Queries: Query to see all databases starting with w* : SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'w%'; All tables in world database: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world'; Tip: MySQL error levels are: ERROR, WARNING, NOTE Tip: The generation of notes can be suppressed by issuing this SQL statement: SET sql_notes = 0; ======================================================================== Basic Optimizations ================================================================== MySQL Programs ~ ================================================================== mysqladmin extended -i100 -r is very nice way to look how MySQL performance counters increment *mysqlcheck* - mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user - mysqld must be running. - It auto locks the tables for READ - most operations supported on both myisam and innodb. - most operations not supported by memory engine. - innodb supports check table but not repair. - default behaviour will be repair/optimize/analyze if you do: ln -s mysqlcheck mysqlrepair|mysqlrepair|mysqloptimize - - Compare with myisamchk which is offline tool. mysqlcheck [options] db_name [tables] mysqlcheck [options] --all-databases *mysqldump* - mysqldump [options] db_name [tables] mysqldump [options] --all-databases e.g. mysqldump --xml -u root world City; Dumps City table to stdout. Usually dumpoutput has all insert statements. very inefficient. --tab=path, -T path *note * Produce tab-separated data files. For each dumped table, mysqldump creates a tbl_name.sql file that contains the CREATE TABLE statement that creates the table, and a tbl_name.txt file that contains its data. The option provides output directory. The output created for use with LOAD DATA writes NULL as sequence of \xx values, not as "NULL". *mysqlimport* - mysqlimport --local dbname mytab.txt Loads the data in mytab.txt in client mc into test.mytab table. The table should pre-exist. Filename used to locate table. By default, the file should exist in *server* file system! *mysqlshow* - command line interface to show commands: e.g. mysqlshow [options] [db_name [tbl_name [col_name]]] mysqlshow test 'my%' => list all tables of form my* *mysqlslap* - Load Emulation Client. mysqlslap [options] mysqlslap -a -i 100 ; -- autogenerate sql; 100 iterations Handy load generation tool! *Examples:* mysqlslap --delimiter=";" \ --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \ --query="SELECT * FROM a" --concurrency=50 --iterations=200 mysqlslap --concurrency=10 --iterations=20 \ --number-int-cols=2 --number-char-cols=3 \ --auto-generate-sql mysqlslap --concurrency=5 \ --iterations=5 --query=query.sql --create=create.sql \ --delimiter=";" Note: There is no option to force leaving it's autogenerated SQL statements. Note: It takes around 20ms to run a query. MySQL administrative and utility programs ~ 4.6.2. *myisam_ftdump* - Display Full-Text Index information myisam_ftdump [options] tbl_name index_num [ from dbdir ] myisam_ftdump /path/to/dbdir/tablename index_num CREATE TABLE mytexttable ( id INT NOT NULL, txt TEXT NOT NULL, PRIMARY KEY (id), FULLTEXT (txt) ); Example: myisam_ftdump mytexttable 1 4.6.3. *myisamchk* - MyISAM Table-Maintenance Utility myisamchk [options] tbl_name ... Example: myisamchk *.MYI ; -- can be used offline. myisamchk -dvv mytable.MYI -- verbose output Note: tablename.MYI stores table info and index. Exists even for no-index tables tablename.MYD stores data. 4.6.4. *myisamlog* - Display MyISAM Log File Contents myisamlog [options] [log_file [tbl_name] ...] This logfile is used only for MyISAM debugging. It is not a binary log. The file path given as --log-isam=file-name server command line option. 4.6.5. *myisampack* - Generate Compressed, Read-Only MyISAM Tables myisampack [options] file_name ... ; -- file_name can be *.MYI or tablename -- use this command offline. -- achieves 40 to 70% compression -- table becomes read-only -- Execute myisamchk -rq to rebuild the indexes after this. -- To unpack, use --unpack option. 4.6.6. *mysqlaccess* - Client for Checking Access Privileges mysqlaccess [host_name [user_name [db_name]]] [options] 4.6.7. *mysqlbinlog* - Utility for Processing Binary Log Files mysqlbinlog [options] log_file ... -- It generates sql statements [ bin log contains only update/change queries ] (mysqldumpslow describes the slow query log. contains read query also) 4.6.8. *mysqlhotcopy* - A Database Backup Program. (physical online backup) mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. 4.6.9. *mysql_convert_table_format* - Convert Tables to another Storage Engine mysql_convert_table_format [options]db_name mysql_convert_table_format --type=enginename db_name 4.6.10. *mysql_find_rows* - Extract SQL Statements from Files 4.6.11. *mysql_fix_extensions* - Normalize Table Filename Extensions 4.6.12. *mysql_setpermission* - Interactively Set Permissions in Grant Tables 4.6.13. *mysql_waitpid* - Kill Process and Wait for Its Termination 4.6.14. *mysql_zap* - Kill Processes That Match a Pattern Note: Perl DBI, DBD modules can be downloaded from http://search.cpan.org 4.7. MySQL Program Development Utilities ~ 4.7.1. innochecksum [options] filename Offline InnoDB File Checksum Utility innochecksum prints checksum for InnoDB files, reports mismatches 4.7.2. *mysql_config* - Get Compile Options for Compiling Clients Example: $ mysql_config --include -I/home/thava/mysql/dev/latest/install/include/mysql 4.7.3. *my_print_defaults* - Display Options from Option Files Example: my_print_defaults --defaults-file=example.cnf client mysql 4.7.4. *resolve_stack_dump* - Resolve Numeric Stack Trace Dump to Symbols resolve_stack_dump [options] symbols_file [numeric_dump_file] 4.8. Miscellaneous Programs ~ 4.8.1. *perror* - Explain Error Codes Example: /usr/bin/perror 13 64 OS error code 13: Permission denied OS error code 64: Machine is not on the network Note: To obtain the error message for a MySQL Cluster error code, invoke perror with the --ndb option: Example: perror --ndb errorcode 4.8.2. *replace* - A String-Replacement Utility In place replacement in file or from stdinput. No glob expressions. Example: replace SomeString AnotherString -- myfile1 replace SomeString AnotherString < myfile1 replace fromStr1 toStr2 OtherStr1 OtherStr2 < inputfile 4.8.3. *resolveip* - Resolve Hostname to IP Address or Vice Versa Example: resolveip buvana # Tells you the IP addr resolveip 127.0.0.1 # Tells you that it is localhost ======================================================================== 5. MySQL Server Administration ~ 23. Extending MySQL 23.3 Adding New Functions to MSQL UDF User Defined Functions ~ 3 ways to add functions: - UDF (create .so file shared lib and load dynamically) - Change source code - Stored functions. (offers high level SQL syntax) Functions can return - string, integer, or real values - can accept arguments of same above types. You can define simple functions that operate on: - a single row at a time, or - aggregate functions on groups of rows. Note: Information is provided to functions for checking: - number/type/names of the arguments passed to them. * You can tell MySQL to coerce arguments before passing them to func. * You can indicate that a function returns NULL or error occurred. * Func must be written in C/C++ * See sql/udf_example.c for 5 new functions examples. 23.4 Adding New procedures to MySQL ~ SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000); It does some analysis for either each row or group. suggests optimal data types for each column to reduce table size. You can write your own procedure similar to analyse. See sql/sql_analyse.c for example. Server Command Options ~ --basedir=path, -b path Char/Collation data all resolved relative to this dir. --bind-address=addr Parameter Meaning 0.0.0.0 All IPV4; Default :: All IPV4 and IPV6 ::ffff:127.0.0.1 IPV4 Mapped Address. Both 127.0.0.1 or ::1 are OK ::ffff:xx.yy.zz.tt IPV4 Mapped Address. Both xx.yy.zz.tt or IPV6 OK. 127.0.0.1 Only given IPV4 (localhost or any IP) ::1 Only given IPV6 (localhost or any IP) --binlog-format={ROW|STATEMENT|MIXED} Default STATEMENT --bootstrap Used by mysql_install_db to create MySQL privilege tables. --character-sets-dir=path --character-set-server=charset_name --collation-server=collation_name --console Only on windows. Useful while diagnosing problems. --datadir=path --default-time-zone=timezone (Only for display. Internal is UTC only) --enable-named-pipe (windows only) --external-locking If multiple mysqld share same dbdir for myisam tables, use this option to lock files at file system level. --general-log[={0|1}] By default off. Logs all queries!!! hostname.log is the default query log file. --general_log_file=file_name Query log file name. (--log deprecated) --log-err=filename By default hostname.err --init-file=file_name Read SQL stmts from this file during startup. --innodb-* --log-isam[=file_name] for debug of myisam --log-output=TABLE|FILE|NONE (Applies to general query log and slow log) --log-slow-queries[=file_name] --long_query_time in seconds what is slow ? See also: --log-long-format to log entire query. --low-priority-updates Applicable for SE's MYISAM, MEMORY which use table level locking. By default updates have high priority. Other alternative: {INSERT | REPLACE | DELETE | UPDATE} LOW_PRIORITY ... stmt. --memlock Lock mysqld process in memory. --open-files-limit=count --partition=ON|OFF By default ON --plugin-load=plugin_list mysqld --plugin-load=myplug1=myplug1.so;myplug2=myplug2.so --port=port_num, -P port_num --secure-auth disable old style auth - pre 4.1 --socket=path --sql-mode=value[,value[,value...]] --tmpdir=path Several dirs can be separated by : or ; Binlog format ~ mysql> SET SESSION|GLOBAL binlog_format = 'STATEMENT'; mysql> SET SESSION|GLOBAL binlog_format = 'ROW'; mysql> SET SESSION|GLOBAL binlog_format = 'MIXED'; Running multiple mysqld ~ On Windows as multiple different services: A) Create my.cnf : options for mysqld1 service [mysqld1] basedir = C:/mysql-5.1.55 port = 3307 enable-named-pipe socket = mypipe1 # options for mysqld2 service [mysqld2] basedir = C:/mysql-5.5.25 port = 3308 enable-named-pipe socket = mypipe2 C:\> C:\mysql-5.1.55\bin\mysqld --install mysqld1 C:\> C:\mysql-5.5.25\bin\mysqld --install mysqld2 C:\> NET START mysqld1 C:\> NET START mysqld2 C:\> NET STOP mysqld1 C:\> NET STOP mysqld2 Approach B) C:\mysql-5.1.55\bin\mysqld --install mysqld1 --defaults-file=C:\my-opts1.cnf C:\mysql-5.1.55\bin\mysqld --install mysqld2 --defaults-file=C:\my-opts2.cnf (service is installed to invoke mysqld with different option files) mysqld --remove mysqld1 # remove service ======================================================================== MySQL Privileges ~ ======================================================================== * Administrative privileges enable users to manage operation of the MySQL server. These are global. * Database privileges apply to a database and to all objects within it. Possible to assign to all dbs as well. * Privileges for db objects: tables, indexes, views, and stored routines, etc. Possible to assign to all-dbs:tables or all-dbs:indexes etc. * Privileges is stored in following tables in mysql db: user: Contains user accounts, global privs, and other non-priv columns. host: obsolete db, tables_priv, columns_priv, procs_priv : Object specific privs. * Access-control decisions are based on in-memory copies of grant tables. * Usually: user table is populated with privileges. host, tables_priv, columns_priv, procs_priv are usually empty. Privilege checking logic: - Check User > Db > Host Tables in that order. - If user table gives permission to DELETE, you can delete any entries in any database! So it is better to set privs as N here (except for root) - The db and host tables grant database-specific privileges: db table has priv entries connecting (Host, Db, User). host table has priv entries connecting (Host, Db). A blank User value in the db table matches the anonymous user. A nonblank value matches literally; No wildcards for usernames. - Wildcards OK for Db or Host columns of either tables db and host. - Blank hostname(' ') in Db table means "Consult Host Table" - The Host table enables restricting access on host by host basis. Privileges Calculation Summary: global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges OR routine privileges SHOW CREATE TABLE USER : mysql> show create table user \G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`Host`,`User`) ) Server Admin SQL privileges: CREATE TABLESPACE; CREATE USER ; PROCESS ; PROXY ; RELOAD REPLICATION CLIENT ; REPLICATION SLAVE SHOW DATABASES ; SHUTDOWN ; SUPER ; ALL ; USAGE 6.3.6 Pluggable Authentication ~ From 5.5.7, pluggable auth is enabled. The entry in mysql.user table specifies which auth mechanism to be used? CREATE USER 'david' IDENTIFIED WITH auth_plugin [AS 'auth_string'] External authentication: methods such as PAM, Windows login IDs, LDAP, or Kerberos. Proxy users: If a user is permitted to connect, an authentication plugin can return to the server a user name different from the name of the connecting user, to indicate that the connecting user is a proxy for another user. While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of a different user. A proxy user account must have the PROXY privilege for the proxied account. Use the GRANT statement for this. Consider the following definitions: CREATE USER 'empl_external'@'localhost' IDENTIFIED WITH auth_plugin AS 'auth_string'; CREATE USER 'employee'@'localhost' IDENTIFIED BY 'employee_pass'; GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost'; When a client connects as *empl_external* from the local host, MySQL uses auth_plugin to perform authentication. If auth_plugin returns the employee user name to the server (based on the content of 'auth_string' and perhaps by consulting some external authentication system), that serves as a request to the server to treat this client, for purposes of privilege checking, as the *employee* local user. In this case, empl_external is the proxy user and employee is the proxied user. mysql> SELECT USER(), CURRENT_USER(); +-------------------------+--------------------+ | USER() | CURRENT_USER() | +-------------------------+--------------------+ | empl_external@localhost | employee@localhost | +-------------------------+--------------------+ GRANT PROXY ON 'proxied_user' TO 'proxy_user'; ======================================================================== SSL Protocol SSL incorporates identity verification using the X509 standard. In basic terms, there should be some company called a Certificate Authority (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can show the certificate to another party as proof of identity. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate. mysqld --ssl --help mysqld --ssl-ca=ca-cert.pem \ # only one CA ??? --ssl-cert=server-cert.pem \ --ssl-key=server-key.pem Account can be created with REQUIRE SSL keyword, which means that the client can use any SSL certificate and server is not going to check the client. This is used to force SSL encryption over the channel to avoid breaches like intercepting: mysql --ssl-ca=ca-cert.pem Account created with REQUIRE X509 option must connect by submitting it's public certificate to server as part of the connection process: mysql --ssl-ca=ca-cert.pem \ --ssl-cert=client-cert.pem \ --ssl-key=client-key.pem mysql> status ... SSL: Cipher in use is DHE-RSA-AES256-SHA ... Option Comments --ssl For server, it means, SSL connection allowed.(not forced) --ssl=0 means SSL should not be used. --ssl-verify-server-cert Forces client to verify server --ssl-cipher=cipher_list A list of permissible ciphers to use for SSL encryption. For greatest portability, cipher_list should be a list of one or more cipher names, separated by colons. Examples: --ssl-cipher=AES128-SHA --ssl-cipher=DHE-RSA-AES256-SHA:AES128-SHA Query Optimization ~ Keep table statistics up to date Adjust the size and properties of the memory areas that MySQL uses for caching. With efficient use of the InnoDB buffer pool, MyISAM key cache, and the MySQL query cache Following queries are very fast: SELECT COUNT(*) FROM tbl_name; /* Only for MyISAM is fast. not for innodb */ SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; SELECT MAX(key_part2) FROM tbl_name WHERE key_part1=constant; SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10; MySQL resolves the following queries using only the entries from a secondary index, if the indexed columns are numeric: SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2; SELECT key_part2 FROM tbl_name GROUP BY key_part1; The following queries use the index data to retrieve the rows in sorted order without a separate sorting pass: SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... ; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... ; Syntax ~ Literal values: 'string1' 'string2' == 'string1string2' (auto-concatenation) DATE '2012-04-04', TIME '200:10:10' (Not TIME of day, it is TIME diff from -838 to 838) TIME '10 11:12:0' (Means 10 Days (Max 34), 11 Hrs 12 Mins 0 Secs.) TIMESTAMP '2012-01-01 01:01:01' ==> all valid. { d 'str' } { t 'str' } { ts 'str' } ==> OK (odbc syntax) '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent. DATETIME/TIMESTAMP: 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' (14 or 12 chars. 12 chars must imply YY format only) 2-Digit Year values in the range 70-99 are converted to 1970-1999. 2-Digit Year values in the range 00-69 are converted to 2000-2069. mysql> SELECT X'4D7953514C'; <=== Hexadecimal support. 'MySQL' Note: A hexadecimal number is promoted to string! SELECT 0x41, CAST(0x41 AS UNSIGNED); -> 'A', 65 mysql> SELECT HEX('cat'); -> '636174' mysql> SELECT 0x636174; -> 'cat' mysql> SELECT TRUE, true, FALSE, false; -> 1, 1, 0, 0 CREATE TABLE t (b BIT(8)); INSERT INTO t SET b = b'11111111'; <= b not for binary or byte. It is BIT!!! INSERT INTO t SET b = b'1010'; INSERT INTO t SET b = b'0101'; mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t; +------+----------+----------+----------+ | b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) | +------+----------+----------+----------+ | 255 | 11111111 | 377 | FF | | 10 | 1010 | 12 | A | | 5 | 101 | 5 | 5 | +------+----------+----------+----------+ Note: The functions BIN(), HEX() etc transform INT to STRING. Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to a user variable, use CAST() or +0: mysql> SET @v1 = 0b1000001; mysql> SET @v2 = CAST(0b1000001 AS UNSIGNED), @v3 = 0b1000001+0; mysql> SELECT @v1, @v2, @v3; \N => NULL value in import files, etc. Max identifier length is 64 (mostly) To use the name as a function call in an expression, there must be no whitespace between the name and the following ( parenthesis character. Conversely, to use the function name as an identifier, it must not be followed immediately by a parenthesis. With IGNORE_SPACE enabled, the parser loosens the requirement that there be no whitespace between the function name and the following parenthesis. (IGNORE_SPACE means, ignore space-requirement rule as per ANSI) and accept the function even if there is a space: f (arg) Expression Syntax: expr XOR expr boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN} boolean_primary IS [NOT] NULL boolean_primary comparison_operator {ALL | ANY} (subquery) bit_expr [NOT] BETWEEN bit_expr AND predicate bit_expr % bit_expr simple_expr COLLATE collation_name BINARY simple_expr ROW (expr, expr [, expr] ...) # Comment /* comment */ -- comment <== All are OK. Special Comment Syntax ~ MySQL server parses the special comment below, but DB2, etc would ignore: SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ... CREATE /*!32302 TEMPORARY */ TABLE t (a INT); # Only MySQL ver > 3.23.02 SET lc_messages = 'en_US'; Data Types ~ SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. ======================================================================== DataType TypeSize Default Comments Bytes ======================================================================== BIT(M) (M+1)/8 1 M:0-64 TINYINT(M) 1 0 [UNSIGNED] [ZEROFILL] OK. M:DisplayWidth BOOL,BOOLEAN 1 0 Alias for TINY(1). Takes 1 byte, not bit. SMALLINT 2 0 64K Range: -32K to +32K MEDIUMINT 3 0 16M Range: -8M to +8M INT, INTEGER 4 0 4G Range: -2G to +2G (10 digits as decimals) BIGINT 8 0 4G*4G Range; (20 digits as decimals) DECIMAL(M,D) 24? 0 M:0-65; Default M=10; Can represent 65 digits! FIXED(M,D) M - Total Precision ; D - Total decimal points M:1 to 65 All calculations done using 65 digits precision. FLOAT(M,D) 4 0 Accurate to only 7 Decimal places. UNSIGNED OK DOUBLE(M,D) 8 0 Accurate to only 15 Decimal places.UNSIGNED OK REAL Synonym for FLOAT if REAL_AS_FLOAT sqlmode; else it is DOUBLE. 27.3. MySQL PHP API ~ 27.3.2. Enabling Both mysql and mysqli in PHP ~ *mysql* Available for PHP versions 4 and 5, this extension is intended for use with MySQL versions prior to MySQL 4.1 *mysqli* Stands for *MySQL-Improved* the extn is available only in PHP 5. It is intended for use with MySQL 4.1.1 and later. This extension fully supports the authentication protocol used in MySQL 5.0, as well as the Prepared Statements and Multiple Statements APIs. Backup and Recovery ~ Logical Backup~ Backup database or table. Portable. Fine granularity. No backup of configfiles. done online. Logical backup tools include the 1) *mysqldump* program and the 2) SELECT ... INTO OUTFILE statement. These work for any storage engine, even MEMORY. Example: select * from yourtable into outfile 'yourtable.txt' ; For restore, SQL-format dump files can be processed using the mysql client. To load delimited-text files, use the 1) LOAD DATA INFILE statement or 2) mysqlimport client. To load two columns of the table, use this statement: LOAD DATA LOCAL INFILE 'more_countries.dat' INTO TABLE Country (Code, Name); Other columns will assume default values. Physical Backup ~ Usually shutdown and copy the files for Offline backup. For memory storage engine, you will have to use logical backup only. Online backup options: - For *MyISAM* And *Archive* storage engines, you can use: *mysqlhotcopy* Perlscript to backup online after issuing statements like LOCK TABLES, FLUSH TABLES, etc. For restore, copy files back to data directory. - For *InnoDB* use *mysqlbackup* tool for backup and restore. (not bundled by default); - For *NDB* there is *ndb_mgm* shell which supports START BACKUP command ; ndb_restore for restore. * Local versus remote backups * *Snapshot* backups Supported by file system such as zfs, veritas, etc. * Full versus incremental backups Server's *binary-log* can be used for incremental backups. Even if MyISAM does not support ACID transactions, the logs can still be used for incremental backup and recovery. * Point-in-time recovery * Backup scheduling, compression, and encryption * Table maintenance ======================================================================== How to do incremental backup ? ======================================================================== - Enable binary logging by starting server with --log-bin option. What are the different types of logs in MySQL ? -------------------------------------------------------------------------------- Log Type Information Written to Log ---------------------------------------------------------------------------- The error log Problems encountered starting, running, or stopping mysqld The general query log Established client connections and statements received from clients The slow query log All queries that took more than long_query_time seconds or didn't use indexes The binary log All statements that change data (also used for repln.) ----------------------------------------------------------------------------- - The logs use CSV engine by default. You can also choose to use MyISAM. But you can't use other engines. Logging related options ~ --log-output=FILE,TABLE,NONE => one or more. TABLE may be *perf killer* !! specify only for general/slow query log (system var: log_output) (Default=FILE) (From 5.1.29 onwards. Before that the options were bit weird) (--log-slow-queries=file is no longer supported) --log-error=/var/log/mysql/error.log (Default: $DATADIR/hostname.err) --general-log = 1 | 0 => *Perf killer* Enables/disables general logging. --general-log-file[=file_name] => General query log file pathname. (sytem var: general_log_file) Default: $DATADIR/hostname.log --slow-query-log = 1 | 0 => Enables/disables slow query logging. --slow-query-log-file[=file_name] => slow query log file pathname. (system var: slow_query_log_file) Default: $DATADIR/hostname-slow.log Slow as determined by long_query_time= option. --log-bin[=base_name] => Binary log pathname Note: To display binlog use mysqlbinlog Use mysql.cnf file rather than above options: Example: logbin = ON/OFF [mysqld] ......... general-log-file=DATADIR/hostname.log ; General query log file path. log-bin = DATADIR/binlog.log ; Enables binary log. To be discussed later Following system variables associated with general and slow logs only: show variables like 'general_log' ; ON or OFF show variables like 'slow_query_log' ; ON or OFF show variables like 'general_log_file' ; name of general log file show variables like 'slow_query_log_file' ; name of query log file show variables like 'sql_log_off' ; ON or OFF - session general query log show variables like 'sql_log_bin' ; 0 or 1 -- session enable binary logging show binlog events; --- discuss this later... In database data dir mysql: general_log.CSM general_log.frm general_log.CSV ndb_binlog_index.frm SHOW CREATE TABLE mysql.general_log; SHOW CREATE TABLE mysql.slow_log; ndb_binlog_index.MYI ndb_binlog_index.MYD slow_log.CSV slow_log.CSM slow_log.frm ================================================================== How to set/get variables ? ~ ================================================================== SET variable_assignment [, variable_assignment] ... variable_assignment: user_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@@global. | @@session. | @@]system_var_name SET @a := 3 ; <=== is also valid !!! Note: variable names are case insensitive. LOCAL == SESSION @@LOCAL == @@SESSION Default == Session Variable. Example: SET SESSION query_cache_type = OFF; SET GLOBAL storage_engine = MyISAM; See Also: show [session | global] variables like ...; var = Refers to session variable. @var = Refers to user variable. @@var = Now refers to session variable. SET SESSION var is preferred. @@global.var = Refers to global server variable. @@session.var = Refers to session variable. SET @old_log_state = @@global.general_log; This is same as: SET @old_log_state = @@general_log; // global. prefix preferred. SET GLOBAL general_log = 'OFF'; is same as: SET @@global.general_log = 'OFF'; ... SET GLOBAL general_log = @old_log_state; Using @var, @@var and SELECT into local variable: SELECT @min_cid:=min(c_id), @max_cid:=max(c_id) from t1; Note: SHOW GLOBAL VARIABLES; ==> 271 rows. SHOW SESSION VARIABLES; ==> 271 rows. How ? Because, global view includes current session view. session view includes global view. i.e. global vars display includes session (only) vars as well. Then what is the difference in output ? SHOW GLOBAL VARIABLES display *global* value for which both exist. displays *session* value otherwise. Example: @@global.autocommit ==> Error: autocommit is session only!!! @@session.plugin_dir ==> Error: plugin_dir is global only!!! SET @CityCount = (SELECT COUNT(*) FROM City); <== More readable !!! But if you want to select multiple variables from one query, then you will have to use: SELECT @var1=min(x), @var2=max(x) FROM t; ~ Form MTR test: let $MEB_MICRO=0 ; Sets local variables. let BACKUP_DIR='/tmp'; Sets env variable! select $MEB_MICRO; gets env. Replication ~ Replication enables you to maintain identical data on multiple servers. This has several benefits, such as allowing client load to be distributed over servers, availability of data even if a given server is taken offline or fails, and the ability to make backups using a slave server without affecting the master. MySQL Cluster ~ MySQL Cluster provides a high-availability. Distributed Replicated Block Device (DRBD) is another HA solution. ~ It works by replicating a block device from a primary to secondary server at the block level. Storage Engine ~ To enable storage engine : INSTALL PLUGIN ha_example SONAME 'ha_example.so'; UNINSTALL PLUGIN ha_example ; show engines; -- to set session specific default storage engine, do: SET storage_engine=INNODB; CREATE TABLE t (i INT) ENGINE = INNODB; Multiple storage engines supported since version 3.23 Server creates tablename.frm file independent of storage engine. It contains table definition. 13.1. The MyISAM Storage Engine ~ Default engine from 4.1 onwards non-transactional full text search capability. (what about others?) Introduced in 3.23. Deprecates ISAM in 4.1. create table yourtable(i integer) engine=MYISAM; The table contains 6 integers (4*6 = 24 bytes data). /home/thava/mysql/dev/latest/install/var/mysql @sa64-v40za-blr03 $ls -l your* -rw-rw---- 1 thava wheel 8554 May 16 20:26 yourtable.frm -rw-rw---- 1 thava wheel 42 May 18 03:01 yourtable.MYD -rw-rw---- 1 thava wheel 1024 May 18 03:50 yourtable.MYI The .MYD contains data and MYI contains index. Looks like it creates index file even if the table does not contain index. 13.2. The InnoDB Storage Engine ~ - Transactional - Available from version 3.23 onwards 13.3. The MERGE Storage Engine ~ - You can treat multiple identical MyISAM tables as one. - Introduced in version 3.23 - Also called MRG_MyISAM engine. 13.4. The MEMORY (HEAP) Storage Engine ~ - Table created in memory. Only tablename.frm file with definition. - Contents lost during restart; only defintion persists. - Available atleast from version 4.0 (or earlier?) Used to be called HEAP. The BDB (BerkeleyDB) Storage Engine (Not supported now in 5.5) ~ - *Transactional* storage engine provided by *Sleepycat* to work with MySQL. - *Support removed* from Version 5.0 The EXAMPLE Storage Engine ~ - Available from 4.1.3 - Just creates only definition in tablename.frm file. - insert into table fails - select succeeds with empty results 13.7. The FEDERATED Storage Engine ~ - Available from 5.0 - Enables access to table in remote MySQL server in local server. - Federated table is like a proxy table for a table in remote server. - The local definition is created in tablename.frm file -- it contains connection string to remote server and the table definition. No data is maintained locally. - Example : - CREATE TABLE federated_table ( id INT(20) NOT NULL AUTO_INCREMENT, ) ENGINE=FEDERATED CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table'; - transactions are supported (if remote table supports), but distributed transactions (XA) are not currently supported. 13.8. The ARCHIVE Storage Engine ~ - Available from version 4.1.4 - To store large amount of data without indexes in small foot print - It supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE - Uses compression on insert - *Select* does complete table scan and does *uncompress* data - Creates .frm file (defintion), .ARZ (data), .ARM (metadata) files. 13.9. The CSV Storage Engine ~ - Available from version 4.1.4 - Stores in comma separated value format in text file. - Creating table creates .frm (definition), .CSV (data), .CSM (metadata) files. - Does not support indexes, does not support partitioning. - Does not support nullable columns (from 6.0.5) 13.10. The BLACKHOLE Storage Engine ~ - Accepts data and throws it away. - select always return empty results - accept index defintion Great replication article using blackhole engine ~ http://jroller.com/dschneller/entry/mysql_replication_using_blackhole_engine Chapter 14. High Availability and Scalability ~ There are a number of solutions available for solving issues of availability and scalability. The two primary solutions supported by MySQL are MySQL Replication and MySQL Cluster. Further options are available using third-party solutions such as DRBD (Distributed Replicated Block Device) and Heartbeat, and more complex scenarios can be solved through a combination of these technologies. Replication ~ - Asynchronous. Can be done over slower links, geographically distributed. - Works across different platforms. - Slave is read only-- can not be written - Replication can be stopped and restarted later. slave catches up. MySQL Cluster ~ - Synchronous. Automatic. - All nodes connected by high speed link. - can read/write to any node in the cluster. - Failover is automatic. Internationalization ~ A character set is a set of symbols; An encoding determines the byte sequence representation of the character sequence. Some encodings can use 1 byte per char or 2 bytes per char or variable number of bytes per character. Examples: ================================================================== Character Set Encoding String Representation ================================================================== latin1 UTF-8 Multi-byte; uses escape sequences; Can represent any character whose binary value can go up to 2 bytes values (or more). This encoding is capable of encoding any charset. latin1 UTF-7 UTF-7 can encode any charset. It is 7bits clean. Initially intended for Mail headers, however base64 is more commonly used. It's use is limited. For C language and most languages, only encoding is important. The decoding("this string") outputs => Lists of (binary char values) How those binary chars will be displayed is application problem and it is the job of xterm (or other terminal) to render that char depending on current charset and current font. The unicode is usually encoded using UTF-8 and there is no need to keep track of "current charset" since it supports almost all chars. However for special custom applications, we may still need to support the concept of "special charset" and it's encoding logic (UTF-8 or whatever). So how will you implement "encoding" ? It is a set of routines, which will take input string and output the sequence of integers with each integer representing character's binary value. Modified UTF-8 ~ In Modified UTF-8,[19] the null character (U+0000) is encoded as 0xC0,0x80; this is not valid UTF-8[20] because it is not the shortest possible representation. Modified UTF-8 strings never contain any actual null bytes but can contain all Unicode code points including U+0000,[21] which allows such strings (with a null byte appended) to be processed by traditional null-terminated string functions. All known Modified UTF-8 implementations also treat the surrogate pairs as in CESU-8. In normal usage, the *Java programming language* supports standard UTF-8 when reading and writing strings through InputStreamReader and OutputStreamWriter. However it uses *Modified UTF-8 for object serialization* [22] for the Java Native Interface,[23] and for embedding constant strings in class files.[24] |Collation| is a set of rules for comparing : - binary comparison, case insensitive, etc. MySQL supports character sets at server, database, table or even *column level. MySQL supports the use of character sets for the MyISAM, MEMORY, NDBCLUSTER, and InnoDB storage engines only. SET NAMES 'utf8'; -- set client connection character set Note: 'utf8' Character set stands for 'unicode' character set!!! Also note that utf8 is an encoding as well. The current server character set and collation can be determined from the values of the character_set_server and collation_server system variables. These variables can be changed at runtime. MySQL can do these things for you: Store strings using a variety of character sets Compare strings using a variety of collations Mix strings with different character sets or collations in the same server, the same database, or even the same table Allow specification of character set and collation at any level In these respects, MySQL is far ahead of most other dbms. mysql> SHOW CHARACTER SET; SHOW COLLATION LIKE 'latin1%'; Collations have these general characteristics: Two different character sets cannot have the same collation. Each character set has one collation that is the default collation. For example, the default collation for latin1 is latin1_swedish_ci. The output for SHOW CHARACTER SET indicates which collation is the default for each displayed character set. There is a convention for collation names: They start with the name of the character set with which they are associated, they usually include a language name, and they end with _ci (case insensitive), _cs (case sensitive), or _bin (binary). See more info at : http://www.collation-charts.org/ Therefore, the following three commands all have the same effect: shell> mysqld shell> mysqld --character-set-server=latin1 shell> mysqld --character-set-server=latin1 \ --collation-server=latin1_swedish_ci The current server character set and collation can be determined from the values of the character_set_server and collation_server system variables. These variables can be changed at runtime. CREATE TABLE Table1 ( column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci ); For the simple statement SELECT 'string', the string has the character set and collation defined by the character_set_connection and collation_connection system variables. SELECT 'string'; SELECT _latin1'string'; SELECT _latin1'string' COLLATE latin1_danish_ci; Standard SQL defines NCHAR or NATIONAL CHAR as a way to indicate that a CHAR column should use some predefined character set. MySQL 6.0 uses utf8 as this predefined character set. For example, these data type declarations are equivalent: CHAR(10) CHARACTER SET utf8 NATIONAL CHARACTER(10) NCHAR(10) As are these: VARCHAR(10) CHARACTER SET utf8 NATIONAL VARCHAR(10) NCHAR VARCHAR(10) NATIONAL CHARACTER VARYING(10) NATIONAL CHAR VARYING(10) You can use N'literal' (or n'literal') to create a string in the national character set. These statements are equivalent: SELECT N'some text'; SELECT n'some text'; SELECT _utf8'some text'; Collations ~ Implicit charset conversion: When sending results from the server to a client: SET NAMES latin1; SELECT utf8_bin_column FROM t2; SET NAMES latin1 COLLATE latin1_bin; mysql> SET NAMES latin1 COLLATE latin1_bin; <= Though bin collation charset is non-binary mysql> SELECT LOWER('aA'), UPPER('zZ'); So lower() works fine!!!! +-------------+-------------+ | LOWER('aA') | UPPER('zZ') | +-------------+-------------+ | aa | ZZ | +-------------+-------------+ SET NAMES binary; ==> Now lower() does not work! ======================================================================== Type Comments ======================================================================== [NATIONAL] CHAR[(M)] M is 0-255; Default M is 1; [CHARACTER SET csname] Default is NULL else '' [COLLATE collation_name] utf8 default charset; CHAR BINARY Same as CHAR - But collation is binary. VARCHAR BINARY Same as VARCHAR but collation is binary. e.g. collation latin1_bin and utf8_bin. Given charset may have many collations with it. Note: It is diff from VARBINARY VARCHAR(M) M is 0-64K. (2 bytes used for storing length) Most famous char type. BINARY(M) Binary byte string upto 0-255 bytes. CHAR BYTE Alias for BINARY VARBINARY(M) Byte string upto 0-64K bytes. 2bytes for length. TINYBLOB Like VARBINARY(255) but 1 byte reserved for length. TINYTEXT Like VARCHAR(255) but 1 byte for length. supports charset. BLOB[(M)] Binary upto 64K size. Like VARBINARY but size is optional while declaring. MEDIUMBLOB Like VARBINARY(16M) bytes. 3 bytes for size. You don't specify size for MEDIUMBLOB! LONGBLOB Like VARBINARY(4G) bytes. 4 bytes for size. No M. TEXT[(M)] Like VARCHAR(64K) chars. But M is optional. MEDIUMTEXT Like VARCHAR(16MB) chars. No M. LONGTEXT Like VARCHAR(4G) chars. No M. ENUM('value1','value2',...) Max of 64K distinct values (using 2 bytes) CHARSET .... SET('value1','value2',...) Max of 64 elements. Uses one 64bit integer. BLOB and TEXT columns cannot be assigned a default value. (can be NULL) TIMESTAMP can be assigned CURRENT_TIMESTAMP as default value, but no other time types (like DATE, DATETIME) can be assigned like this. For a column declared NOT NULL and default value not applicable (e.g. BLOB), all insert statements must explicitly insert it. INSERT INTO t VALUES(); <== OK INSERT INTO t VALUES(DEFAULT); If NOT NULL : ======================================================================== Type Default Value ======================================================================== All numeric 0 DATE, TIME, DATETIME 0000 value. String '' ENUM First ENUM value. ================================================================== Note: The difference between bitmap string and empty string: create temporary table a select b'' as v; The above creates v binary(0) => data type. create temporary table b select '' as v; The above creates v char(0) => data type. Note: MySQL uses little endian format for most use of data. ================================================================== What are Temporal Data Types in MySQL? ~ ================================================================== I want to know about temporal data types in MySQL 5. Any detail please? MySQL provides data types for storing different kinds of temporal information. The following table summarizes the storage requirements and ranges for the date and time data types. In the following descriptions, the terms YYYY, MM, DD, hh, mm, and ss stand for a year, month, day of month, hour, minute, and second value, respectively: ======================================================================== Type Storage Required Range ======================================================================== DATE 3 bytes '1000-01-01' to '9999-12-31' TIME 3 bytes '-838:59:59' to '838:59:59' (interval) DATETIME 8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59' TIMESTAMP 4 bytes '1970-01-01 00:00:00' to mid-year 2037 YEAR(2|4) 1 byte 1901 to 2155 (for YEAR(4)), 1970 to 2069 (for YEAR(2)) Each temporal data type also has a "zero" value that's used when you attempt to store an illegal value. The "zero" value is represented in a format appropriate for the type (such as '0000-00-00' for DATE values and '00:00:00' for TIME) values. MySQL represents date values in 'YYYY-MM-DD' format and time values in 'hh:mm:ss' format when it displays them. * Default for TIMESTAMP is NOT NULL ! * First TIMESTAMP column is auto-set to last-modification-timestamp if not explicitly assigned value! * The SUM() and AVG() aggregate functions do not work with temporal values. SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name; SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name; SELECT '2008-02-31' + INTERVAL 0 DAY; SELECT LAST_DAY('2008-02-31'); # 2008-02-29 CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP # same as ts TIMESTAMP; Above is default for 1st timestamp column!!! ); ================================================================== MySQL server sql_mode ~ ================================================================== Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode; Most Important SQL Modes ~ ANSI (Combination Mode For: Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.) This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section. STRICT_TRANS_TABLES ~ If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. TRADITIONAL ~ Make MySQL behave like a traditional SQL database system. A simple description of this mode is *give an error instead of a warning* when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section. SQL Modes could be *DB2, ORACLE, MSSQL* etc. Full List: ALLOW_INVALID_DATES ANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE NO_AUTO_CREATE_USER NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE NO_ENGINE_SUBSTITUTION NO_FIELD_OPTIONS NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES STRICT_TRANS_TABLES Overflow handling ~ If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard. If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. ============================================================================== Note about mtr : If you have a copy of mysqld running on the machine where you want to run the test suite, you do not have to stop it, as long as it is not using ports 9306 or 9307. If either of those ports is taken, you should set the MTR_BUILD_THREAD environment variable to an appropriate value, and the test suite will use a different set of ports for master, slave, NDB, and Instance Manager). For example: shell> export MTR_BUILD_THREAD=31 shell> ./mysql-test-run.pl [options] [test_name] ============================================================================== Tip: To enable binary logging, use the following option in my.cnf : datadir = /drbd/mysql/data log-bin = mysql-bin Tip: Use dedicated network interfaces -- like 1-1 cross-over ethernet cable connection for HighAvailability replication connection Tip: Virtualization: Native Virtualization: e.g. VirutalBox : Host OS/Guest OS concepts. ParaVirtualization (Hypervisor) : e.g. Xen, Solaris xVM(based on Xen); Better h/w perf. Dedicated CPU, disk etc possible. OS Level Virtualization: BSD Jails, Solaris Zones Multiple instances of same OS on same hardware. Note: Cluster: - Each Tx is associated with GCI (Global checkpoint identifier). e.g. when checkpoint 15 is completed all txns with GCI 15 have been written to disk. Tip: Printing MySQL Error code: shell> perror 126 127 132 134 135 136 141 144 145 MySQL error code 126 = Index file is crashed MySQL error code 127 = Record-file is crashed ======================================================================== MySQL Transactions ~ show variables like '%isolation%'; show variables like 'binlog_format'; show variables like '%binlog%'; [mysqld] transaction-isolation = REPEATABLE-READ Note: Server Variable and option option has different names! mysql> show variables like '%isolation%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ mysql> SET GLOBAL tx_isolation=REPEATABLE-READ; # Global Level mysql> SET [SESSION] tx_isolation=REPEATABLE-READ; # Session Level Note: Changing global tx_isolation does not affect existing Sessions!!! Repeatable Read Vs Read-committed --------------------------------- With Repeatable Read, you don't consume too many locks or anything-- but you consume lot of "undo" log space, because 'the oldest version' must be read-accessible even if the tx is never going to commit (successfully). See: http://mysqldump.azundris.com/archives/77-Transactions-An-InnoDB-Tutorial.html For READ-Modify-Write framework to work, you have to use : SELECT ... LOCK IN SHARE MODE (sets an IS lock) OR SELECT ... FOR UPDATE sets an IX lock. Serializable Mode All regular select statements converted to LOCK IN SHARE MODE START TRANSACTION [ WITH CONSISTENT SNAPSHOT ] MVCC of INNODB * Allows multiple non-blocking consistent reads * Still allows only one write lock on a table among all threads! * The repeatable-read thread gets to see consistent read, but while updating it updates the latest committed copy!!! What is the use of repeatable-read then ????? !!!! * If you ever plan to update the records, use only SERIALIZABLE mode! * Atleast read-committed is not misleading, since it does not guarantee any 'time snapshot'. Transaction locks ~ create table account(id int primary key, balance real) engine=InnoDB; On both clients you have to enter the command: set autocommit=0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; client 1: select * from account; Client 2: (read and write) update account set balance = balance +1; commit; ---- reverse the above 2 statements. Note: InnoDB claims to implement 'Row-level locking' and multi-versioning. But this is not same as MVCC as implemented by Postgress. In the true MVCC implementation, not even row locking is needed-- 2 write operations can go ahead and only one will be eventually committed (the other one will fail on COMMIT statement). InnoDB Lock Modes A shared (S) lock permits a transaction to read a row. An exclusive (X) lock permits a transaction to update or delete a row. Intention shared (IS): Transaction T intends to set S locks on individual rows in table t. Intention exclusive (IX): Transaction T intends to set X locks on those rows. For example, SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock. If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that permits other transactions to read the examined rows but not to update or delete them. Note: To implement REPEATABLE-READ, you don't necessarily need SHARED LOCK-- you just need a multi-versioning system. You may have problem while committing the txn if there was another transaction which have updated the same rows that you have updated. Try this: mysql> CREATE TABLE t (i INT) ENGINE = InnoDB; Query OK, 0 rows affected (1.07 sec) mysql> INSERT INTO t (i) VALUES(1); Query OK, 1 row affected (0.09 sec) ======================================================================== Client-1 Client-2 ======================================================================== START TRANSACTION; SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; START TRANSACTION; DELETE FROM t WHERE i = 1; DELETE FROM t WHERE i = 1; Deadlock detection Fired, InnoDB chooses to abort Client-2. Client-1's shared lock is promoted to exclusive lock. ======================================================================== Q: Can you check if you are inside a transaction which already started ? SEE show innodb status; for innodb tx status. connection_id() returns current connection id. You can manually check, output will be like: ---TRANSACTION 0 50179, not started, process no 27798, OS thread id 29879816 ---TRANSACTION 0 50178, ACTIVE 4059 sec, process no 27798, OS thread id 29877809 Q: What is START TRANSACTION WITH CONSISTENT SNAPSHOT ? Only For InnoDB; Usually Tx not started until INNODB table is read/written to. This is == START TRANSACTION; select ... FROM innodb_table; Applies only when current isolation level = REPEATABLE-READ | SERIALIZABLE Otherwise it just gets ignored and real tx starts later. Q: Can MySQL 5.5 perform ACID transactions? Yes. All current MySQL versions support transactions. The InnoDB storage engine offers full ACID transactions with row-level locking, multi-versioning, nonlocking repeatable reads, and all four SQL standard isolation levels. The NDB storage engine supports READ COMMITTED transaction isolation level only. ======================================================================== Each InnoDB installation has at least one datafile, commonly called *ibdata1* and at least two *redo* logfiles named ib_logfile0 and ib_logfile1*. ibdata1 also contains *undo log* inside it. ======================================================================== Q: What is DECLARE .. HANDLER statement ? This is for use with in stored procedure or statement block. Example: mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1)); mysql> delimiter // mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; -> SET @x = 1; -> INSERT INTO test.t VALUES (1); -> SET @x = 2; -> INSERT INTO test.t VALUES (1); -> SET @x = 3; -> END; -> // call handlerdemo; select @x; prints @x = 3 That is because eventhough second stmt should have failed due to PKEY violation, it succeeds dueto CONTINUE handler. See Also: SIGNAL/RESIGNAL conditions. ======================================================================== B.4.14: Can MySQL 5.5 stored routines return result sets? Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client/server protocol for this to work. This means that for instance in PHP, you need to use the mysqli extension rather than the old mysql extension. ======================================================================== B.4.18: Can I pass a cursor as an IN parameter to a stored procedure? In MySQL 5.5, cursors are available inside stored procedures only. ======================================================================== B.4.21: Can I commit or roll back transactions inside a stored procedure? Yes. However, you cannot perform transactional operations within a stored function. Note: Stored procedure is more powerful. Stored function is supposed to be primitive (like calculating sin(theta)), so tx operations are not expected. ======================================================================== MySQL SHOW syntax ~ SHOW AUTHORS SHOW CHARACTER SET [like_or_where] SHOW COLLATION [like_or_where] SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where] SHOW CONTRIBUTORS SHOW CREATE DATABASE db_name SHOW CREATE EVENT event_name SHOW CREATE FUNCTION func_name SHOW CREATE PROCEDURE proc_name SHOW CREATE TABLE tbl_name SHOW CREATE TRIGGER trigger_name SHOW CREATE VIEW view_name SHOW DATABASES [like_or_where] SHOW ENGINE engine_name {STATUS | MUTEX} SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset,] row_count] SHOW [FULL] EVENTS SHOW FUNCTION CODE func_name SHOW FUNCTION STATUS [like_or_where] SHOW GRANTS FOR user SHOW INDEX FROM tbl_name [FROM db_name] SHOW INNODB STATUS SHOW OPEN TABLES [FROM db_name] [like_or_where] SHOW PLUGINS SHOW PROCEDURE CODE proc_name SHOW PROCEDURE STATUS [like_or_where] SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n] /* Exec time breakup*/ SHOW PROFILES SHOW SCHEDULER STATUS SHOW [GLOBAL | SESSION] STATUS [like_or_where] SHOW TABLE STATUS [FROM db_name] [like_or_where] SHOW TABLES [FROM db_name] [like_or_where] SHOW TRIGGERS [FROM db_name] [like_or_where] SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] SHOW WARNINGS [LIMIT [offset,] row_count] like_or_where: LIKE 'pattern' | WHERE expr If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL ?%? and ?_? wildcard characters. The pattern is useful for restricting statement output to matching values. Several SHOW statements also accept a WHERE clause that provides more flexibility in specifying which rows to display. See Section 20.28, ?Extensions to SHOW Statements?. Tip: SHOW TABLES FROM INFORMATION_SCHEMA; Tip: Multi-table updates are supported from MySQL 3* version. UPDATE items,month SET items.price=month.price WHERE items.id=month.id; Delete multi-table: The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching: DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; OR DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; Note: DELETE FROM t1 WHERE ... == DELETE t1 FROM t1 WHERE ... Frequently used show commands ~ SHOW OPEN TABLES [FROM db_name] [like_or_where] SHOW [GLOBAL | SESSION] STATUS [like_or_where] SHOW TABLE STATUS [FROM db_name] [like_or_where] SHOW TABLES [FROM db_name] [like_or_where] SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] SHOW [FULL] PROCESSLIST SHOW [STORAGE] ENGINES SHOW ENGINE engine_name {STATUS | MUTEX} SHOW PLUGINS 12.2.4. HANDLER Syntax ~ HANDLER tbl_name OPEN [ [AS] alias] HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...) [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST } [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name READ { FIRST | NEXT } [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name CLOSE e.g. create table t1 (i integer); insert into t1 values(10); handler t1 read first; handler t1 read next; Note: table name used as cursor name! You can't use handler t1 read first without opening it before: handler t1 open; 12.1.11. CREATE EVENT Syntax ~ Example: CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1; Binlog Behaviour of mysql ~ - DDLs are immediately committed. - What about updates ? Does it get bin-logged ? - Even statement inside transactions are immediately logged with BEGIN st1; st2; ... etc. : And binlog position keeps growing inside txn - Note this Error: mysql> delete from in1; ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' ================================================================== How to use MySQL Profiling ~ ================================================================== If you are using PHP connection, do the following: $mysqliObj->query("SET profiling = 1"); $mysqliObj->query("SELECT query_id, SUM(duration) FROM information_schema.profiling GROUP BY query_id ORDER BY query_id DESC LIMIT 1;") Or you can execute SHOW PROFILES command. profiling is session variable: Profiling is enabled per session. When a session ends, its profiling information is lost. mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE T1 (id INT); Query OK, 0 rows affected (0.01 sec) mysql> SHOW PROFILES; +----------+----------+--------------------------+ | Query_ID | Duration | Query | +----------+----------+--------------------------+ | 0 | 0.000088 | SET PROFILING = 1 | | 1 | 0.000136 | DROP TABLE IF EXISTS t1 | | 2 | 0.011947 | CREATE TABLE t1 (id INT) | +----------+----------+--------------------------+ 3 rows in set (0.00 sec) mysql> SHOW PROFILE; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | checking permissions | 0.000040 | | creating table | 0.000056 | | After create | 0.011363 | | query end | 0.000375 | | freeing items | 0.000089 | | logging slow query | 0.000019 | | cleaning up | 0.000005 | +----------------------+----------+ 7 rows in set (0.00 sec) mysql> SHOW PROFILE FOR QUERY 1; +--------------------+----------+ | Status | Duration | +--------------------+----------+ | query end | 0.000107 | | freeing items | 0.000008 | | logging slow query | 0.000015 | | cleaning up | 0.000006 | +--------------------+----------+ 4 rows in set (0.00 sec) mysql> SHOW PROFILE CPU FOR QUERY 2; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | checking permissions | 0.000040 | 0.000038 | 0.000002 | | creating table | 0.000056 | 0.000028 | 0.000028 | | After create | 0.011363 | 0.000217 | 0.001571 | | query end | 0.000375 | 0.000013 | 0.000028 | | freeing items | 0.000089 | 0.000010 | 0.000014 | | logging slow query | 0.000019 | 0.000009 | 0.000010 | | cleaning up | 0.000005 | 0.000003 | 0.000002 | +----------------------+----------+----------+------------+ 7 rows in set (0.00 sec) ================================================================== Explain basic server options ~ ================================================================== --user=mysql --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --skip-external-locking --bind-address=127.0.0.1 --key_buffer=16M --max_allowed_packet=16M --thread_stack=192K --thread_cache_size=8 --myisam-recover=BACKUP --query_cache_limit=1M --query_cache_size=16M --log_error=/var/log/mysql/error.log --expire_logs_days=10 --max_binlog_size=100M --port=6666 --socket=/tmp/my-mysql.sock --basedir=/home/thava/mysql/install/5 --datadir=/home/thava/mysql/install/5/var --skip-external-locking --key_buffer_size=16M --max_allowed_packet=1M --table_open_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --log-bin=mysql-bin --binlog_format=mixed --server-id=1 Symbolic Links ? mysqld --symbolic-links / --skip-symbolic-links Windows: Can use linkname.sym file which contains target_path. Unix: Create table (...) DATA DIRECTORY=path, INDEX DIRECTORY=path ; <== For MyISAM Tables Only. ================================================================== MySQL Release History ~ ================================================================== MySQL 5.0 introduces Stored Procedures, Triggers, Views and Information Schema. MySQL Features and Syntax: ================================================================== CAST Function ~ ================================================================== Examples: Convert HEX (string) to DECIMAL SELECT CAST(X'01ABCDEF' AS DECIMAL); SELECT CAST(NOW() as DATE); etc. Cast is especially useful when the result is inserted into another (temp) table where the type of operand has to be known! e.g. create table newtable as select 'some const', 10, * from t; Suppose you want to insert NULL of specific type, how to do it ? create table newtable as select CAST(NULL as signed integer); (but CAST(NULL as INT) does not work! for whatever reason) ================================================================== MySQL Locking ~ ================================================================== ------------------------------------------------------------------ Server Internal Locking ------------------------------------------------------------------ MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables. For these engines, table writes given higher priority than reads to avoid write starving. mysqld with --low-priority-updates changes this. MyISAM also allows concurrent insert appends(not write in middle). You can analyze the table lock contention by: mysql> SHOW STATUS LIKE 'Table%'; +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Table_locks_immediate | 1151552 | all these were granted immediately. | Table_locks_waited | 15324 | no of locks waited in the past. +-----------------------+---------+ InnoDB does not use server's table level locking. ------------------------------------------------------------------ External Locking: ------------------------------------------------------------------ Clients explicitly locks the table. Once lock table statement issued, it should include *all* tables that will be accessed until end of session or release of locks. Eg. LOCK TABLES t1 READ, t2 WRITE, t3 READ LOCAL; Can explicitly lock both for InnoDB or MyISAM tables. READ LOCAL is only for MyISAM to allow concurrent insert at the end. Lock tables also implicitly lock's all the tables used in those triggers. UNLOCK TABLES explicitly releases any table locks held by the current session. FLUSH TABLES WITH READ LOCK statement locks all tables => Used to take backup. FTWRL Does not prevent inserts into general/slow query log tables. ------------------------------------------------------------------ (Applicaton Specific, Advisory) Named Locks ------------------------------------------------------------------ GET_LOCK(str,timeout) mysql> SELECT GET_LOCK('lock1',10); -> 1 mysql> SELECT IS_FREE_LOCK('lock2'); -> 1 mysql> SELECT GET_LOCK('lock2',10); /* Second lock releases first lock */ -> 1 mysql> SELECT RELEASE_LOCK('lock2'); -> 1 mysql> SELECT RELEASE_LOCK('lock1'); -> NULL /* Because already released */ Only one lock at a time. Commits don't auto release these locks. Get Second lock releases first lock. Return 1 - OK; 0 - Lock not available/timed out; NULL - Error. Note: Not safe for statement-based binary logging or replication. ================================================================== Implicit Commits ================================================================== DDL operations. Unlock Tables command when there are tables explicitly locked; (exception -- flush tables with read lock); How do I monitor my locks ? ~ Show engine innodb status; Displays exactly the records which are locked: MySQL thread id 1, query id 59 localhost root updating delete from t *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 640 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 50179 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000001c00; asc ;; 1: len 6; hex 00000000c401; asc ;; 2: len 7; hex 80000001830110; asc ;; 3: len 4; hex 80000001; asc ;; *** (2) TRANSACTION: TRANSACTION 0 50178, ACTIVE 55 sec, process no 27798, OS thread id 2987780976 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4 lock struct(s), heap size 320, 4 row lock(s) Row locks are nicely displayed, but table locks (by server) are not displayed ??? SHOW OPEN TABLES ; ==> Command to show the tables which are locked by server; (either by lock tables command or myisam tables implicitly locked by server) innotop ===> For innodb tables. Note: Use innotop !!! Press T - Enter transaction mode Press c - Choose columns e.g. Locks,Tbl Used,Tbl Lck columns visible. innotop Very Very GOOD! =================================================================== Storage Engines ~ ================================================================== MyISAM - Old standard ISAM (Indexed Sequential Access Method) implementation by MySQL. Was ISAM, then named as MyISAM. Transaction Supported Engines: InnoDB and BDB ================================================================== Query Caching ~ ================================================================== These server config options are relevant: have_query_cache -- YES means server was not started by disabling it. query_cache_limit -- Do not cache results that are larger than this number of bytes. The default value is 1MB. query_cache_size -- Size in bytes. 16M. Max buffer for query cache. query_cache_type -- OFF/ON/DEMAND (0/1/2) DEMAND => SELECT SQL_CACHE ... query_cache_min_res_unit - 4K. Min block size allocated for query cache. Reduce if you have too many small queries. Increase if you have many large queries. SHOW STATUS LIKE '%Qcache%' => Displays statistics variables; Example: Qcache_free_blocks = 1 Qcache_total_blocks = 19 Qcache_free_memory = 16M Qcache_hits = 3219 Qcache_inserts = 9173 Qcache_lowmem_prunes = 0 ==> ???? Qcache_queries_in_cache = 8 FLUSH QUERY CACHE; ================================================================== SQL derived tables ================================================================== Derived Table = Inline Table in From class. A SQL derived table is created with a nested select statement, as in the following example: select * from (select * from table_1) derived_table_1 An equivalent group of SQL statements that uses a view instead of a SQL derived table requires both create and drop statements: create view view_1 as select * from table_1 select * from view_1 drop view view_1 ================================================================== Percona Toolkit ~ ================================================================== Good toolkit for trouble shooting and performance tuning. Installed in /home/thava/mysql/install/percona-toolkit pt-find is a tool similar to find for processes. you can search for db/tables and execute commands on them. Many such utilities. ======================================================================== Optimization ~ ======================================================================== From MySQL Manual: Some examples of queries that are very fast: SELECT COUNT(*) FROM tbl_name; SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; SELECT MAX(key_part2) FROM tbl_name WHERE key_part1=constant; SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10; MySQL resolves the following queries using only the entries from a secondary index, if the indexed columns are numeric: SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2; SELECT key_part2 FROM tbl_name GROUP BY key_part1; The following queries use the index data to retrieve the rows in sorted order without a separate sorting pass: SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... ; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... ; ======================================================================== COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table. ======================================================================== Constant Table ~ All constant tables are read first before any other tables in the query. A constant table is any of the following: An empty table or a table with one row. A table that is used with a WHERE clause on a PRIMARY KEY or a UNIQUE index, where all index parts are compared to constant expressions and are defined as NOT NULL. All of the following tables are used as constant tables: SELECT * FROM t WHERE primary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id; ======================================================================== If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table. 8.2.1.3. Optimizing LIMIT Queries MySQL sometimes optimizes a query that has a LIMIT row_count clause and no HAVING clause: If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. GROUP BY can use the keys. If it is composite key, then order of keys matters to be able to be used by GROUP BY operation. As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS. LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns 8.2.1.4. How to Avoid Full Table Scans * Minimize the OR keywords in your WHERE clauses. * If you have one index that helps to optimize one side of an OR query, and a different index that helps to optimize the other side, use a UNION operator to run separate fast queries and merge the results afterward. * create table mtable() i integer primary key using btree; -- Default is HASH !!! ... } engine = memory; ==> much better performance for range queries!!! * Use FORCE INDEX to avoid table scans: SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name; Optimizing DML ~ * Use multiple inserts in single insert into t values (...),(),()...; * you can tune the bulk_insert_buffer_size variable * When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements * For a MyISAM table that uses dynamic row format, updating a row to a longer total length may split the row. If you do this often, it is very important to use OPTIMIZE TABLE occasionally. See Section 13.7.2.4, OPTIMIZE TABLE Syntax. * The time required to delete individual rows in a MyISAM table is exactly proportional to the number of indexes. To delete rows more quickly, you can increase key cache size: by increasing *key_buffer_size* system variable. Optimizing Database Privileges : Keep perm checking simple. ======================================================================== CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); <== partial index !!! FULLTEXT Indexes * Only MyISAM * only for CHAR, VARCHAR, and TEXT columns. * On whole of column, no prefix index. Spatial Indexes * Only MyISAM supports R-tree indexes. * Others support B-tree for indexing spatial data, so it is not as powerful. ======================================================================== CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) ); However, the name index is not used for lookups in the following queries: SELECT * FROM test WHERE first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael'; ======================================================================== To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary. ======================================================================== Table 8.1. EXPLAIN Output Columns Column Meaning id The SELECT identifier select_type The SELECT type table The table for the output row type The join type possible_keys The possible indexes to choose key The index actually chosen key_len The length of the chosen key ref The columns compared to the index rows Estimate of rows to be examined filtered Percentage of rows filtered by table condition Extra Additional information +----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | +----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+ | 1 | SIMPLE | S | ALL | PRIMARY | NULL | NULL | NULL | 4 | | 1 | SIMPLE | R | ref | PRIMARY,boat_id | PRIMARY | 4 | case1.S.sailor_id | 1 | | 1 | SIMPLE | B | eq_ref | PRIMARY,bindex | PRIMARY | 4 | case1.R.boat_id | 1 | +----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+ +---------+---------+-------------------+------+----------+-------------+ | key | key_len | ref | rows | filtered | Extra | +---------+---------+-------------------+------+----------+-------------+ | NULL | NULL | NULL | 4 | 100.00 | | | PRIMARY | 4 | case1.S.sailor_id | 1 | 100.00 | Using index | | PRIMARY | 4 | case1.R.boat_id | 1 | 100.00 | Using where | +---------+---------+-------------------+------+----------+-------------+ explain extended select sname from Sailors S JOIN Reserves R using(sailor_id) JOIN Boats B using(boat_id) where B.color='red' +----+-------------+-------+-------+---------------+---------+--------- | id | select_type | table | type | possible_keys | key | key_len +----+-------------+-------+-------+---------------+---------+--------- | 1 | PRIMARY | B | ref | bindex | bindex | 23 | 1 | PRIMARY | S | index | NULL | PRIMARY | 4 | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL +----+-------------+-------+-------+---------------+---------+--------- +-------+------+--------------------------------+ | ref | rows | Extra | -+-------+------+--------------------------------+ | const | 2 | Using where; Using index | | NULL | 4 | Using index; Using join buffer | | NULL | NULL | Select tables optimized away | -+-------+------+--------------------------------+ ================================================================== Optimization Tricks ~ ================================================================== * Ofcourse, look at Explain outputs and see if queries use index. * Turn on Slow query log: log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 # Anything takes more than 1 sec, log it. Use mysqldumpslow to process entries in slow query log. * Change isolation level to READ-COMMITTED if you can live with that. The default isolation is: tx_isolation REPEATABLE-READ set tx_isolation = 'READ-COMMITTED'; # Quotes required. SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL {REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE} * Increase thread pool size to avoid thread recreations: set global thread_cache_size = 16 ; Default is 8 * The global variables table_open_cache, table_definition_cache are by default 64 and 256 respectively. You can increase, if necessary. * Look at http://localhost/phpmyadmin !!! Click on Status! It highlights things RED which need attention ! * InnoDB can't optimize SELECT COUNT(*) queries. Use counter tables! That's how to scale InnoDB. * On multi-column indexes, order of fields within index is important. * Co-related subquery is like a nested loop. A nested query with no-correlated component runs much faster if possible. Select * from t1 where col1 in (Select col2 from t2); O(mxn) Select * from t1 where col1 in (Select col2 from t2 where col3 = t1.col1); O(mx(query-execute-m-times)xn) * If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table. * See Also: Query Caching =========================================================== Workbench Notes ~ =========================================================== When you create table, the columns have these markers: PK - Primary Key NN - Not Null UQ - Unique BIN - Binary UN - Unsigned ZF - ZeroFill AI - AutoIncrement Privileges ~ Assume that you want to load data into a table that are in a file that is located on the server host. What privileges are necessary so that this succeeds? The server must have read access to that file, and you need the FILE privilege. ================================================================== Index Of Key Terms ~ ================================================================== mysqld_safe - Wrapper program to mysqld. Restarts mysqld. \e in mysql - Use external editor 3. Tutorial ~ SELECT DATABASE(); SHOW TABLES; SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; SELECT * FROM shop WHERE price=@min_price OR price=@max_price; CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23); The example table contains year-month-day values representing visits by users to the page. To determine how many different days in each month these visits occur, use this query: SELECT year,month,BIT_COUNT(BIT_OR(1< Aggregate func BIT_COUNT => Normal func. CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich'); SELECT * FROM animals; Using MySQL with Apache Apache configuration file: LogFormat \ "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \ \"%U\",\"%{Referer}i\",\"%{User-Agent}i\"" To load a log file in that format into MySQL, you can use a statement something like this: LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' 4. MySQL Programs mysql_install_db This script creates the MySQL database and initializes the grant tables with default privileges. It is usually executed only once, when first installing MySQL on a system. mysql_secure_installation Offers you to fix things. Important Functions ~ mysql> SELECT COALESCE(NULL,1); ==> Return first non-null -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT LEAST(34.0,3.0,5.0,767.0); -> 3.0 MySQL Tips ~ Use following option for mysql command line: pager less -n -i -S -F -X Why Quora Uses MySQL Vs MongoDB ~ From: http://www.quora.com/Quora-Infrastructure/Why-does-Quora-use-MySQL-as-the-data-store-instead-of-NoSQLs-such-as-Cassandra-MongoDB-CouchDB-etc Many of the problems created by manually partitioning the data over a large number of MySQL machines can be mitigated by creating a layer below the application and above MySQL that automatically distributes data. FriendFeed described a good example implementation of this [8]. You can actually get pretty far on a single MySQL database and not even have to worry about partitioning at the application level. You can "scale up" to a machine with lots of cores and tons of ram, plus a replica. If you have a layer of memcached servers in front of the databases (which are easy to scale out) then the database basically only has to worry about writes. You can also use S3 or some other distributed hash table to take the largest objects out of rows in the database. See Also: http://www.quora.com/What-is-Facebooks-architecture http://davidralbrecht.com/blog/2009/11/facebook-seattle-engineering-roadshow/ Todo ~ Checkout what is there at /usr/share/sql-bench in RPM installation. Additional Links ~ Refactoring MySQL Cursors http://rpbouman.blogspot.in/2006/09/refactoring-mysql-cursors.html Check for violated FK constraints: http://forge.mysql.com/tools/tool.php?id=11