You searched for alter table engine type myisam

MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency

May 22, 2013 By Justin Swanhart Leave a Comment

This blog post is part two in what is now a continuing series on the Star Schema Benchmark. In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using only the InnoDB storage engine. In my testing I discovered that innodb_old_blocks_time had an effect on performance of the benchmark. There was [...]

Filed Under: Benchmarks, MySQL Tagged With: InnoDB, Justin Swanhart, Low Concurrency, MyISAM, MySQL, SSB, star schema, Star Schema Benchmark

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

MySQL optimizer: ANALYZE TABLE and Waiting for table flush

February 27, 2013 By Miguel Angel Nieto 3 Comments

The MySQL optimizer makes the decision of what execution plan to use based on the information provided by the storage engines. That information is not accurate in some engines like InnoDB and they are based in statistics calculations therefore sometimes some tune is needed. In InnoDB these statistics are calculated automatically, check the following blog [...]

Filed Under: MySQL Tagged With: ANALYZE TABLE, InnoDB, Miguel Angel Nieto, MySQL Optimizer, table flush

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

MySQL Upgrade Webinar Questions Followup

June 28, 2012 By Peter Zaitsev 7 Comments

I did a Webinar about MySQL Upgrade – Best Practices Yesterday and there were some questions we could not answer during Webinar, following Jay’s Lead I decided to post them as a Blog Post. Q: Can you go directly MySQL 5.0 to 5.5 for MyISAM tables? MyISAM have not been getting any significant development since [...]

Filed Under: Insight for DBAs, MySQL

Ultimate MySQL variable and status reference list

January 8, 2011 By Baron Schwartz 4 Comments

I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on variables, or look for content in the Percona documentation or forums. So I present to you what is now my newest Firefox toolbar bookmark: an option and [...]

Filed Under: Insight for DBAs, MySQL

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

Converting Character Sets

March 17, 2009 By Ryan Lowe 21 Comments

The web is going the way of utf8.Â Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.Â Googling for “mysql convert charset to utf8″ results in a plethora of sites, [...]

Filed Under: Insight for Developers Tagged With: Community, Tips, Tools

Should you move from MyISAM to Innodb ?

January 12, 2009 By Peter Zaitsev 59 Comments

There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ? I generally prefer to see Innodb as the main storage engine because it makes life much [...]

Filed Under: Insight for DBAs Tagged With: InnoDB, MyISAM, Production

High-Performance Click Analysis with MySQL

December 22, 2008 By Baron Schwartz 13 Comments

We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.Â The first thing these have in common is that they’re generally some kind of loggable event. The next characteristic of a lot of these systems (real or planned) is [...]

Filed Under: Insight for Developers Tagged With: Ideas, Optimizer, Replication, Tips, XtraDB

MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency

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

MySQL optimizer: ANALYZE TABLE and Waiting for table flush

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

MySQL Upgrade Webinar Questions Followup

Ultimate MySQL variable and status reference list

How (not) to find unused indexes

Converting Character Sets

Should you move from MyISAM to Innodb ?

High-Performance Click Analysis with MySQL

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA