SELECT mysqlgenie FROM lamp;

So there’s a whole bunch of MySQL scripts included with any release, who can say they know them all? In this blog post, we will take a look at some of these and show usage examples that may help you in your environment.

mysql_access

Let’s say you want to manage your users’ rights, and you have this user ‘john.doe’ that can connect from ‘host1′, but you aren’t sure what he can do to your db: company-staging as well as company-dev.

What do you do? Well, for example, you could use mysql_access and take a look:

/5075/bin:09:13:38:Qalbi $ ./mysqlaccess -U root -d company-staging -u john.doe -h host1
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.

Access-rights
for USER 'john.doe', from HOST 'host1', to DB 'company-staging'
	+-----------------+---+	+-----------------+---+
	| Select_priv     | Y |	| Lock_tables_priv | N |
	| Insert_priv     | N |	| Execute_priv    | N |
	| Update_priv     | N |	| Repl_slave_priv | N |
	| Delete_priv     | N |	| Repl_client_priv | N |
	| Create_priv     | N |	| Create_view_priv | N |
	| Drop_priv       | N |	| Show_view_priv  | N |
	| Reload_priv     | N |	| Create_routine_priv | N |
	| Shutdown_priv   | N |	| Alter_routine_priv | N |
	| Process_priv    | N |	| Create_user_priv | N |
	| File_priv       | N |	| Ssl_type        | ? |
	| Grant_priv      | N |	| Ssl_cipher      | ? |
	| References_priv | N |	| X509_issuer     | ? |
	| Index_priv      | N |	| X509_subject    | ? |
	| Alter_priv      | N |	| Max_questions   | 0 |
	| Show_db_priv    | N |	| Max_updates     | 0 |
	| Super_priv      | N |	| Max_connections | 0 |
	| Create_tmp_table_priv | N |	| Max_user_connections | 0 |
	+-----------------+---+	+-----------------+---+
BEWARE:	 Everybody can access your DB as user `john.doe' from host `host1'
      :	 WITHOUT supplying a password.
      :	 Be very careful about it!!

The following rules are used:
 db    : 'host1','company-staging','john.doe','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N'
 host  : 'Not processed: host-field is not empty in db-table.'
 user  : 'host1','john.doe','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','','0','0','0','0'

BUGs can be reported by email to bugs@mysql.com
/5075/bin:09:13:39:Qalbi $ ./mysqlaccess -U root -d company-dev -u john.doe -h host1
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.

Access-rights
for USER 'john.doe', from HOST 'host1', to DB 'company-dev'
	+-----------------+---+	+-----------------+---+
	| Select_priv     | Y |	| Lock_tables_priv | N |
	| Insert_priv     | Y |	| Execute_priv    | N |
	| Update_priv     | Y |	| Repl_slave_priv | N |
	| Delete_priv     | Y |	| Repl_client_priv | N |
	| Create_priv     | N |	| Create_view_priv | N |
	| Drop_priv       | N |	| Show_view_priv  | N |
	| Reload_priv     | N |	| Create_routine_priv | N |
	| Shutdown_priv   | N |	| Alter_routine_priv | N |
	| Process_priv    | N |	| Create_user_priv | N |
	| File_priv       | N |	| Ssl_type        | ? |
	| Grant_priv      | N |	| Ssl_cipher      | ? |
	| References_priv | N |	| X509_issuer     | ? |
	| Index_priv      | N |	| X509_subject    | ? |
	| Alter_priv      | N |	| Max_questions   | 0 |
	| Show_db_priv    | N |	| Max_updates     | 0 |
	| Super_priv      | N |	| Max_connections | 0 |
	| Create_tmp_table_priv | N |	| Max_user_connections | 0 |
	+-----------------+---+	+-----------------+---+
BEWARE:	 Everybody can access your DB as user `john.doe' from host `host1'
      :	 WITHOUT supplying a password.
      :	 Be very careful about it!!

The following rules are used:
 db    : 'host1','company-dev','john.doe','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N','N','N','N'
 host  : 'Not processed: host-field is not empty in db-table.'
 user  : 'host1','john.doe','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','','0','0','0','0'

So, as you can see, on company-staging he has only select privileges but on company-dev he has select,update,insert,delete.

Fair enough, this can be done in SQL as well:

mysql> SHOW GRANTS FOR 'john.doe'@'host1';
+-------------------------------------------------------------------------------+
| Grants for john.doe@host1                                                     |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'john.doe'@'host1'                                      |
| GRANT SELECT ON `company-staging`.* TO 'john.doe'@'host1'                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `company-dev`.* TO 'john.doe'@'host1' |
+-------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.db WHERE user='john.doe' AND host = 'host1' AND db IN ('company-staging', 'company-dev') \G
*************************** 1. row ***************************
                 Host: host1
                   Db: company-dev
                 User: john.doe
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: host1
                   Db: company-staging
                 User: john.doe
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)

But mysql_access can give you that without logging into mysql.

mysqlshow

How many people know that there is a program to show what databases or tables you have? For example, take a look at this:

/5075/bin:09:31:36:Qalbi $ ./mysqlshow -uroot
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| company-dev        |
| company-staging    |
| employees          |
| mysql              |
| test               |
+--------------------+
/5075/bin:09:31:44:Qalbi $ ./mysqlshow -uroot employees
Database: employees
+---------------+
|    Tables     |
+---------------+
| departments   |
| dept_emp      |
| dept_manager  |
| employees     |
| salaries      |
| titles        |
| v_emp_by_dept |
+---------------+

This shows the databases this user has access to, as well as the tables in the ‘employees’ database.

If you want to see how many rows (and columns) you have in the tables in the employees db:

/5075/bin:09:31:49:Qalbi $ ./mysqlshow --count -uroot employees
Database: employees
+---------------+----------+------------+
|    Tables     | Columns  | Total Rows |
+---------------+----------+------------+
| departments   |        2 |          9 |
| dept_emp      |        4 |     331603 |
| dept_manager  |        4 |         24 |
| employees     |        6 |     300024 |
| salaries      |        4 |    2844047 |
| titles        |        4 |     443308 |
| v_emp_by_dept |        2 |          9 |
+---------------+----------+------------+
7 rows in set.

But yet again, we can see this with pure SQL too (databases first, then tables in ‘employees’ and then the rows per table in ‘employees’:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| company-dev        |
| company-staging    |
| employees          |
| mysql              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees';
+---------------+
| TABLE_NAME    |
+---------------+
| departments   |
| dept_emp      |
| dept_manager  |
| employees     |
| salaries      |
| titles        |
| v_emp_by_dept |
+---------------+
7 rows in set (0.63 sec)

mysql> SELECT
    ->  t.TABLE_NAME, COUNT(1) AS "Cols", t.TABLE_ROWS
    -> FROM
    ->  TABLES t
    -> INNER JOIN
    ->  COLUMNS c ON (
    ->   t.TABLE_NAME=c.TABLE_NAME
    -> )
    -> WHERE
    ->  t.TABLE_SCHEMA='employees'
    -> GROUP BY
    ->  c.TABLE_NAME;
+---------------+------+------------+
| TABLE_NAME    | Cols | TABLE_ROWS |
+---------------+------+------------+
| departments   |    2 |          9 |
| dept_emp      |    4 |     332289 |
| dept_manager  |    4 |         24 |
| employees     |    6 |     300695 |
| salaries      |    4 |    2844513 |
| titles        |    4 |     443803 |
| v_emp_by_dept |    2 |       NULL |
+---------------+------+------------+
7 rows in set (0.17 sec)

Please note that the TABLE_ROWS column is only a estimate, as all the tables (except for the view used in another blog) are InnoDB.

So this covers some initial lesser-known scripts, and I will continue to report on the usage of other less-known scripts and what they do in this series of blogs. Please stay tuned.

First off, let me wish you all a Happy 2009, and my apologies for being a bit slack with blogging in the last two months of 2008. It’s been a bunch of busy weeks, but I’m fully expecting to remedy that in this year.

Second, let me welcome you to this series on refactoring MySQL applications. I will cover best practices and useful tips, as well as show working examples of potential loopholes and solutions.

So, you are a DBA or a developer, and you’re faced with a problem. Your years-old application (perhaps you inherited it from a former co-worker) is now starting to keel over, and your choice of responses is limited.

Let’s start by saying that there are other ways to reach performance, but in some cases refactoring may be the only way that is possible to pursue, for one reason or another. Let’s take the refactoring way, focusing on SQL rather than applications, as there’s a lot that can be done on this side.

But first, why refactoring? Refactoring normally sits between parameter-tuning and throwing more hardware at the problem, and can be a very cost-effective way of solving performance or scalability issues.

So let’s start with a good point about composite indexes that seems to be forgotten sometimes. Given the following data . . . 

group, category, name, manufacturer

 . . . we want to grab all Electronics from Apple. Given that we have an index on category and manufacturer, the data for category could look like this (visually explained, not following the column specifications): category=electronics, group=mp3, name=whatever name is used, manufacturer=apple/microsoft . . . 

...
Electronics|MP3|iPod|Apple
Electronics|MP3|Zune|Microsoft
Electronics|TV|Apple TV|Apple
...

And so on. But, if we want to grab all the Apple electronics, we can quickly see that we’re also fetching other data (Microsoft zune in this case), which means that it’s not really that optimal, given that this is potentially a big table and fetching unneeded data is never good.

What if we use a index on (category,manufacturer) instead?

...
Electronics|Apple|MP3|iPod
Electronics|Apple|TV|Apple TV
Electronics|Microsoft|MP3|Zune
...

As shown here, the even more selective index allows us to group together all apple electronics and pull them out without including the Microsoft ones (if the query is for example SELECT ... FROM t1 WHERE category='Electronics' AND manufacturer='Apple'). However, don’t go around asking for example: SELECT ... FROM t1 WHERE manufacturer='Apple' if you only have this composite index, as it does not form a prefix of our (category,manufacturer) index, and will not be usable.

Using autocommit=1 can also be a major player in performance, so investigate how often you really should commit, and try to optimize that, as committing includes overhead, and will slow down your application. On the other hand, not committing may result in concurrency issues and/or data loss, so you need to find the sweet spot there.

To use views, or not to? Well, in some cases views can increase performance, and in some cases they can reduce it. Let’s take a look at the “employees” example database, trying to count the number of employees in a specific department (whether or not they’re still working there).

Let us also compare a view with another example. So, first let’s create a view:

mysql> CREATE VIEW v_emp_by_dept AS
    -> SELECT dept_no, COUNT(emp_no) Employees
    -> FROM dept_emp
    -> GROUP BY dept_no;
Query OK, 0 rows affected (0.11 sec)

mysql> set query_cache_type=0;
Query OK, 0 rows affected (0.02 sec)

And then let’s get some data out of it:

mysql> SELECT SQL_NO_CACHE dept_no, Employees
    -> FROM v_emp_by_dept
    -> WHERE
    ->  dept_no = 'd002';
+---------+-----------+
| dept_no | Employees |
+---------+-----------+
| d002    |     17346 |
+---------+-----------+
1 row in set (0.18 sec)

All fine and okay. But, 0.18 seconds, even on my (admittedly overloaded) laptop? We gotta be able to decrease that. Let’s try another approach:

mysql> SELECT SQL_NO_CACHE dept_no, Employees
    -> FROM (
    ->  SELECT SQL_NO_CACHE dept_no, COUNT(emp_no) Employees
    ->  FROM
    ->   dept_emp
    ->  WHERE
    ->   dept_no='d002'
    ->  GROUP BY
    ->   dept_no
    -> ) t1;
+---------+-----------+
| dept_no | Employees |
+---------+-----------+
| d002    |     17346 |
+---------+-----------+
1 row in set (0.01 sec)

0.01 sec, much better (obviously). Basically, the difference here is that I push the WHERE clause up to before the group by, and minimize the number of rows that have to be grouped.

Let’s do another simple example, but this time on unnecessary joins that may be the result of copy/pasted code from some earlier employee. Let’s say you want to grab the employees first name, last name, and department number (perhaps for automatic processing later on). That previous employee apparently grabbed the query from something that also showed the name of the department:

mysql> SELECT
    ->  e.first_name, e.last_name, de.dept_no
    -> FROM
    ->  employees e INNER JOIN
    ->  dept_emp de ON (e.emp_no=de.emp_no)
    ->  LEFT JOIN departments d ON(de.dept_no=d.dept_no)
    -> LIMIT 2;
+------------+-----------+---------+
| first_name | last_name | dept_no |
+------------+-----------+---------+
| Georgi     | Facello   | d005    |
| Bezalel    | Simmel    | d007    |
+------------+-----------+---------+
2 rows in set (0.02 sec)

And the explain plan:

+----+-------------+-------+--------+----------------+---------+---------+----------------------+--------+-------------+
| id | select_type | table | type   | possible_keys  | key     | key_len | ref                  | rows   | Extra       |
+----+-------------+-------+--------+----------------+---------+---------+----------------------+--------+-------------+
|  1 | SIMPLE      | e     | ALL    | PRIMARY        | NULL    | NULL    | NULL                 | 300201 |             |
|  1 | SIMPLE      | de    | ref    | PRIMARY,emp_no | PRIMARY | 4       | employees.e.emp_no   |      1 | Using index |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY        | PRIMARY | 4       | employees.de.dept_no |      1 | Using index |
+----+-------------+-------+--------+----------------+---------+---------+----------------------+--------+-------------+
3 rows in set (0.00 sec)

Now, this isn’t the worst example, but still it does include a totally unnecessary table, departments, which we can get rid of and still get the needed number. Basically it’s a useless table in this query, and should be removed. It’s simply a result of someone not checking what she/he really needed for the query to work.

To wrap up this first part on refactoring, remember to push WHERE clauses as far up as you can to eliminate fetching unneeded rows and then later removing them. Also remember how indexes work and do not include useless tables in a join—identify what you want the query to accomplish, and how you can do that without touching more rows than needed. And to really top it off, use transactions and don’t commit after every insert/update unless you really need to. Grouping them together may very well increase performance.

In Part 2, we will cover example programs, loops/cursors, procedures, and how to optimize those without having to change the application behaviour. Stand by.