�ݺ�ߣ

10 things you might
not know about
MySQL
Jorge Bernal <jbernal@warp.es>

Version 0.1

Before
mysql> SELECT AVG(Population) FROM City_huge;
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| AVG(Population) |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 354359.9948 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.58 sec)

+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| AVG(Population) |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 354359.9948 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

After
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| AVG(Population) |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 354359.9948 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| AVG(Population) |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 354359.9948 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

The magic
SET global query_cache_size=8 * 1024 * 1024;
mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
| Variable_name                | Value   |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
5 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Qc%';
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 8378312 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 0       |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+

2
Life beyond MyISAM/
InnoDB

ARCHIVE MyISAM

80% compression

But...

• Only INSERT and SELECT
• No indexing

CSV
mysql> ALTER TABLE City ENGINE=CSV;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

root@warhol:/usr/local/mysql/data/world$ head City.CSV
1,quot;Kabulquot;,quot;AFGquot;,quot;Kabolquot;,1780000
2,quot;Qandaharquot;,quot;AFGquot;,quot;Qandaharquot;,237500
3,quot;Heratquot;,quot;AFGquot;,quot;Heratquot;,186800
4,quot;Mazar‐e‐Sharifquot;,quot;AFGquot;,quot;Balkhquot;,127800
5,quot;Amsterdamquot;,quot;NLDquot;,quot;Noord‐Hollandquot;,731200
6,quot;Rotterdamquot;,quot;NLDquot;,quot;Zuid‐Hollandquot;,593321
7,quot;Haagquot;,quot;NLDquot;,quot;Zuid‐Hollandquot;,440900
8,quot;Utrechtquot;,quot;NLDquot;,quot;Utrechtquot;,234323
9,quot;Eindhovenquot;,quot;NLDquot;,quot;Noord‐Brabantquot;,201843
10,quot;Tilburgquot;,quot;NLDquot;,quot;Noord‐Brabantquot;,193238

Merge
mysql> SHOW CREATE TABLE allweekG
[...]
Create Table: CREATE TABLE `allweek` (
  `ID` int(11) NOT NULL DEFAULT '0',
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  `modtime` datetime DEFAULT NULL,
  KEY `modtime` (`modtime`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
UNION=(`monday`,`tuesday`,`wednesday`,`thursday`,`city
date`)

mysql>   CREATE TABLE t (
    ‐>    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
    ‐>   );

mysql>   INSERT INTO t (id)
    ‐>   VALUES (NULL);
Query OK, 1 row affected (0.04 sec)

mysql>   SELECT *
    ‐>   FROM t
    ‐>   WHERE id IS NULL;
+‐‐‐‐+
| id |
+‐‐‐‐+
|  1 |
+‐‐‐‐+

But...
• id is AUTO_INCREMENT, so it’s 1
• let’s run that again
    ‐>   );

    ‐>   VALUES (NULL);

mysql>   SELECT *
    ‐>   FROM t
+‐‐‐‐+
| id |
+‐‐‐‐+
|  1 |
+‐‐‐‐+

    ‐>   );

    ‐>   VALUES (NULL);

mysql>   SELECT *
    ‐>   FROM t
+‐‐‐‐+
| id |
+‐‐‐‐+
|  1 |
+‐‐‐‐+
mysql>   SELECT *
    ‐>   FROM t
Empty set (0.00 sec)

mysql> SELECT *   FROM t;
+‐‐‐‐+
| id |
+‐‐‐‐+
|  1 |
+‐‐‐‐+

WTF???

• WHERE ID IS NULL acts as
WHERE ID = LAST_ISERT_ID()
• But only the first time
• Brought to you by some weird ODBC
compatibility decision

4
How to get random
data

SELECT name
FROM random
ORDER BY RAND()
LIMIT 1

X
SELECT name
FROM random
ORDER BY RAND()
LIMIT 1
Wrong!

There is a better way
SELECT name
FROM random JOIN
(
SELECT CEIL(
  RAND() *
  (SELECT MAX(id) FROM random))
  AS id
) AS r2
USING (id);

Really?
ORDER BY RAND() Subquery

10.000,00

1.000,00

100,00
Seconds

10,00

1,00

0,10
100 1000 10000 100000
Table Size

So...

Every time you use ORDER BY RAND()...
God kills a kitten
Please, think of the kittens

There’s no need to
index the whole
column

Name is CHAR(52)
mysql> SHOW CREATE TABLE CountryG
** 1. row **
       Table: Country
Create Table: CREATE TABLE `Country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North
America','Africa','Oceania','Antarctica','South America') NOT NULL
DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> SELECT COUNT(*) AS Total, COUNT(DISTINCT Name) AS Diff, COUNT(*)
‐ COUNT(DISTINCT Name) AS Dupes FROM Country;
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Total | Diff | Dupes |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|   239 |  239 |     0 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
mysql> SELECT COUNT(*) AS Total, COUNT(DISTINCT LEFT(Name,50)) AS Diff,
COUNT(*) ‐ COUNT(DISTINCT LEFT(Name,50)) AS Dupes FROM Country;
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|   239 |  239 |     0 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+

+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|   239 |  239 |     0 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+

mysql> SELECT COUNT(*) AS Total, COUNT(DISTINCT Name) AS Diff, COUNT(*)
‐ COUNT(DISTINCT Name) AS Dupes FROM Country;
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|   239 |  239 |     0 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|   239 |  239 |     0 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+

+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
Got it? Cool! Now...
|   239 |  239 |     0 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+

There is a better way
CREATE DEFINER=`root`@`localhost` PROCEDURE `pref_index`(t_name CHAR(255), c_name CHAR(255))
BEGIN

DECLARE plength INT DEFAULT 1;
SET @q = CONCAT('SELECT CHARACTER_MAXIMUM_LENGTH INTO @maxlen
                                                        FROM INFORMATION_SCHEMA.COLUMNS
                                                        WHERE TABLE_SCHEMA = quot;world2quot; AND
                                                                        TABLE_NAME = quot;',
t_name,'quot; AND
                                                                        COLUMN_NAME = quot;',
c_name, 'quot;');
PREPARE q FROM @q;
EXECUTE q;
DEALLOCATE PREPARE q;

REPEAT
        SET @qq = CONCAT('SELECT COUNT(*) ‐ COUNT(DISTINCT ',c_name,')
                                                                into @dupe FROM ',t_name);
        SET @pq = CONCAT('SELECT COUNT(*) ‐ COUNT(DISTINCT LEFT(',c_name,',',plength,'))
                                                                into @pdupe FROM ',t_name);

        PREPARE qs FROM @qq;
        EXECUTE qs;
        DEALLOCATE PREPARE qs;

        PREPARE ps FROM @pq;
        EXECUTE ps;
        DEALLOCATE PREPARE ps;

        SET plength = plength + 1;
UNTIL plength >= @maxlen OR @pdupe = @dupe
END REPEAT;
SELECT plength, @pdupe, @dupe;
END

mysql> CALL pref_index('Country', 'Name');
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| plength | @pdupe | @dupe |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|      18 |      0 |     0 |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+


mysql> SELECT COUNT(*) AS Total, COUNT(DISTINCT LEFT(Name,18)) AS
Diff, COUNT(*) ‐ COUNT(DISTINCT LEFT(Name,18)) AS Dupes FROM Country;
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|   239 |  239 |     0 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+

mysql> ALTER TABLE Country ADD KEY (Name(18));

mysql> CALL pref_index('Country', 'Name');
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| plength | @pdupe | @dupe |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|      18 |      0 |     0 |
+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+


mysql> SELECT COUNT(*) AS Total, COUNT(DISTINCT LEFT(Name,18)) AS
Diff, COUNT(*) ‐ COUNT(DISTINCT LEFT(Name,18)) AS Dupes FROM Country;
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+
|   239 |  239 |     0 |
We just saved 34 bytes per row!
+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐+

mysql> ALTER TABLE Country ADD KEY (Name(18));

Data stored in PK order
Primary key Index

Leaf nodes

Indexes point to PK instead of actual data

Load data in order
mysql> load data infile '/tmp/city_order.txt' into
table City_huge;
Records: 818027 Deleted: 0 Skipped: 0 Warnings: 0

mysql> load data infile '/tmp/city_rand.txt' into
table City_huge;
Query OK, 818027 rows affected (33 min 23.57 sec)
Records: 818027 Deleted: 0 Skipped: 0 Warnings: 0

+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

W hy?

mysql> set profiling=1;

+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

mysql> show profiles;
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| Query_ID | Duration   | Query                                         |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
|        1 | 0.00007600 | set profiling=1                               |
|        2 | 4.01965600 | select * from v_client_portfolio_high         |
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

mysql> select min(seq) seq,state,count(*) numb_ops,
    ‐> round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,
    ‐> round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu
    ‐> from information_schema.profiling
    ‐> where query_id = 2
    ‐> group by state
    ‐> order by seq;
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
| seq   | state                | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
|     0 | (initialization)     |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |
|     1 | Opening tables       |        1 | 0.00023 | 0.00023 | 0.00000 | 0.00000 |
|     2 | System lock          |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
|     3 | Table lock           |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
|     4 | checking permissions |        1 | 0.00010 | 0.00010 | 0.00000 | 0.00000 |
|     5 | optimizing           |        4 | 0.00004 | 0.00001 | 0.00000 | 0.00000 |
|     6 | statistics           |        4 | 0.00007 | 0.00002 | 0.00100 | 0.00025 |
|     7 | preparing            |        4 | 0.00005 | 0.00001 | 0.00000 | 0.00000 |
|     8 | Creating tmp table   |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
|     9 | executing            |    37352 | 0.16631 | 0.00000 | 0.05899 | 0.00000 |
|    10 | Copying to tmp table |        1 | 0.00006 | 0.00006 | 0.00000 | 0.00000 |
|    15 | Sending data         |    37353 | 3.85151 | 0.00010 | 3.72943 | 0.00010 |
| 74717 | Sorting result       |        1 | 0.00112 | 0.00112 | 0.00100 | 0.00100 |
| 74719 | removing tmp table   |        2 | 0.00003 | 0.00001 | 0.00000 | 0.00000 |
| 74721 | init                 |        1 | 0.00002 | 0.00002 | 0.00000 | 0.00000 |
| 74727 | end                  |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 74728 | query end            |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
| 74729 | freeing items        |        1 | 0.00002 | 0.00002 | 0.00000 | 0.00000 |
| 74730 | closing tables       |        2 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 74733 | logging slow query   |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+

But...

• Only in 5.0
• 5.1 implementation under review
• http://forge.mysql.com/wiki/
Testing_Show_Profiles_5_1

8
Usage statistics
Know what’s going on

SHOW STATUS
mysql> SHOW STATUS LIKE 'Com_select';
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Com_select | 13 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+

mysql> SELECT 1;
+‐‐‐+
| 1 |
+‐‐‐+
| 1 |
+‐‐‐+

+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Com_select | 14 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+

SHOW STATUS
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Com_select | 14 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+

mysql> FLUSH STATUS;

+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+
| Com_select | 0 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+

External tools

• mysqlsla
http://hackmysql.com/mysqlsla
mysqlsla parses, filters, analyzes and
sorts MySQL slow, general, binary and
microslow patched logs in order to
create a customizable report of the
queries and their meta-property values.

External tools
• mysqlidxchk
http://hackmysql.com/mysqlidxchk
mysqlidxchk (MySQL Index Checker)
checks MySQL databases/tables for
unused indexes. Given one or more
slow, general, or quot;rawquot; log files,
mysqlidxchk reports which indexes in
the database schema are not used by
the queries in the log files.

mysql> CREATE TABLE fk_relations (
    ‐>   key1 INT NOT NULL PRIMARY KEY,
    ‐>   key2 INT NOT NULL UNIQUE
    ‐> );

mysql> INSERT INTO fk_relations VALUES (1,1), (2,2);

mysql> SELECT * FROM fk_relations;
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
|    1 |    1 |
|    2 |    2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

10 things you might not know about MySQL

mysql> REPLACE INTO fk_relations VALUES (1,3);


+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 1 | 3 |
| 2 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+


???


+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 1 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 1 | 3 |
| 2 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

‐‐ Equals...

+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 1 | 3 |
| 2 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

‐‐ Equals...
‐‐ key1 is PK
mysql> DELETE FROM fk_relations WHERE key1 = 1;

+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 2 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

‐‐ Equals...
‐‐ key1 is PK
‐‐ key2 is Unique

+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 1 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

‐‐ Equals...
‐‐ key1 is PK
‐‐ key2 is Unique
mysql> INSERT INTO fk_relations VALUES (1,2);

That’s why

+‐‐‐‐‐‐+‐‐‐‐‐‐+
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 1 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

That’s why

+‐‐‐‐‐‐+‐‐‐‐‐‐+ + 1 insert
2 deletes
| key1 | key2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+
| 1 | 2 |
+‐‐‐‐‐‐+‐‐‐‐‐‐+

10
The good and bad
about temporary tables

Performance

• Be careful with complex queries
• Extremely slow on large datasets
• Use summary tables instead

Too complex
select d.dept_name, SUM(salary) from departments d
LEFT JOIN dept_emp de USING (dept_no) LEFT JOIN
salaries s USING (emp_no) where s.from_date >=
'2000‐01‐01' and s.to_date < '2001‐01‐01' group by
dept_no;

Instead, split in two
select d.dept_name, SUM(salary) from departments d
LEFT JOIN dept_emp de USING (dept_no) LEFT JOIN
salaries s USING (emp_no) where s.from_date >=
'2000‐01‐01' and s.to_date < '2001‐01‐01' group by
dept_no;

CREATE TEMPORARY TABLE salaries2000
SELECT * FROM salaries s
WHERE s.from_date >= '2000‐01‐01'
AND s.to_date < '2001‐01‐01';

SELECT d.dept_name, SUM(salary)
FROM departments d
LEFT JOIN dept_emp de USING (dept_no)
LEFT JOIN salaries2000 s USING (emp_no)
GROUP BY dept_no;

Big performance gain
(If indexes are in their place)

Big performance gain
(If indexes are in their place)

I’ve seen 10X!

one more thing...
I had to do it ;-)

Shit happens
mysql> select * from t;
+‐‐‐‐‐‐+
| id |
+‐‐‐‐‐‐+
| 2 |
+‐‐‐‐‐‐+

mysql> drop table t;
WTF?!?

ERROR 1051 (42S02): Unknown table 't'

+‐‐‐‐+
| id |
+‐‐‐‐+
|  1 |
+‐‐‐‐+
WTF?!?
mysql> create temporary table t(id int);
...again

Empty set (0.00 sec)

mysql> insert into t values (2);

+‐‐‐‐‐‐+
| id   |
+‐‐‐‐‐‐+
|    2 |
+‐‐‐‐‐‐+

• Temporary tables exist per session
• Be careful using connection pools
• They overlap current tables

There’s a reason for everything
+‐‐‐‐‐‐+
| id   |
+‐‐‐‐‐‐+
|    2 |
+‐‐‐‐‐‐+
temporary

+‐‐‐‐+
| id |
+‐‐‐‐+
|  1 |
+‐‐‐‐+
regular


ERROR 1051 (42S02): Unknown table 't'

What else?
• MySQL Proxy
https://launchpad.net/mysql-proxy
• MySQL Sandbox
https://launchpad.net/mysql-sandbox
• MySQL Random Query Generator
https://launchpad.net/randgen

Questions?
Probably out of time at this point, but it’s
the standard

�ݺ�ߣ

10 things you might not know about MySQL

More Related Content

10 things you might not know about MySQL