|Showing entries 1 to 30 of 43||Next 13 Older Entries|
Since the dawn of time, MySQL indexes have a limit: they cannot be descending. Yes,
ALTER TABLE and
CREATE INDEX can contain the
DESC keywords. It is perfectly legal in MySQL. But this does not mean that descending indexes are created. This is a well-known MySQL feature: when it cannot do something, it just pretends to. Well… someone thinks it is a feature. I think it is a bug (a bug is an unexpected behaviour), but what can we do.
The lack of support for descending indexes is only an issue when we need to create an index in which at least one column is ascending and at least one column is descending. For example, MySQL pretends to understand the following statement, but the resulting index won’t probably help us:
CREATE INDEX my_index ON my_table (my_column ASC, your_column
The talk is on YouTube at Are You Getting the Best Out of Your MySQL Indexes? There are also PDF slides.
From the official conference description, if you want to know more:
MySQL indexes are often used to make performance better. However, they can make performance suffer if you are not using them properly. Oracle ACE Director Sheeri Cabral explains the pitfalls to avoid with indexes and how to utilize compound indexes to maximize index availability with the least amount of write overhead.
*I know I have not[Read more...]
Thanks to Tim Callaghan for speaking Tuesday night at the Effective MySQL New York meetup on Fractal Tree Indexes : Theory and Practice (MySQL and MongoDB). There was a good turnout and a full room to learn how the TokuDB storage engine from Tokutek is changing how to handle big data in MySQL.
Also interesting is how the same technology has been applied for use in MongoDB including giving MongoDB transactions; a big change for NoSQL.
Related News: Tokutek Meets Big Data Demand With Open Source TokuDB
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...]
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...]
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...]
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...]
In the article about the role of a primary key, I mentioned that a secondary index in an InnoDB table consists not only of the values of its member columns, but also values of the table’s primary key are concatenated to the index. I.e. the primary key contents is part of every other index.
Assuming the following table structure:
CREATE TABLE `bets` ( `id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `game_id` int(10) unsigned NOT NULL, ... PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB
Here is the visualization:
If MySQL could use in queries these implicitly[Read more...]
MySQL performance is largely defined by keys and how efficiently queries can use them. As you scale, at certain point it isn’t enough anymore to just have any indexes and still get a good performance in return. You have to really figure them out and allow your queries to do less work, as little work as possible.
The approach presented in this article can sometimes help designing such good, efficient indexes. As a consultant, I have to rely on it myself from time to time, having to optimize a query that works in a database I know nothing about.
Let’s assume there is an application, which collects user activity in various places. The application uses a poorly indexed database, so there are plenty of examples to choose from. Our example query performs a full table scan, which means it reads all rows from the table it uses.[Read more...]
How important a primary key design can be for MySQL performance? The answer is: Extremely! If tables use InnoDB storage engine, that is.
It all begins with the specific way InnoDB organizes data internally. There are two major pieces of information that anyone should know:
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5
The post Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact appeared first on ovais.tariq.
In addition to speaking at RMOUG event in Denver, I also spoke today in Broomfield on “Improving MySQL Performance with Better Indexes”.
This presentation included details on :
This is the title of my first blog post on MySQL Performance Blog. It deals with a customer case where the customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. You...
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...]
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...]
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...]
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...]
Archimedes, the first DBA
According to a recent MIT Sloan Management Review study, top performing organizations use analytics 5 times more than lower performers. That’s pretty astounding. And while we all know about the ocean/lake/waves/(your favorite water analogy) of Big Data we struggle with everyday, information is not knowledge. So how can we get insight from data? Recent articles from[Read more...]
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.[Read more...]
At this week’s SF 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 tomorrow (Wednesday, 6/22), and will be held at CBS Interactive (235 2nd St., San Francisco). Thanks to hosts Erin O’Neill and Mike Tougeron for the invitation and location.
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.
This is a general discussion applicable to all databases using indexes and is not[Read more...]
Historically it was considered that MySQL will generally use only one index per referenced table in a SQL query. In MySQL 5.0 the introduction of merge indexes enabled for certain conditions the possibility to utilize two indexes however this could result in worst performance then creating a better index. In MySQL 5.1 it became possible to control optimization switches with the optimizer_switch system variable.
However in explaining how to utilize the intersection, union and sort union in queries I discovered that MySQL could use three indexes for one given table.
Extra: Using union(name,intersect(founded,type)); Using where
I was not aware of this.
Learn how to use one simple advanced technique to make better MySQL indexes and improve your queries by 500% or more. Even with a highly indexed schema significant improvements in performance can be achieved by creating better indexes.
This presentation introduces the approach for correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then discussed is not only how to apply indexes to improve query performance, but how to apply better indexes and provide even greater performance gains.
Running with Big Data
It’s spring here in Boston, though one could hardly tell (still barely hitting 40°F). So, for those stuck indoors working out on the treadmill, or those lucky enough to do a workout outdoors, we’ve got a great podcast. Our Chief Scientist and co-founder Martín Farach-Colton had the privilege of sitting down with Sheeri Cabral and Sarah Novotny for their weekly MySQL Database Community Podcast (OurSQL Episode 39). In it, he speaks about Tokutek and TokuDB v5.0, which was just released last week (see[Read more...]
|Showing entries 1 to 30 of 43||Next 13 Older Entries|