Archive for the ‘ MySql ’ Category

Add Row Numbers to MySQL Results

Sometimes when querying MySQL I like to return a new column containing an ordered numerical sequence for each row in the result set. Here’s an explanation of how to do it.

Screen Shot 2014-06-13 at 08.56.52

I suppose this isn’t really needed in many, if not most, queries, but sometimes it’s just what I want. As an example, I have a users table that contains three columns: id, first_name and last name. The following query will retrieve a result set from this table where the first_name column is equal to John.

SELECT uuid
FROM users
WHERE email LIKE '%j%';

We can easily modify this query to insert an additional column to the front of the result set containing a sequential number for each record. Try this:

SELECT @ROW := @ROW + 1 AS ROW, uuid
FROM users, (SELECT @ROW := 0) r
WHERE email LIKE '%j%';

Voila! And now, how does it work? Let’s start with the FROM clause. We are specifying two sources of data. The first is the users table itself and the second is a subquery that sets a session variable called @ROW and initializes its value to zero. Being a derived table, we must specify an alias, so I used r.

In the SELECT clause, we are incrementing the value of the variable @ROW by one and selecting its value followed by the first_name column from our users table. Since @ROW started life with a value of zero, the first row of the returned result set will be one followed by two, three etc. If we wanted to start at a different value we could’ve set that value in the FROM clause.

And finally, the WHERE clause constrains our results to those whose first name is John. That was pretty easy and quite useful I think.

via Add Row Numbers to MySQL Results | DigitalWindFire.

MySQL :: White Papers – MySQL Cluster

MySQL :: White Papers – MySQL Cluster.

MySQL

  • Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine While effective for smaller, “read-mostly” deployments, as web services evolve users of the MySQL MEMORY storage engine can find themselves confronting issues in scalability, concurrency and availability.
    MEMORY is a basic MySQL storage engine designed for in-memory operations. MySQL Cluster, which itself can be implemented as a MySQL storage engine, can perform all the same in-memory operations, and is faster, more reliable and uses less RAM for data, even on a single node.
    In performance testing, MySQL Cluster was able to deliver 30x higher throughput with 1/3rd the latency of the MEMORY storage engine on just a single node.
    MySQL Cluster can be configured and run in the same way as MEMORY (ie on a single host with no replication and no persistence), but then any of these high availability and scalability attributes can be added in any combination as the workload evolves. The database can be scaled across multiple nodes without implementing sharding (partitioning) in the application, significantly reducing both cost and complexity.
    In this whitepaper, comparisons between the MEMORY storage engine and MySQL Cluster are presented, including a performance study of the two technologies, before then providing a step-by-step guide on how existing Memory storage engine users can upgrade to MySQL Cluster.
    Read more »
  • Using MySQL Cluster 7.1 for Web and eCommerce Applications As your on-line services expand, so too can the demands on your web infrastructure. Challenges include:
    • Growing revenue streams and customer loyalty from your web and eCommerce applications
    • The need for continuous application availability and real time responsiveness to ensure a quality customer experience
    • Constant pace of innovation to quickly deliver compelling new services to market

    MySQL Cluster is a proven key component of web infrastructure that can help you cost-effectively deploy online applications to generate new revenue streams and build vibrant user communities.
    Read the white paper to learn how deploying MySQL Cluster with your web and eCommerce services enables you to grow revenue and enhance customer loyalty.

    Read more »

  • Building an Open Source, Carrier Grade Platform for Data Management with MySQL Cluster Whether Service Providers are looking to deploy new Web/Telco 2.0 applications to mobile internet users or consolidating subscriber data within the network to support greater service personalization and targeted advertising, the database plays a key enabling role.
    With the rapid shift from closed, expensive and proprietary technology, MySQL has grown to become the world’s most popular open source database. In this paper we explore how an open source carrier grade platform architecture is able to cost-effectively meet the communication industry’s high availability, scalability and real-time performance requirements.
    Read more »
  • MySQL Cluster 7.1: Architecture and New Features Whitepaper MySQL Cluster has been widely adopted for a range of telecommunications, web and enterprise workloads demanding carrier-grade availability with high transaction throughput and low latency. In this paper we will explore new features introduced in MySQL Cluster 7.1 to meet an ever expanding and more demanding set of mission-critical data management requirements.Read more »
  • MySQL Cluster Evaluation Guide – Designing, Evaluating and Benchmarking MySQL Cluster In this whitepaper learn the fundamentals of how to design and select the proper components for a successful MySQL Cluster evaluation. We explore hardware, networking and software requirements, and work through basic functional testing and evaluation best-practices.Read more »
  • Guide to Optimizing Performance of the MySQL Cluster Database This guide explores how to tune and optimize the MySQL Cluster database to handle diverse workload requirements. It discusses data access patterns and how to build distribution awareness into applications, before exploring schema and query optimization, tuning of parameters and how to get the best out of the latest innovations in hardware design.
    The Guide concludes with recent performance benchmarks conducted with the MySQL Cluster database, an overview of how MySQL Cluster can be integrated with other MySQL storage engines, before summarizing additional resources that will enable you to optimize MySQL Cluster performance with your applications.
    Read more »

The innotop MySQL and InnoDB monitor at Xaprb

The innotop MySQL and InnoDB monitor at Xaprb.

MySQL and InnoDB expose lots of information about their internals, but it’s hard to gather it all into one place and make sense of it. I’ve written a tool to do that, and you are free to download and use it. This article introduces innotop, a powerful text-mode MySQL and InnoDB monitoring tool. It has lots of features, is fast and configurable, and it’s easy to use.

Note: I’m now making it a priority to make innotop very stable and robust. If innotop crashes, please help me fix it. Please read this article about how to submit a bug report for innotop. Thanks so much for helping me build a comprehensive test suite!

Why another text-mode monitoring tool?

Because the available ones aren’t good enough. I didn’t want to duplicate anyone else’s effort, but the other similar tools (mytop, mtop) haven’t been actively maintained in years, don’t work correctly with newer versions of MySQL, and frankly only offer a small fraction of the features I’ve built into innotop.

I said innotop has lots of features and is really flexible. Here’s just a small sampling to give an idea:

  1. 11 different modes to show lots of information in very useful ways
  2. completely configurable — for example, you can choose which columns to see in every tabular display, in what order, what column to sort by, what direction to sort, add perl regex filters to any column, and on and on.
  3. offers features no other tool gives you, period. What was the date, time, and query (plus lots of other info!) of each transaction involved in the last deadlock? No other tool can give you that. And that’s just one of its dozens of unique features.
  4. it parses and displays InnoDB information, which is packed full of information. No other tool even tries to do this. Parsing the output of the InnoDB monitor is not for the faint of heart!
  5. its interactive help and configuration make it very easy to learn and highly productive to use. What keys are mapped in the current mode? Press ‘?’ and find out. What configuration variables can you change? Press a key and find out. Every function has context-sensitive help to keep you productive.

Perhaps its most powerful and ambitious feature is the way it presents InnoDB internals. MySQL is sorely lacking in instrumentation and analysis compared to other major relational database systems (for example, Microsoft SQL Server), and it is just no fun searching through the output of the InnoDB monitors to glean bits of information from it. In my opinion, this feature alone is a major step forward to looking at what MySQL is doing internally. The information has always been there, but until now it’s been hard for DBAs to use.

innotop is designed to do whatever you need it to do, and if it doesn’t, you can let me know how to improve it. I am continually using and improving this tool, in response to my own needs and those of other people using it.