Yesterday we had our third MySQL User Group meeting in Trondheim with close to 40 participants, a mix of database administrators, application developers, database developers, and even university professors.

We had all gathered to listen to Truls Bergskaug from Basefarm a leading hosting provider for mission critical business applications. Truls Bergskaug is an experienced database administrator who deals with around 500 MySQL instances in production, all MySQL versions starting from 4.1 up to 5.6. Truls presented the hosting environment, typical set up scenarios, issues related to database upgrades, monitoring, and management. As an example Truls outlined Basefarm’s internally made rpm …

On Wednesday I gave a presentation on “How to Avoid Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:

Q: Does a JOIN operation between two tables always produce an “access table” on the rows of the first table of the join, or it is possible to define an index (COVERING INDEX) to avoid this access to the first table?

Yes, if your …

To  truncate table we use TRUNCATE command As follows.

TRUNCATE  TABLE  <table_name>;

What if  you  want to  TRUNCATE  ALL tables from different   databases in mysql ,It is possible using  metadata information of  database. INFORMATION_SCHEMA database created while installing  mysql ,which  contains  all information like stores information about all the other databases that the MySQL server maintains.

Here  Is the Query  which  gives output  script with  truncate commands   for all table.You  don’t  have to  write truncate command  one by  one  for each  table.

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1_name,db2_name);

Use Query Result to truncate tables

I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.

Isolating the problem

As always with a slow query, finding the execution plan with EXPLAIN is the 1st step to understand where time is spent. Here the plan was very good with almost all joins using the primary key or a unique key, but perhaps the most interesting part was that EXPLAIN was very slow as well. This indicates that the optimizer takes a lot of time finding the optimal execution plan. To double check, we can run SHOW PROFILE:

mysql> set @@profiling = 1;
mysql> SELECT …

March 6, 2014 By Severalnines

Check Out Our Latest Resources for MySQL, MariaDB & MongoDB Clusters

Here is a summary of resources & tools that we’ve made available to you in the past weeks. If you have any questions on these, feel free to contact us!

ClusterControl 1.2.5 released

We are pleased to announce the release of ClusterControl 1.2.5, which now supports MySQL 5.6 and Global Transaction IDs to enable cross-datacenter and cloud replication over high latency networks. Galera users are now able to assign nodes to their respective datacenter. Other features include User Defined Alerts and agent-less monitoring.

…

I want to teach you the difference between an inner and an outer join. We first need to think about what a join is. Simply, it’s when you combine two tables to make a new one. You’re not physically creating a new table when you join them together, but for the purposes of the query, you are creating a new virtual table. Every row now has the columns from both tables. So if TableA has columns Col1 and Col2 and TableB has columns Col3 and Col4, when you join these two tables, you’ll get Col1, Col2, Col3, and Col4. Just as with any query, you have the option of including all columns or excluding some, as well as filtering out rows.

Inner join. A join is combining the rows from two tables. An inner join attempts to match up the two tables based on the criteria you specify in the query, and only returns the rows that match. If a row from the first table in the join matches two rows in the second table, then two rows will be …

Dear MySQL users,

We are proud to announce that we have made the open source version of our Connector/NET 6.8.3 is available as official MySQL Nuget packages.

Currently we have 3 nuget packages which contain:

• MySQL Connector/NET Core
• MySQL Connector/NET for Entity Framework 6
• MySQL Connector/NET Web providers

I’ve been a little behind in recent blogging efforts, and realized that in less than a month we’ll be back at Percona Live: MySQL Conference and Expo 2014, aka PLMCE. Last year’s PLMCE was my first, as well as the event where Tokutek announced the open sourcing of TokuDB.

It’s hard to believe that a year has gone by, but the customer adoption in both enterprise and community users has been awesome. TokuDB is available from our website for both MySQL and MariaDB, and is also available directly from MariaDB and …

Google senior systems engineer Jeremy Cole is once again teaming with LinkedIn senior software engineer Davi Arnaut for two InnoDB-focused sessions at the upcoming Percona Live MySQL Conference and Expo 2014 this April 1-4 in Santa Clara, California.

The duo will present “InnoDB: A journey to the core II” on April 2 and “InnoDB: A  hands-on exploration of on-disk storage with innodb-ruby” on April 4. Based on Jeremy’s InnoDB blog series, both sessions will be a continuation of …

The MySQL developer tools team is pleased to announce 6.1 Beta 2 - the start of the public beta testing phase of MySQL Workbench 6.1

MySQL Workbench 6.1 is the upcoming major update for the official MySQL graphical development tool.
Introducing over 30 new features, this version has many significant enhancement focusing on real-time performance assessment and analysis from the SQL statement level to server internals and file IO. You'll see this in additions to the SQL Editor as well as new dashboard visualization and reporting that takes advantage of MySQL Server 5.6 and 5.7 Performance Schema, and enhancements to the MySQL Explain Plans.

Additionally Workbench 6.1 this leveraging work from various teammates in MySQL Engineering in introducing a schema called "SYS" that provides simplified views on Performance Schema, Information Schema, and other areas.
Special thanks to the server optimizer team, …