I just tried out EXPLAIN UPDATE in MySQL 5.6 and found unexpected results. This query has no usable index: EXPLAIN UPDATE ... WHERE col1 = 9 AND col2 = 'something'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: index possible_keys: NULL key: PRIMARY key_len: 55 ref: NULL rows: 51 Extra: Using where The EXPLAIN output makes it seem like a perfectly fine query, but it’s a full table scan.

#DBHangOps 11/27/13 -- Ramping up new DBAs!

Please check out the recording below!

Hello everybody!

Join in #DBHangOps this Wednesday, November 27th, 2013 at 12:00pm pacific (19:00 GMT), to participate in the discussion about:

• How do you ramp up new DBAs?

  • How do you ramp up a non-DBA to being a DBA?

Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Wednesday!

See all of you on Wednesday!

Show notes

Ramping up new DBAs

Literature/Media

• Blogs, Videos, and Podcasts

  • …

Today I wanted to look at the relative merits of different optimization paths that can be taken on a Greenfield project. That is to say, one that has no constraints imposed on it by previous decisions made, and has received little to no optimization work to date.

Specifically, the two optimizations I wanted to compare are optimizing mysql versus caching. I should point out in advance that these optimizations are really orthogonal. The only thing that ties you to doing one versus the other is that they both consume the resource developer time.

Optimizing MySQL

This optimization typically starts by taking a look at the queries that are being sent to MySQL and running …

Kauli – Open Source Grown Up

Kuali is proof that the Open Source model works for more than nerds and geeks. Kuali is a movement where colleges and universities pool resources to develop the software they need to run their institutions. The parties involved provide money and bodies to work on the various projects. And if an school needs a feature in a hurry, they provide money or bodies to get the work done. This is a highly collaborative effort spans the globe.

Many of these same schools found themselves locked into very expensive software that had to be extensively tailored to meet their needs. Each upgrade was an expensive and time consuming process in an era of shrinking staffs and budgets. Regular upgrades were needed to support changes in regulations or latent needs. The cost of upgrades and customization was taking too much of scarce funds. This drove them into an open …

Currently you cannot specify the current time as a default value for a DATETIME column. It works for TIMESTAMP but not for DATETIME,and even with TIMESTAMP this only work for the one timestamp column in the table, if you have more than 1 TIMESTAMP column you have to specify a default for all but the first.

This is fixed in MySQL 5.6 and MariaDB 10 which allows you to use a construct like this:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());

So what can you do if you are on MariaDB? Well, you can use a trigger to achieve a similar result:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());
delimiter //
mysql> CREATE TRIGGER td1_ins BEFORE INSERT ON td1 FOR EACH ROW
BEGIN
  IF new.c2 IS NULL THEN
    …

This blog talks about MySQL Enterprise Backup integration with Workbench and how the Workbench UI can be used to configure and operate MEB.

Or I could place in the title – “Yes, we done it!”

After reaching 500K QPS in Read-Only on SQL queries, it was natural to expect a much higher performance level from InnoDB Memcached Plugin which is by-passing all SQL related layers.. However the story is not simple, and yet far from finished

While for today we have already our first “preview” results showing that we’re able to reach over 1,000,000 Query/sec level with the latest MySQL 5.7 code:

click here to read the full article..

Join 15,000 others and follow Sean Hull on twitter @hullsean. 1. MySQL does not compete with Oracle It’s a myth that MySQL somehow poses a threat to Oracle. Oracle’s customers tend to be large enterprises running apps like e-business suite. These are certified to run on Oracle, and further they sit close to finance. MySQL […]

MySQL Cluster have all the ingridients to be designed as a very scalable and extremely efficient key lookup engine for the Cloud. As we have shown in earlier entries of my blog we've been able to scale MySQL Cluster 7.2 to handle 72 million key lookups per second or 4.3 billion key lookups per minute. This benchmark was actually limited by a limitation of the NDB API nodes to handle more than about 300k lookups per second and so with a maximum of 255 nodes we got to around 72 million per second in total. However in MySQL Cluster 7.3 we have removed this limitation, in addition we have also enabled scaling to even bigger data nodes, so it should be possible now to reach even higher numbers.

The aim of this blog is however not to give any new benchmark results, rather it is providing details about how the benchmark program works and how this benchmark program architecture can be used to design an efficient scalable key lookup data …

Do you have basic computer literacy skills? Then you can take the first step to using the MySQL Server by taking the MySQL for Beginners course.

Why get started on MySQL? Well, with MySQL, you can power the most demanding Web, E-commerce, and Online Transaction Processing applications.

In the 4-day MySQL for Beginners course, you develop an understanding of relational databases and how to design a robust and efficient database. You will install and configure the MySQL server and clients. You also get an introduction to advanced MySQL tools and features. You can take this live, instructor-led course as a:

• Live-virtual event: Take this course from your own desk, no travel required. Choose from a selection of events on the schedule to suit …