Data Loading...

2018-MySQL® Notes for Professionals book Flipbook PDF

2018-MySQL® Notes for Professionals book

126 Views
14 Downloads
FLIP PDF 1.88MB

MySQL MySQL Notes for Professionals

®

Notes for Professionals

100+ pages

of professional hints and tricks

GoalKicker.com

Free Programming Books

Disclaimer This is an unocial free book created for educational purposes and is not aliated with ocial MySQL® group(s) or company(s). All trademarks and registered trademarks are the property of their respective owners

Contents About ................................................................................................................................................................................... 1 Chapter 1: Getting started with MySQL ............................................................................................................. 2 Section 1.1: Getting Started ........................................................................................................................................... 2 Section 1.2: Information Schema Examples ................................................................................................................ 6

Chapter 2: WHERE id=8

Example return value: Query OK, 1 row aﬀected (0.06 sec) The int value can be inserted in a query without quotes. Strings and Dates must be enclosed in single quote ' or double quotes ". Deleting a row into a MySQL table DELETE FROM mytable WHERE id=8

Example return value: Query OK, 1 row aﬀected (0.06 sec) This will delete the row having id is 8. Selecting rows based on conditions in MySQL SELECT * FROM mytable WHERE username = "myuser";

Return value: +----+----------+---------------------+ | id | username | email |

GoalKicker.com – MySQL® Notes for Professionals

3

+----+----------+---------------------+ | 1 | myuser | [email protected] | +----+----------+---------------------+

1 row in set (0.00 sec) Show list of existing =", you are saying "This is not a comparison, this is a SET".) 3. You can set a variable to be the result of a select statement using INTO (This was particularly helpful when I needed to dynamically choose which Partitions to query from)

SET @start_date = '2015-07-20'; SET @end_date = '2016-01-31'; #this gets the year month value to use as the partition names SET @start_yearmonth = (SELECT EXTRACT(YEAR_MONTH FROM @start_date)); SET @end_yearmonth = (SELECT EXTRACT(YEAR_MONTH FROM @end_date)); #put the partitions into a variable SELECT GROUP_CONCAT(partition_name) FROM information_schema.partitions p WHERE table_name = 'partitioned_table' AND SUBSTRING_INDEX(partition_name,'P',-1) BETWEEN @start_yearmonth AND @end_yearmonth INTO @partitions; #put the query in a variable. You need to do this, because mysql did not recognize my variable as a variable in that position. You need to concat the value of the variable together with the rest of the query and then execute it as a stmt. SET @query = CONCAT('CREATE TABLE part_of_partitioned_table (PRIMARY KEY(id)) SELECT partitioned_table.* FROM partitioned_table PARTITION(', @partitions,') JOIN users u USING(user_id) WHERE date(partitioned_table.date) BETWEEN ', @start_date,' AND ', @end_date); #prepare the statement from @query PREPARE stmt FROM @query; #drop table DROP TABLE IF EXISTS tech.part_of_partitioned_table; #create table using statement EXECUTE stmt;

GoalKicker.com – MySQL® Notes for Professionals

28

Section 8.2: Row Number and Group By using variables in Select Statement Let's say we have a table team_person as below: +======+===========+ | team | person | +======+===========+ | A | John | +------+-----------+ | B | Smith | +------+-----------+ | A | Walter | +------+-----------+ | A | Louis | +------+-----------+ | C | Elizabeth | +------+-----------+ | B | Wayne | +------+-----------+

CREATE TABLE team_person AS SELECT 'A' team, 'John' person UNION ALL SELECT 'B' team, 'Smith' person UNION ALL SELECT 'A' team, 'Walter' person UNION ALL SELECT 'A' team, 'Louis' person UNION ALL SELECT 'C' team, 'Elizabeth' person UNION ALL SELECT 'B' team, 'Wayne' person;

To select the table team_person with additional row_number column, either SELECT @row_no := @row_no+1 AS row_number, team, person FROM team_person, (SELECT @row_no := 0) t;

OR SET @row_no := 0; SELECT @row_no := @row_no + 1 AS row_number, team, person FROM team_person;

will output the result below: +============+======+===========+ | row_number | team | person | +============+======+===========+ | 1 | A | John | +------------+------+-----------+ | 2 | B | Smith | +------------+------+-----------+ | 3 | A | Walter | +------------+------+-----------+ | 4 | A | Louis | +------------+------+-----------+ | 5 | C | Elizabeth | +------------+------+-----------+ | 6 | B | Wayne | +------------+------+-----------+

GoalKicker.com – MySQL® Notes for Professionals

29

Finally, if we want to get the row_number group by column team SELECT @row_no := IF(@prev_val = t.team, @row_no + 1, 1) AS row_number ,@prev_val := t.team AS team ,t.person FROM team_person t, (SELECT @row_no := 0) x, (SELECT @prev_val := '') y ORDER BY t.team ASC,t.person DESC; +============+======+===========+ | row_number | team | person | +============+======+===========+ | 1 | A | Walter | +------------+------+-----------+ | 2 | A | Louis | +------------+------+-----------+ | 3 | A | John | +------------+------+-----------+ | 1 | B | Wayne | +------------+------+-----------+ | 2 | B | Smith | +------------+------+-----------+ | 1 | C | Elizabeth | +------------+------+-----------+

GoalKicker.com – MySQL® Notes for Professionals

30

Chapter 9: Comment MySQL Section 9.1: Adding comments There are three types of comment: # This comment continues to the end of line -- This comment continues to the end of line /* This is an in-line comment */ /* This is a multiple-line comment */

Example: SELECT * FROM t1; -- this is comment CREATE TABLE stack( /*id_user int, username varchar(30), password varchar(30) */ id int );

The -- method requires that a space follows the -- before the comment begins, otherwise it will be interpreted as a command and usually cause an error. #This comment works /*This comment works.*/ --This comment does not.

Section 9.2: Commenting table deﬁnitions CREATE TABLE menagerie.bird ( bird_id INT NOT NULL AUTO_INCREMENT, species VARCHAR(300) DEFAULT NULL COMMENT 'You can include genus, but never subspecies.', INDEX idx_species (species) COMMENT 'We must search on species often.', PRIMARY KEY (bird_id) ) ENGINE=InnoDB COMMENT 'This table was inaugurated on February 10th.';

Using an = after COMMENT is optional. (Oﬃcial docs) These comments, unlike the others, are saved with the schema and can be retrieved via SHOW CREATE TABLE or from information_schema.

GoalKicker.com – MySQL® Notes for Professionals

31

Chapter 10: INSERT Section 10.1: INSERT, ON DUPLICATE KEY UPDATE INSERT INTO `table_name` (`index_field`, `other_field_1`, `other_field_2`) VALUES ('index_value', 'insert_value', 'other_value') ON DUPLICATE KEY UPDATE `other_field_1` = 'update_value', `other_field_2` = VALUES(`other_field_2`);

This will INSERT into table_name the speciﬁed values, but if the unique key already exists, it will update the other_field_1 to have a new value.

Sometimes, when updating on duplicate key it comes in handy to use VALUES() in order to access the original value that was passed to the INSERT instead of setting the value directly. This way, you can set diﬀerent values by using INSERT and UPDATE. See the example above where other_field_1 is set to insert_value on INSERT or to update_value on UPDATE while other_field_2 is always set to other_value.

Crucial for the Insert on Duplicate Key Update (IODKU) to work is the schema containing a unique key that will signal a duplicate clash. This unique key can be a Primary Key or not. It can be a unique key on a single column, or a multi-column (composite key).

Section 10.2: Inserting multiple rows INSERT INTO `my_table` (`field_1`, `field_2`) VALUES (' mycompany employee.txt

Section 51.6: Import a standard csv $ mysqlimport --fields-optionally-enclosed-by='"' --fields-terminated-by=, --lines-terminated-by="\r\n" mycompany employee.csv

GoalKicker.com – MySQL® Notes for Professionals

147

Chapter 52: LOAD is to be speciﬁed in the where clause.

Query: SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;

Result: Number of Records: 14

City Aachen

Country Germany

Berlin

Germany

Berlin

Germany

Brandenburg Germany Cunewalde

Germany

Cuxhaven

Germany

Frankfurt

Germany

Frankfurt a.M. Germany Köln

Germany

Leipzig

Germany

Mannheim

Germany

München

Germany

Münster

Germany

Stuttgart

Germany

GoalKicker.com – MySQL® Notes for Professionals

151

Chapter 54: MySQL client Parameter

Description

-D --

make the MySQL client PROMPT show current user@host [, use_unicode=True)

For web pages, one of these:

Section 58.2: PHP In php.ini (this is the default after PHP 5.6): default_charset UTF-8

When building a web page: header('Content-type: text/plain; charset=UTF-8');

When connecting to MySQL: (for mysql:) (for mysqli:) (for PDO:)

Do not use the mysql_* API! $mysqli_obj->set_charset('utf8mb4'); $db = new PDO('dblib:host=host;dbname=db;charset=utf8', $user, $pwd);

In code, do not use any conversion routines. For >

For JSON, to avoid \uxxxx: $t = json_encode($s, JSON_UNESCAPED_UNICODE);

GoalKicker.com – MySQL® Notes for Professionals

159

Chapter 59: Time with subsecond precision Section 59.1: Get the current time with millisecond precision SELECT NOW(3)

does the trick.

Section 59.2: Get the current time in a form that looks like a Javascript timestamp Javascript timestamps are based on the venerable UNIX time_t source address="1.2.3.4" service name="mysql" accept' # I force everything to the drop zone. Season the above command to taste.

Now restart ﬁrewalld service firewalld restart

Next, log in to dbserver's mysql server: mysql -uroot -p

Issue the following to create a user for the client. note REQUIRE SSL in GRANT statement. GRANT ALL PRIVILEGES ON *.* TO ‘iamsecure’@’appclient’ IDENTIFIED BY ‘dingdingding’ REQUIRE SSL; FLUSH PRIVILEGES; # quit mysql

You should still be in /root/certs/mysql from the ﬁrst step. If not, cd back to it for one of the commands below. Create the client certiﬁcates openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem openssl rsa -in client-key.pem -out client-key.pem openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Note: I used the same common name for both server and client certiﬁcates. YMMV. Be sure you're still /root/certs/mysql/ for this next command Combine server and client CA certiﬁcate into a single ﬁle: cat server-cert.pem client-cert.pem > ca.pem

Make sure you see two certiﬁcates: cat ca.pem

END OF SERVER SIDE WORK FOR NOW. Open another terminal and

GoalKicker.com – MySQL® Notes for Professionals

169

ssh appclient

As before, create a permanent home for the client certiﬁcates mkdir /etc/pki/tls/certs/mysql/

Now, place the client certiﬁcates (created on dbserver) on appclient. You can either scp them over, or just copy and paste the ﬁles one by one. scp dbserver # copy files from dbserver to appclient # exit scp

Again, be sure to set permissions on the folder and ﬁles. mysql needs full ownership and access. chown -R mysql:mysql /etc/pki/tls/certs/mysql

You should have three ﬁles, each owned by user mysql: /etc/pki/tls/certs/mysql/ca.pem /etc/pki/tls/certs/mysql/client-cert.pem /etc/pki/tls/certs/mysql/client-key.pem

Now edit appclient's MariaDB/MySQL conﬁg in the [client] section. vi /etc/my.cnf # i [client] ssl-ca=/etc/pki/tls/certs/mysql/ca.pem ssl-cert=/etc/pki/tls/certs/mysql/client-cert.pem ssl-key=/etc/pki/tls/certs/mysql/client-key.pem # :wq

Restart appclient's mariadb service: systemctl restart mariadb

still on the client here This should return: ssl TRUE mysql --ssl --help

Now, log in to appclient's mysql instance mysql -uroot -p

Should see YES to both variables below show variables LIKE '%ssl'; have_openssl YES have_ssl YES

Initially I saw have_openssl NO

GoalKicker.com – MySQL® Notes for Professionals

170

A quick look into mariadb.log revealed: SSL error: Unable to get certiﬁcate from '/etc/pki/tls/certs/mysql/client-cert.pem' The problem was that root owned client-cert.pem and the containing folder. The solution was to set ownership of /etc/pki/tls/certs/mysql/ to mysql. chown -R mysql:mysql /etc/pki/tls/certs/mysql

Restart mariadb if needed from the step immediately above NOW WE ARE READY TO TEST THE SECURE CONNECTION We're still on appclient here Attempt to connect to dbserver's mysql instance using the account created above. mysql -h dbserver -u iamsecure -p # enter password dingdingding (hopefully you changed that to something else)

With a little luck you should be logged in without error. To conﬁrm you are connected with SSL enabled, issue the following command from the MariaDB/MySQL prompt: \s

That's a backslash s, aka status That will show the status of your connection, which should look something like this: Connection id: 4 Current database: Current user: iamsecure@appclient SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.X.X-MariaDB MariaDB Server Protocol version: 10 Connection: dbserver via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 42 min 13 sec

If you get permission denied errors on your connection attempt, check your GRANT statement above to make sure there aren't any stray characters or ' marks. If you have SSL errors, go back through this guide to make sure the steps are orderly. This worked on RHEL7 and will likely work on CentOS7, too. Cannot conﬁrm whether these exact steps will work elsewhere.

GoalKicker.com – MySQL® Notes for Professionals

171

Hope this saves someone else a little time and aggravation.

GoalKicker.com – MySQL® Notes for Professionals

172

Chapter 63: Create New User Section 63.1: Create a MySQL User For creating new user, We need to follow simple steps as below : Step 1: Login to MySQL as root $ mysql -u root -p

Step 2 : We will see mysql command prompt mysql> CREATE USER 'my_new_user'@'localhost' IDENTIFIED BY 'test_password';

Here, We have successfully created new user, But this user won't have any permissions, So to assign permissions to user use following command : mysql> GRANT ALL PRIVILEGES ON my_db.* TO 'my_new_user'@'localhost' identified by 'my_password';

Section 63.2: Specify the password The basic usage is: mysql> CREATE USER 'my_new_user'@'localhost' IDENTIFIED BY 'test_password';

However for situations where is not advisable to hard-code the password in cleartext it is also possible to specify directly, using the directive PASSWORD, the hashed value as returned by the PASSWORD() function: mysql> select PASSWORD('test_password'); -- returns *4414E26EDED6D661B5386813EBBA95065DBC4728 mysql> CREATE USER 'my_new_user'@'localhost' IDENTIFIED BY PASSWORD '*4414E26EDED6D661B5386813EBBA95065DBC4728';

Section 63.3: Create new user and grant all priviliges to schema grant all privileges on schema_name.* to 'new_user_name'@'%' identified by 'newpassword';

Attention: This can be used to create new root user

Section 63.4: Renaming user rename user 'user'@'%' to 'new_name`@'%';

If you create a user by mistake, you can change his name

GoalKicker.com – MySQL® Notes for Professionals

173

Chapter 64: Security via GRANTs Section 64.1: Best Practice Limit root (and any other SUPER-privileged user) to GRANT ... TO root@localhost ...

That prevents access from other servers. You should hand out SUPER to very few people, and they should be aware of their responsibility. The application should not have SUPER. Limit application logins to the one database it uses: GRANT ... ON dbname.* ...

That way, someone who hacks into the application code can't get past dbname. This can be further reﬁned via either of these: GRANT SELECT ON dname.* ... -- "read only" GRANT ... ON dname.tblname ... -- "just one table"

The readonly may also need 'safe' things like GRANT SELECT, CREATE TEMPORARY TABLE ON dname.* ...

-- "read only"

As you say, there is no absolute security. My point here is there you can do a few things to slow hackers down. (Same goes for honest people gooﬁng.) In rare cases, you may need the application to do something available only to root. this can be done via a "Stored Procedure" that has SECURITY DEFINER (and root deﬁnes it). That will expose only what the SP does, which might, for example, be one particular action on one particular table.

Section 64.2: Host (of user@host) The "host" can be either a host name or an IP address. Also, it can involve wild cards. GRANT SELECT ON db.* TO sam@'my.domain.com' IDENTIFIED BY 'foo';

Examples: Note: these usually need to be quoted localhost -- the same machine as mysqld 'my.domain.com' -- a specific domain; this involves a lookup '11.22.33.44' -- a specific IP address '192.168.1.%' -- wild card for trailing part of IP address. (192.168.% and 10.% and 11.% are "internal" ip addresses.)

Using localhost relies on the security of the server. For best practice root should only be allowed in through localhost. In some cases, these mean the same thing: 0.0.0.1 and ::1.

GoalKicker.com – MySQL® Notes for Professionals

174

Chapter 65: Change Password Section 65.1: Change MySQL root password in Linux To change MySQL's root user password: Step 1: Stop the MySQL server. in Ubuntu or Debian: sudo /etc/init.d/mysql stop

in CentOS, Fedora or Red Hat Enterprise Linux: sudo /etc/init.d/mysqld stop

Step 2: Start the MySQL server without the privilege system. sudo mysqld_safe --skip-grant-tables &

or, if mysqld_safe is unavailable, sudo mysqld --skip-grant-tables &

Step 3: Connect to the MySQL server. mysql -u root

Step 4: Set a new password for root user. Version > 5.7

FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; FLUSH PRIVILEGES; exit; Version ≤ 5.7

FLUSH PRIVILEGES; SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); FLUSH PRIVILEGES; exit;

Note: The ALTER USER syntax was introduced in MySQL 5.7.6. Step 5: Restart the MySQL server. in Ubuntu or Debian: sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start

in CentOS, Fedora or Red Hat Enterprise Linux: sudo /etc/init.d/mysqld stop sudo /etc/init.d/mysqld start

Section 65.2: Change MySQL root password in Windows When we want to change root password in windows, We need to follow following steps : Step 1 : Start your Command Prompt by using any of below method :

GoalKicker.com – MySQL® Notes for Professionals

175

Perss Crtl+R or Goto Start Menu > Run and then type cmd and hit enter Step 2 : Change your directory to where MYSQL is installed, In my case it's C:\> cd C:\mysql\bin

Step 3 : Now we need to start mysql command prompt C:\mysql\bin> mysql -u root mysql

Step 4 : Fire query to change root password mysql> SET PASSWORD FOR root@localhost=PASSWORD('my_new_password');

Section 65.3: Process 1. Stop the MySQL (mysqld) server/daemon process. 2. Start the MySQL server process the --skip-grant-tables option so that it will not prompt for a password: mysqld_safe --skip-grant-tables &

3. Connect to the MySQL server as the root user: mysql -u root 4. Change password: (5.7.6 and newer): ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password'; (5.7.5 and older, or MariaDB): SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new-password); flush privileges; quit;

5. Restart the MySQL server. Note: this will work only if you are physically on the same server. Online Doc: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

GoalKicker.com – MySQL® Notes for Professionals

176

Chapter 66: Recover and reset the default root password for MySQL 5.7+ After MySQL 5.7, when we install MySQL sometimes we don't need to create a root account or give a root password. By default when we start the server, the default password is stored in the mysqld.log ﬁle. We need to login in to the system using that password and we need to change it.

Section 66.1: What happens when the initial start up of the server Given that the data directory of the server is empty: The server is initialized. SSL certiﬁcate and key ﬁles are generated in the data directory. The validate_password plugin is installed and enabled. The superuser account 'root'@'localhost' is created. The password for the superuser is set and stored in the error log ﬁle.

Section 66.2: How to change the root password by using the default password To reveal the default "root" password: shell> sudo grep 'temporary password' /var/log/mysqld.log

Change the root password as soon as possible by logging in with the generated temporary password and set a custom password for the superuser account: shell> mysql -uroot -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass5!';

Note: MySQL's validate_password plugin is installed by default. This will require that passwords contain at least one upper case letter, one lower case letter, one digit, and one special character, and that the total password length is at least 8 characters.

Section 66.3: reset root password when " /var/run/mysqld' for UNIX socket ﬁle don't exists" if I forget the password then I'll get error. $ mysql -u root -p

Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I tried to solve the issue by ﬁrst knowing the status: $ systemctl status mysql.service

mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor GoalKicker.com – MySQL® Notes for Professionals

177

preset: en Active: active (running) since Thu 2017-06-08 14:31:33 IST; 38s ago Then I used the code mysqld_safe --skip-grant-tables & but I get the error: mysqld_safe Directory '/var/run/mysqld' for UNIX socket ﬁle don't exists.

$ systemctl stop mysql.service $ ps -eaf|grep mysql $ mysqld_safe --skip-grant-tables &

I solved:

$ mkdir -p /var/run/mysqld $ chown mysql:mysql /var/run/mysqld

Now I use the same code mysqld_safe --skip-grant-tables & and get mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql If I use $ mysql -u root I'll get : Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2017, Oracle and/or its aﬃliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its aﬃliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> Now time to change password:

mysql> use mysql mysql> describe user;

Reading table information for completion of table and column names You can turn oﬀ this feature to get a quicker startup with -A Database changed mysql> FLUSH PRIVILEGES; mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('newpwd');

or If you have a mysql root account that can connect from everywhere, you should also do: UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

GoalKicker.com – MySQL® Notes for Professionals

178

Alternate Method: USE mysql UPDATE user SET Password = PASSWORD('newpwd') WHERE Host = 'localhost' AND User = 'root';

And if you have a root account that can access from everywhere: USE mysql UPDATE user SET Password = PASSWORD('newpwd') WHERE Host = '%' AND User = 'root';`enter code here

now need to quit from mysql and stop/start FLUSH PRIVILEGES; sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start

now again ` mysql -u root -p' and use the new password to get mysql>

GoalKicker.com – MySQL® Notes for Professionals

179

Chapter 67: Recover from lost root password Section 67.1: Set root password, enable root user for socket and http access Solves problem of: access denied for user root using password YES Stop mySQL: sudo systemctl stop mysql

Restart mySQL, skipping grant tables: sudo mysqld_safe --skip-grant-tables

Login: mysql -u root

In SQL shell, look if users exist: select User, password,plugin FROM mysql.user ;

Update the users (plugin null enables for all plugins): update mysql.user set password=PASSWORD('mypassword'), plugin = NULL WHERE User = 'root'; exit;

In Unix shell stop mySQL without grant tables, then restart with grant tables: sudo service mysql stop sudo service mysql start

GoalKicker.com – MySQL® Notes for Professionals

180

Chapter 68: MySQL Performance Tips Section 68.1: Building a composite index In many situations, a composite index performs better than an index with a single column. To build an optimal composite index, populate it with columns in this order. = column(s) from the WHERE clause ﬁrst. (eg, INDEX(a,b,...) for WHERE a=12 AND b='xyz' ...) IN column(s); the optimizer may be able to leapfrog through the index.

One "range" (eg x BETWEEN 3 AND 9, name LIKE 'J%') It won't use anything past the ﬁrst range column. All the columns in GROUP BY, in order All the columns in ORDER BY, in order. Works only if all are ASC or all are DESC or you are using 8.0. Notes and exceptions: Don't duplicate any columns. Skip over any cases that don't apply. If you don't use all the columns of WHERE, there is no need to go on to GROUP BY, etc. There are cases where it is useful to index only the ORDER BY column(s), ignoring WHERE. Don't "hide" a column in a function (eg DATE(x) = ... cannot use x in the index.) 'Preﬁx' indexing (eg, text_col(99)) is unlikely to be helpful; may hurt. More details and tips .

Section 68.2: Optimizing Storage Layout for InnoDB Tables 1. In InnoDB, having a long PRIMARY KEY (either a single column with a lengthy value, or several columns that form a long composite value) wastes a lot of disk space. The primary key value for a row is duplicated in all the secondary index records that point to the same row. Create an AUTO_INCREMENT column as the primary key if your primary key is long. 2. Use the VARCHAR data type instead of CHAR to store variable-length strings or for columns with many NULL values. A CHAR(N) column always takes N characters to store data, even if the string is shorter or its value is NULL. Smaller tables ﬁt better in the buﬀer pool and reduce disk I/O. When using COMPACT row format (the default InnoDB format) and variable-length character sets, such as utf8 or sjis, CHAR(N) columns occupy a variable amount of space, but still at least N bytes. 3. For tables that are big, or contain lots of repetitive text or numeric data, consider using COMPRESSED row format. Less disk I/O is required to bring data into the buﬀer pool, or to perform full table scans. Before making a permanent decision, measure the amount of compression you can achieve by using COMPRESSED versus COMPACT row format. Caveat: Benchmarks rarely show better than 2:1 compression and there is a lot of overhead in the buﬀer_pool for COMPRESSED. 4. Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. The reorganized tables require less disk I/O to perform full table scans. This is a straightforward technique that can improve performance when other techniques such as improving index usage or tuning application code are not practical. Caveat: Regardless of table size, OPTIMIZE TABLE should only rarely be performed. This is because it is costly, and rarely improves the table enough to be worth it. InnoDB is reasonably good at keeping its B+Trees free of a lot of wasted space.

GoalKicker.com – MySQL® Notes for Professionals

181

OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes. The beneﬁts come from improved packing of data within indexes, and reduced fragmentation within the tablespaces and on disk. The beneﬁts vary depending on the data in each table. You may ﬁnd that there are signiﬁcant gains for some and not for others, or that the gains decrease over time until you next optimize the table. This operation can be slow if the table is large or if the indexes being rebuilt do not ﬁt into the buﬀer pool. The ﬁrst run after adding a lot of data to a table is often much slower than later runs.

GoalKicker.com – MySQL® Notes for Professionals

182

Chapter 69: Performance Tuning Section 69.1: Don't hide in function A common mistake is to hide an indexed column inside a function call. For example, this can't be helped by an index: WHERE DATE(dt) = '2000-01-01'

Instead, given INDEX(dt) then these may use the index: WHERE dt = '2000-01-01'

-- if `dt` is datatype `DATE`

This works for DATE, DATETIME, TIMESTAMP, and even DATETIME(6) (microseconds): WHERE dt >= '2000-01-01' AND dt < '2000-01-01' + INTERVAL 1 DAY

Section 69.2: OR In general OR kills optimization. WHERE a = 12 OR b = 78

cannot use INDEX(a,b), and may or may not use INDEX(a), INDEX(b) via "index merge". Index merge is better than nothing, but only barely. WHERE x = 3 OR x = 5

is turned into WHERE x IN (3, 5)

which may use an index with x in it.

Section 69.3: Add the correct index This is a huge topic, but it is also the most important "performance" issue. The main lesson for a novice is to learn of "composite" indexes. Here's a quick example: INDEX(last_name, first_name)

is excellent for these: WHERE last_name = '...' WHERE first_name = '...' AND last_name = '...'

-- (order in WHERE does not matter)

but not for WHERE first_name = '...' -- order in INDEX _does_ matter WHERE last_name = '...' OR first_name = '...' -- "OR" is a killer

GoalKicker.com – MySQL® Notes for Professionals

183

Section 69.4: Have an INDEX The most important thing for speeding up a query on any non-tiny table is to have a suitable index. WHERE a = 12 WHERE a > 12

--> INDEX(a) --> INDEX(a)

WHERE a = 12 AND b > 78 WHERE a > 12 AND b > 78

--> INDEX(a,b) is more useful than INDEX(b,a) --> INDEX(a) or INDEX(b); no way to handle both ranges

ORDER BY x --> INDEX(x) ORDER BY x, y --> INDEX(x,y) in that order ORDER BY x DESC, y ASC --> No index helps - because of mixing ASC and DESC

Section 69.5: Subqueries Subqueries come in several ﬂavors, and they have diﬀerent optimization potential. First, note that subqueries can be either "correlated" or "uncorrelated". Correlated means that they depend on some value from outside the subquery. This generally implies that the subquery must be re-evaluated for each outer value. This correlated subquery is often pretty good. Note: It must return at most 1 value. It is often useful as an alternative to, though not necessarily faster than, a LEFT JOIN. SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c FROM u ... SELECT a, b, ( SELECT MAX(x) ... ) AS c FROM u ... SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c FROM u ...

This is usually uncorrelated: SELECT ... FROM ( SELECT ... ) AS a JOIN b ON ...

Notes on the FROM-SELECT: If it returns 1 row, great. A good paradigm (again "1 row") is for the subquery to be ( SELECT @n := 0 ), thereby initializing an `@variable for use in the rest or the query. If it returns many rows and the JOIN also is ( SELECT ... ) with many rows, then eﬃciency can be terrible. Pre-5.6, there was no index, so it became a CROSS JOIN; 5.6+ involves deducing the best index on the temp tables and then generating it, only to throw it away when ﬁnished with the SELECT.

Section 69.6: JOIN + GROUP BY A common problem that leads to an ineﬃcient query goes something like this: SELECT ... FROM a JOIN b ON ... WHERE ... GROUP BY a.id

First, the JOIN expands the number of rows; then the GROUP BY whittles it back down the the number of rows in a. GoalKicker.com – MySQL® Notes for Professionals

184

There may not be any good choices to solve this explode-implode problem. One possible option is to turn the JOIN into a correlated subquery in the SELECT. This also eliminates the GROUP BY.

Section 69.7: Set the cache correctly innodb_buffer_pool_size should be about 70% of available RAM.

Section 69.8: Negatives Here are some things that are not likely to help performance. They stem from out-of-date information and/or naivety. InnoDB has improved to the point where MyISAM is unlikely to be better. PARTITIONing rarely provides performance beneﬁts; it can even hurt performance.

Setting query_cache_size bigger than 100M will usually hurt performance. Increasing lots of values in my.cnf may lead to 'swapping', which is a serious performance problem. "Preﬁx indexes" (such as INDEX(foo(20))) are generally useless. OPTIMIZE TABLE is almost always useless. (And it involves locking the table.)

GoalKicker.com – MySQL® Notes for Professionals

185

Appendix A: Reserved Words MySQL has some special names called reserved words. A reserved word can be used as an identiﬁer for a table, column, etc. only if it's wrapped in backticks (`), otherwise it will give rise to an error. To avoid such errors, either don't use reserved words as identiﬁers or wrap the oﬀending identiﬁer in backticks.

Section A.1: Errors due to reserved words When trying to select from a table called order like this select * from order

the error rises: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1 Reserved keywords in MySQL need to be escaped with backticks (`) select * from `order`

to distinguish between a keyword and a table or column name. See also: Syntax error due to using a reserved word as a table or column name in MySQL.

GoalKicker.com – MySQL® Notes for Professionals

186

Credits Thank you greatly to all the people from Stack Overﬂow Documentation who helped provide this content, more changes can be sent to [email protected] for new content to be published or updated 0x49D1 4thﬂoorstudios a coder AJ Abubakkar Adam agold Alex Recarey alex9311 Alvaro Flaño Larrondo Aman Dhanda Aminadav Andy andygeers Ani Menon animuson aries12 arushi Aryo Asaph Asjad Athick Athafoud BacLuc Barranka Batsu Ben Visness Benvorth Bhavin Solanki bhrached Blag CGritton ChintaMoney Chip Chris CodeWarrior CPHPython dakab Damian Yerrick Darwin von Corax Dinidu Hewage Dipen Shah Divya Drew e4c5 Eugene falseﬁve Filipe Martins

Chapter 10 Chapter 12 Chapter 62 Chapters 21, 24 and 30 Chapter 20 Chapter 14 Chapter 50 Chapter 31 Chapter 25 Chapter 6 Chapter 1 Chapter 63 Chapter 1 Chapter 25 Chapters 3 and 53 Chapter 6 Chapter 52 Chapter 68 Chapter 25 Chapters 50 and 52 Chapter 3 Chapter 1 Chapter 67 Chapters 19, 25, 31 and 50 Chapters 11, 20, 50, 51, 54 and 63 Chapter 31 Chapters 3, 16 and 25 Chapter 3 Chapter 52 Chapter 37 Chapter 10 Chapter 38 Chapter 3 Chapter 12 Chapters 1 and 38 Chapters 6 and 25 Chapter 2 Chapter 15 Chapters 25 and 30 Chapter 10 Chapter 1 Chapter 24 Chapters 2, 3, 4, 7, 10, 11, 12, 21, 25, 29, 30, 39 and 46 Chapters 24, 34, 37 and 52 Chapters 38, 56 and 62 Chapter 60 Chapter 14

GoalKicker.com – MySQL® Notes for Professionals

187

Florian Genser FMashiro ForguesR gabe3886 Hardik Kanjariya ツ HCarrasko Horen Hugo Buﬀ Ian Gregory Ian Kenney jan_kiran JayRizzo Jean Vitor John M JohnLBevan juergen d KalenGi KartikKannapur Khurram kolunar Kruti Patel Lahiru Lijo LiuYan 刘研 llanato Lucas Paolillo Majid Marina K. Matas Vaitkevicius Matt S Mattew Whitt Matthew matthiasunt mayojava Md. Nahiduzzaman Rose MohaMad molavec Nate Vaughan Nathaniel Ford Nikita Kurtin Noah van der Aa O. Jones Optimised Bikash Panda Parth Patel ParthaSen Philipp Ponnarasu R.K123 RamenChef ratchet RationalDev rene

Chapters 13 and 36 Chapters 17 and 61 Chapter 11 Chapters 10 and 11 Chapters 63 and 65 Chapter 25 Chapter 10 Chapter 10 Chapter 6 Chapter 10 Chapter 38 Chapter 1 Chapter 25 Chapter 25 Chapter 26 Chapters 1, 2, 20, 29, 30 and 70 Chapter 50 Chapter 3 Chapters 11 and 12 Chapters 8, 20, 26, 30, 31 and 33 Chapter 3 Chapter 66 Chapter 14 Chapter 26 Chapter 30 Chapter 29 Chapter 48 Chapter 24 Chapters 26, 36 and 37 Chapters 6 and 21 Chapter 18 Chapter 50 Chapter 16 Chapter 26 Chapter 1 Chapters 2, 34 and 35 Chapter 41 Chapter 57 Chapter 54 Chapter 24 Chapter 27 Chapters 1, 3, 15, 19, 21, 22, 29, 32 and 59 Chapter 16 Chapters 1 and 25 Chapters 1, 11, 25 and 27 Chapter 66 Chapters 10 and 16 Chapters 3, 7, 11, 12, 24, 28, 29, 44, 45, 49, 50 and 55 Chapters 7, 24 and 27 Chapter 50 Chapters 24 and 37 Chapter 36 Chapter 39

GoalKicker.com – MySQL® Notes for Professionals

188

Richard Hamilton Rick Rick James Riho Rodrigo Darti da Costa Romain Vincent Saroj Sasmal SeeuD1 Sevle skytreader Stefan Rogin Steve Chambers still_learning strangeqargo Strawberry Sumit Gupta SuperDJ Tarik Thuta Aung Timothy Tushar patel user2314737 user3617558 user5389107 user6655061 userlond vijeeshin Viktor WAF wangengzheng Wenzhong whrrgarbl winter YCF_L ypercube yukoﬀ Yury Fedorov zeppelin

Chapters 2 and 19 Chapters 16, 25 and 42 Chapters 2, 3, 5, 6, 7, 9, 10, 11, 13, 14, 16, 18, 19, 20, 21, 24, 25, 26, 27, 28, 29, 30, 31, 33, 36, 38, 40, 42, 43, 44, 45, 46, 47, 48, 50, 52, 53, 54, 55, 58, 64, 65, 68 and 69 Chapters 10 and 18 Chapter 68 Chapter 1 Chapter 25 Chapter 3 Chapter 12 Chapter 25 Chapter 25 Chapter 26 Chapters 7 and 50 Chapters 10 and 11 Chapter 25 Chapters 5 and 21 Chapter 4 Chapter 18 Chapters 14 and 26 Chapter 25 Chapter 27 Chapters 23 and 70 Chapter 16 Chapters 1 and 10 Chapter 8 Chapter 30 Chapter 28 Chapter 65 Chapters 9, 14, 20, 24, 26, 37, 50 and 63 Chapter 18 Chapter 56 Chapter 14 Chapter 33 Chapters 1, 3, 9, 23, 25, 26, 27 and 37 Chapter 11 Chapter 44 Chapter 25 Chapter 10

GoalKicker.com – MySQL® Notes for Professionals

189

You may also like