Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 1030 10 Older Entries

Displaying posts with tag: sql (reset)

SQL mini hack of the day, inverted IN clause
+0 Vote Up -0Vote Down

We are used to issue queries with an IN clause of the form:

... where state in ('started', 'completed', 'failed') ...

However I've had a few cases where I used an inverted format. Here's one use case followed by an inverted IN clause.

Dynamic query building

Say we have this function:

GetLaggingSlaves(clusterName string)

Which, based on whether given clusterName is empty or not, would return list of all lagging slaves, or only those in the given cluster, respectively:

SELECT hostname FROM database_instance WHERE slave_lag_seconds > …
  [Read more...]
1 Million SQL Queries per second: GA MariaDB 10.1 on POWER8
+0 Vote Up -0Vote Down

A couple of days ago, MariaDB announced that MariaDB 10.1 is stable GA – around 19 months since the GA of MariaDB 10.0. With MariaDB 10.1 comes some important scalabiity improvements, especially for POWER8 systems. On POWER, we’re a bit unique in that we’re on the higher end of CPUs, have many cores, and up to 8 threads per core (selectable at …

  [Read more...]
Leader election using MySQL
+0 Vote Up -0Vote Down

Being a stateful, centralized datastore, MySQL can serve in negotiating leadership: a mechanism to elect a single service out of multiple services; moreover, a mechanism to promote a new leader should the existing leader cease to function.

What of Zookeeper?

Zookeeper makes for an excellent leader election mechanism. This is one of the most recognized uses for Zookeeper. It has HA via multiple nodes & quorum,  ephemeral nodes, all you need. To achieve similar benefits with MySQL you'd need to use Galera or NDB Cluster; so why not use Zk?

The use case at hand is

  [Read more...]
Create MySQL Index
+1 Vote Up -0Vote Down

Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT, UPDATE, REPLACE INTO, and DELETE statements. Indexes are also called fast access paths.

In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT, UPDATE, REPLACE INTO, and …

  [Read more...]
MariaDB/MySQL: ON REPLACE triggers
+0 Vote Up -0Vote Down

There are several reasons why, generally, in MySQL/MariaDB one should not use REPLACE as a shortcut for SELECT + (UPDATE or INSERT). One of these reasons is the way REPLACE fires triggers. Of course it does not fire UPDATE triggers, and there is no such thing as a REPLACE trigger; DELETE and INSERT triggers are fired instead. What is not obvious is the order in which REPLACE activates triggers (UPDATE: this beavior was undocumented; I documented it …

  [Read more...]
Importance of MySQL cache
+0 Vote Up -0Vote Down

My test environment is:
Ubuntu 14.04 Trusty Tahr
MySQL Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)

MySQL uses sql cache to store results of queries that have been executed so that when the same query is executed again it retrieves the result data set from the cache instead of getting it again from db. So it is faster data access.

It is by default enabled in MySQL.

This is interesting since there is one question we ought to ask here whether we should use it or disable it or just leave it as it is who cares :).

Ok, moving forward today's session goals are:

  [Read more...]
+0 Vote Up -0Vote Down

Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.

I gave my students this ORDER BY clause as an example:

    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN 2
    WHEN filter = 'Total' THEN 3

It raises the following error in MySQL for students:

  [Read more...]
What’s the latest with Hadoop
+0 Vote Up -0Vote Down

The Big Data explosion in recent years has created a vast number of new technologies in the area of data processing, storage, and management. One of the biggest names to appear on the scene is Hadoop. In case you need a quick review, Hadoop is a Big Data storage system that takes in large amounts of data from servers and breaks it into smaller, manageable chunks. The technology is complex but at a high level the Hadoop ecosystem essentially takes a “divide and conquer” approach to processing Big Data instead of processing data in tables, as in a relational database like …

  [Read more...]
Bash Arrays & Oracle
+0 Vote Up -0Vote Down

Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.

If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays, if-statements, and for-loops. In this blog post I only cover how to implement a bash

  [Read more...]
Bash Arrays & MySQL
+0 Vote Up -0Vote Down

Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.

  [Read more...]
Showing entries 1 to 10 of 1030 10 Older Entries

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.