You searched for searching mysql tables'

MySQL Backup tools used by Percona Remote DBA for MySQL

March 13, 2013 By Ryan Huddleston 8 Comments

As part of Percona Remote DBA for MySQL service we recognize that reliable backups are one of the most important things we can bring to the table. In my experience handling emergencies, the single worst thing that can happen is finding out you don’t have backups available when some sort of data loss or catastrophic [...]

Filed Under: Cloud and NoSQL, Insight for DBAs, Insight for Developers, MySQL Tagged With: Amazon S3, data loss, database outage, database performance, MySQL, MySQL Backup tools, Percona Remote DBA, Percona XtraBackup, Rryan Huddleston

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By Ernie Souhrada 7 Comments

This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: Ernie Souhrada, FTS, FullText Search, InnoDB, MyISAM, MySQL 5.6

InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By Ernie Souhrada 1 Comment

I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: Ernie Souhrada, FTS, FullText Search, InnoDB, MyISAM, MySQL 5.6

Visualization tools for pt-query-digest tables

August 31, 2012 By Roman Vynar 6 Comments

When you process MySQL slow query logs using pt-query-digest you can store samples of each query into query_review table and historical values for review trend analysis into query_review_history table. But it could be difficult to easily browse those tables without a good GUI tool. For the visual browsing of tables created by pt-query-digest you may [...]

Filed Under: Insight for DBAs, MySQL Tagged With: pt-query-digest

Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs

February 23, 2012 By Jervin Real 3 Comments

LVM snapshots is one powerful way of taking a consistent backup of your MySQL databases – but did you know that you can now restore directly from a snapshot (and binary logs for point in time recovery) in case of that ‘Oops’ moment? Let me show you quickly how. This howto assumes that you already [...]

Filed Under: Insight for DBAs

Moving from MyISAM to Innodb or XtraDB. Basics

November 22, 2010 By Peter Zaitsev 2 Comments

I do not know if it is because we’re hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb. Webinar will cover the process in a lot more details though I would like to go over basics in [...]

Filed Under: Events and Announcements, MySQL Tagged With: InnoDB, MyISAM, XtraDB

Sharing an auto_increment value across multiple MySQL tables

October 4, 2010 By Morgan Tocker 9 Comments

The title is SEO bait – you can’t do it. We’ve seen a few recurring patterns trying to achieve similar – and I thought I would share with you my favorite two: Option #1: Use a table to insert into, and grab the insert_id:

CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb;
# each insert does one operations to get the value:
INSERT INTO option1 VALUES (NULL);
# $connection->insert_id();

CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb;

# each insert does one operations to get the value:

INSERT INTO option1 VALUES (NULL);

# $connection->insert_id();

Option #2: Use a table with one just row:

CREATE TABLE option2 (id int not null primary key) engine=innodb;
INSERT INTO option2 VALUES (1); # start from 1
# each insert does two operations to get the value:
UPDATE option2 SET id=@id:=id+1;
SELECT @id;

CREATE TABLE option2 (id int not null primary key) engine=innodb;

INSERT INTO option2 VALUES (1); # start from 1

# each insert does two operations to get the value:

UPDATE option2 SET id=@id:=id+1;

SELECT @id;

Filed Under: Benchmarks, Insight for Developers, MySQL Tagged With: autoincrement, auto_increment, MySQL, Tips

When should you store serialized objects in the database?

January 21, 2010 By Morgan Tocker 47 Comments

A while back Friendfeed posted a blog post explaining how they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then, the technique has gotten more popular with Ruby gems now around to do this for you automatically.

Filed Under: Insight for Developers, MySQL Tagged With: eblob, friendly, MySQL, Tips

How (not) to find unused indexes

October 16, 2009 By Morgan Tocker 2 Comments

I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.Â This method is flawed – here’s the first reason why:

CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`status` enum('archived','active') DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;
mysql> SELECT count(*), status FROM sales GROUP by status;
+----------+---------+
| count(*) | statusÂ  |
+----------+---------+
|Â Â Â  65536 | archived |
|Â Â Â Â Â  154 | activeÂ  |
+----------+---------+
2 rows in set (0.17 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | keyÂ Â Â  | key_len | refÂ Â  | rows | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | refÂ  | statusÂ Â Â Â Â Â Â  | status | 2Â Â Â Â Â Â  | const |Â  196 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | keyÂ  | key_len | refÂ  | rowsÂ  | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | ALLÂ  | statusÂ Â Â Â Â Â Â  | NULL | NULLÂ Â Â  | NULL | 65690 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

CREATE TABLE `sales` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`customer_id` int(11) DEFAULT NULL,

`status` enum('archived','active') DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `status` (`status`)

) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;

mysql> SELECT count(*), status FROM sales GROUP by status;

+----------+---------+

| count(*) | statusÂ |

+----------+---------+

|Â Â Â 65536 | archived |

|Â Â Â Â Â 154 | activeÂ |

+----------+---------+

2 rows in set (0.17 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)

The cardinality of status index is woeful, but provided that the application [...]

Filed Under: Insight for DBAs, MySQL Tagged With: explain, indexing, MySQL, Tips

How Percona does a MySQL Performance Audit

November 24, 2008 By Baron Schwartz 17 Comments

Our customers or prospective customers often ask us how we do a performance audit (it’s our most popular service). I thought I should write a blog post that will both answer their question, so I can just reply “read all about it at this URL” and share our methodology with readers a little bit. This [...]

Filed Under: Insight for Developers Tagged With: Tuning

MySQL Backup tools used by Percona Remote DBA for MySQL

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

InnoDB Full-text Search in MySQL 5.6 (part 1)

Visualization tools for pt-query-digest tables

Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs

Moving from MyISAM to Innodb or XtraDB. Basics

Sharing an auto_increment value across multiple MySQL tables

When should you store serialized objects in the database?

How (not) to find unused indexes

How Percona does a MySQL Performance Audit

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA