Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 58 Next 28 Older Entries

Displaying posts with tag: indexing (reset)

Why Unique Indexes are Bad
+1 Vote Up -0Vote Down

Before creating a unique index in TokuMX or TokuDB, ask yourself, “does my application really depend on the database enforcing uniqueness of this key?” If the answer is ANYTHING other than yes, do not declare the index to be unique. Why? Because unique indexes may kill your write performance. In this post, I’ll explain why.

Unique indexes are a strange beast: they have no impact on standard databases that use B-Trees, such as MongoDB and MySQL, but may be horribly painful for databases that use write optimized data structures, like TokuMX’s Fractal Tree(R) indexes. How? They

  [Read more...]
Purging old rows with QueryScript: three use cases
+0 Vote Up -0Vote Down

Problem: you need to purge old rows from a table. This may be your weekly/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy.

You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server side scripting with QueryScript, offering a very simple syntax with no external scripting, dependencies and command line options.

I wish to present three cases of row deletion, with three different solutions. In all cases we assume some TIMESTAMP column

  [Read more...]
532x Multikey Index Insertion Performance Increase for MongoDB with Fractal Tree Indexes
+1 Vote Up -1Vote Down

In my three previous MongoDB blogs I wrote about our implementation of Fractal Tree(R) indexes on MongoDB, showing a 10x insertion performance increase, a 268x query performance increase, and a comparison of covered indexes and clustered indexes. These benchmarks show the difference that rich and efficient indexing can make to your MongoDB workload.

Given the high performance of Fractal Tree Indexes, we’ve created a new

  [Read more...]
My Talk Next Week at HighLoad++
+2 Vote Up -0Vote Down

Next week I’ll be visiting Moscow to talk at Highload++. The conference will take place during Monday 22nd and Tuesday 23rd at the Radisson hotel. I will be giving my personal version of an indexing talk that my colleagues have given in meetups and conferences in the US.

Highload++ conference is targeted to address the issues of complex high traffic web properties. Most of these sites depend on databases to deliver their content, record the traffic and report the application activities in real time. As I learned early in my career at MySQL, the database schema and in particular the indexing strategy, are critical to achieve the highest possible performance out of the

  [Read more...]
Looking for MongoDB users to test Fractal Tree Indexing
+1 Vote Up -0Vote Down

In my three previous blogs I wrote about our implementation of Fractal Tree Indexes on MongoDB, showing a 10x insertion performance increase, a 268x query performance increase, and a comparison of covered indexes and clustered indexes. The benchmarks show the difference that rich and efficient indexing can make to your MongoDB workload.

It’s one thing for us to benchmark MongoDB + TokuDB and another to measure real world performance. If you are looking for a way to improve the performance or

  [Read more...]
MongoDB Index Shootout: Covered Indexes vs. Clustered Fractal Tree Indexes
+1 Vote Up -0Vote Down

In my two previous blogs I wrote about our implementation of Fractal Tree Indexes on MongoDB, showing a 10x insertion performance increase and a 268x query performance increase. MongoDB’s covered indexes can provide some performance benefits over a regular MongoDB index, as they reduce the amount of IO required to satisfy certain queries.  In essence, when all of the fields you are requesting are present in the index key, then MongoDB does not have to go back to the main storage heap to

  [Read more...]
How common_schema split()s tables - internals
+1 Vote Up -0Vote Down

This post exposes some of the internals, and the SQL behind QueryScript's split. common_schema/QueryScript 1.1 introduces the split statement, which auto-breaks a "large" query (one which operates on large tables as a whole or without keys) into smaller queries, and executes them in sequence.

This makes for easier transactions, less locks held, potentially (depending on the user) more idle time released back to the database. split has similar concepts to oak-chunk-update and

  [Read more...]
Table split(...) for the masses
+1 Vote Up -0Vote Down

(pun intended)

common_schema's new split statement (see release announcement) auto-splits complex queries over large tables into smaller ones: instead of issuing one huge query, split breaks one's query into smaller queries, each working on a different set of rows (a chunk).

Thus, it is possible to avoid holding locks for long times, allowing for smaller transactions. It also makes for breathing space for the RDBMS, at times boosting operation speed, and at times prolonging operation speed at will.

In this post I show how split exposes itself to the user, should the user wish so.

split can

  [Read more...]
Webinar: Understanding Indexing
+1 Vote Up -0Vote Down

Three rules on making indexes around queries to provide good performance

Application performance often depends on how fast a query can respond and query performance almost always depends on good indexing. So one of the quickest and least expensive ways to increase application performance is to optimize the indexes. This talk presents three simple and effective rules on how to construct indexes around queries that result in good performance.


Time: 2PM EDT / 11AM PDT

This webinar is a general discussion applicable to all databases using indexes and is not specific to any particular MySQL® storage engine


  [Read more...]
Fractal Tree Indexing and Filesystems – HotStorage 2012
+2 Vote Up -0Vote Down

Modern file systems are well equipped to deal with large writes. One area that remains challenging however is to efficiently write out “microdata”, such as metadata and small portions of large files, while showing good I/O utilization when the data is read back. This challenge is evident with mount options like “noatime” which disables updating file access time on reads. This kind of solution avoids the problem altogether. Another approach, delayed allocation, is meant to coalesce small writes in memory as long as possible before writing it out to disk. Filesystems like ext4 and Btrfs use delayed allocation to make a best-effort at reducing fragmentation and random I/O.

Isn’t there a way to fundamentally solve filesystem fragmentation and random I/O?

This week, I’ll be speaking at

  [Read more...]
Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
+0 Vote Up -0Vote Down
I have written a second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5
FictionPress Selects TokuDB for Consistent Performance and Fast Disaster Recovery
+1 Vote Up -0Vote Down

FictionPress

Issues addressed:

  • Support complex and efficient indexes at 100+ million rows.
  • Predicable and consistent performance regardless of data size growth.
  • Fast recovery.

Ensuring Predictable Performance at Scale

The Company:  FictionPress operates both FictionPress.com and FanFiction.net and is home to over 6 million works of fiction, with millions of writers/readers

  [Read more...]
Slides of my talk on B+Tree Indexes and InnoDB
+2 Vote Up -0Vote Down
The slides of my talk on B+Tree Indexes and InnoDB are now available for download. This slide was presented during Percona Live London 2011. You can download the slides from here. There are many other interesting and informative talks that were presented during Percona Live London 2011, and I think you should definitely check them out, if you haven't. They are available here.
Book Review – Effective MySQL
+3 Vote Up -0Vote Down

Read the original article at Book Review – Effective MySQL

Effective MySQL: Optimizing SQL Statements by Ronald Bradford No Nonsense, Readable, Practical, and Compact I like that this book is small; 150 pages means you can carry it easily.  It’s also very no nonsense.  It does not dig too deeply into theory unless it directly relates to your day-to-day needs.  And those needs probably cluster [...]

For more articles like these go to iHeavy, Inc +1-212-533-6828

Understanding Indexing – NY Effective MySQL Meetup
+1 Vote Up -0Vote Down

At next week’s NY Effective MySQL Meetup, I will give a talk: “Understanding Indexing: Three rules on making indexes around queries to provide good performance.” The meetup is 7 pm Tuesday, October 11th, and will be held at Hive at 55 (55 Broad Street, New York, NY). Thanks to host Ronald Bradford for the invitation.

Application performance often depends on how fast a query can respond and query performance almost always depends on good indexing. So one of the quickest and least expensive ways to increase application performance is to optimize the indexes. This talk presents

  [Read more...]
Write Optimization: Myths, Comparison, Clarifications, Part 2
+0 Vote Up -0Vote Down

In my last post, we talked about the read/write tradeoff of indexing data structures, and some ways that people augment B-trees in order to get better write performance. We also talked about the significant drawbacks of each method, and I promised to show some more fundamental approaches.

We had two “workload-based” techniques: inserting in sequential order, and using fewer indexes, and two “data structure-based” techniques: a write buffer, and OLAP. Remember, the most common thing people do when faced with an insertion bottleneck is to use fewer indexes, and this kills query performance. So keep in mind that all our work on write-optimization is really work for read-optimization, in that write-optimized

  [Read more...]
Are You Forcing MySQL to Do Twice as Many JOINs as Necessary?
+2 Vote Up -0Vote Down
.
Baron Schwartz This guest post is from our friends at Percona. They’re hosting Percona Live London from October 24-25, 2011. Percona Live is a two day summit with 100% technical sessions led by some of the most established speakers in the MySQL field.

In the London area and interested in attending? We are giving away two free passes in the next few days. Watch our @tokutek twitter feed for a chance to win.

Did you know that the following query actually performs a JOIN? You can’t see it, but it’s there:

SELECT the_day, COUNT(*), SUM(clicks),

  [Read more...]
Write Optimization: Myths, Comparison, Clarifications
+2 Vote Up -0Vote Down

Some indexing structures are write optimized in that they are better than B-trees at ingesting data. Other indexing structures are read optimized in that they are better than B-trees at query time. Even within B-trees, there is a tradeoff between write performance and read performance. For example, non-clustering B-trees (such as MyISAM) are typically faster at indexing than clustering B-trees (such as InnoDB), but are then slower at queries.

This post is the first of two about how to understand write optimization, what it means for overall performance, and what the difference is between different write-optimized indexing schemes. We’ll be talking about how to deal with workloads that don’t fit in memory—in particular, if we had our data in B-trees, only the internal nodes (perhaps not even all of them) would fit

  [Read more...]
common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()
+3 Vote Up -0Vote Down

Revision 68 of common_schema is out, and includes some interesting features:

  • eval(): Evaluates the queries generated by a given query
  • match_grantee(): Match an existing account based on user+host
  • processlist_grantees: Assigning of GRANTEEs for connected processes
  • candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
  • easter_day(): Returns DATE of easter day in given DATETIME's year.

Let's take a slightly closer look at these:

eval()

I've dedicated this blog post on MySQL eval() to describe it. In simple

  [Read more...]
May the Index be with you!
+0 Vote Up -0Vote Down

 

The summer’s end is rapidly approaching — in the next two weeks or so, most people will be settling back into work. Time to change your mindset, re-evaluate your skills and see if you are ready to go back from the picnic table to the database table.

With this in mind, let’s see how much folks can remember from the recent indexing talks my colleague Zardosht Kasheff gave (O’Reilly Conference, Boston, and SF MySQL Meetups). Markus Winand’s site “

  [Read more...]
Indexing: The Director’s Cut
+0 Vote Up -0Vote Down

Thanks again to Erin O’Neill and Mike Tougeron for having me at the SF MySQL Meetup last month for the talk on “Understanding Indexing.” The crowd was very interactive, and I appreciated that over 100 people signed up for the event and left some very positive comments and reviews.

Thanks to Mike, a video of the talk is now available:

As a brief overview – Application performance often depends on how fast a query can respond and query performance almost always depends on good indexing. So one of the quickest and least expensive ways to increase application performance is to optimize the indexes. This talk presents three simple and effective rules on how to construct

  [Read more...]
Understanding B+tree Indexes and how they Impact Performance
+3 Vote Up -0Vote Down
Indexes are a very important part of databases and are used frequently to speed up access to particular data item or items. So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes, because unless you understand the inner working of an index, you will never be able to fully harness its power.
Announcing common_schema: common views & routines for MySQL
+1 Vote Up -0Vote Down

Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.

What does it do?

There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals... There are basic functions answering for common needs.

Some of the views/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.

Here are a few highlights:

  • Did you know you can work out
  [Read more...]
On Covering Indexes and Their Impact on Performance
+0 Vote Up -0Vote Down
The purpose of this post is to describe what covering indexes are and how they can be used to improve the performance of queries. People mostly use indexes to filter or sort the results but not much thought is given to actually reduce the disk reads by using proper indexes. So I will show you how to reduce disk reads and hence improve the performance of queries by utilizing indexes properly.
Query Planner Gotchas
+1 Vote Up -0Vote Down

Indexes can reduce the amount of data your query touches by orders of magnitude. This results in a proportional query speedup. So what happens when you define a nice set of indexes and you don’t get the performance pop you were expecting? Consider the following example:

mysql> show create table t;
| t     | CREATE TABLE `t` (
  `a` varchar(255) DEFAULT NULL,
  `b` bigint(20) NOT NULL DEFAULT '0',
  `c` bigint(20) NOT NULL DEFAULT '0',
  `d` bigint(20) DEFAULT NULL,
  `e` char(255) DEFAULT NULL,
  PRIMARY KEY (`b`,`c`),
  KEY `a` (`a`,`b`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now we’d like to perform the following query:

select sql_no_cache count(d) from t where a = 'this is a test' and b between 8000000 and 8100000;

Great! We have index a, which cover this query. Using a should be really fast. You’d expect to use

  [Read more...]
Reasons to use AUTO_INCREMENT columns on InnoDB
+2 Vote Up -0Vote Down

An InnoDB table must have a primary key (one is created if you don’t do it yourself). You may have a natural key at hand. Stop! Allow me to suggest an AUTO_INCREMENT may be better.

Why should one add an AUTO_INCREMENT PRIMARY KEY on a table on which there’s a natural key? Isn’t an AUTO_INCREMENT a pseudo key, meaning, it doesn’t have any explicit relation to the row data, other than it is a number and unique?

Yes, indeed so. Nevertheless, consider:

  • Natural keys are many times multi-columned.
  • Multi column PRIMARY KEYs make for larger keys, and make for bloated secondary keys as well. You may be wasting space for storing the additional AUTO_INCREMENT column, but you may gain space back on secondary keys.
  • Multi column PRIMARY KEYs make for more
  [Read more...]
EffectiveMySQL Meetup in NY
+3 Vote Up -0Vote Down

The first EffectiveMySQL meetup will be held in NY on Tuesday 22nd March 2011 by Ronald Bradford. Details here

The title of the talk is “How better indexes save you money”. Saving money? Hey sure thing :) I’m in Ronald.

For those of you who do not know Ronald Bradford, he’s an Oracle Ace Director in the MySQL field, a long time community contributor and a MySQL expert.

I hope to see you at 902 Broadway New York, NY on Tuesday 22nd March 6pm.

Multi condition UPDATE query
+3 Vote Up -0Vote Down

A simple question I’ve been asked:

Is it possible to merge two UPDATE queries, each on different WHERE conditions, into a single query?

For example, is it possible to merge the following two UPDATE statements into one?

mysql> UPDATE film SET rental_duration=rental_duration+1 WHERE rating = 'G';
Query OK, 178 rows affected (0.01 sec)

mysql> UPDATE film SET rental_rate=rental_rate-0.5 WHERE length < 90;
Query OK, 320 rows affected (0.01 sec)

To verify our tests, we take a checksum:

mysql> pager md5sum
PAGER set to 'md5sum'
mysql> SELECT film_id, title, rental_duration, rental_rate FROM film
  [Read more...]
MySQL Query Optimization – Tip # 1 – Avoid using wildcard character at the start of a LIKE pattern.
+4 Vote Up -1Vote Down
The more I go through others SQL, there are some common mistakes that I see developers making over and over again, so I thought why not start a series of tips that can help developers optimize their queries and avoid common pitfalls. So this post is a part of that series of tips, and this is the first tip "Avoid using a wild card character at the start of a LIKE pattern".
Simple guideline for choosing appropriate InnoDB PRIMARY KEYs
+1 Vote Up -0Vote Down

Risking some flames, I’d like to suggest only two options for choosing PRIMARY KEYs for InnoDB tables. I suggest they should cover 99% (throwing numbers around) of cases.

PRIMARY KEY cases

  • An integer (SMALLINT / INT / BIGINT), possibly AUTO_INCREMENT column.
  • The combination of two columns on a many-to-many connecting table (e.g. film_actor, which connects films to actors), the two columns being the PRIMARY KEYs of respective data tables. This rule may be extended to 3-way relation tables.
  • A short recap: an InnoDB must have a PRIMARY KEY. It will pick one if you don’t offer it. It can pick a really bad UNIQUE KEY (e.g. website_url(255)) or make one up using

      [Read more...]
    Showing entries 1 to 30 of 58 Next 28 Older Entries

    Planet MySQL © 1995, 2014, 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.