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 29

Displaying posts with tag: index (reset)

Indexing Talk Online
+1 Vote Up -1Vote Down

I am doing a quick blog post to announce that I have put an indexing talk online*. Most recently, I delivered this indexing talk at Confoo and Scale 11x.

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...]
The small improvements of MySQL 5.6: Duplicate Index Detection
+3 Vote Up -0Vote Down

Here at the MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5.6 brought: GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete

  [Read more...]
Speedup mysql index using ssd disk
+1 Vote Up -0Vote Down
You probably ask yourself sometimes how you can boost MySQL indexes when you are working with BIG databases/tables. Really the solution is here for a long time already. You can use SSD disks (also known as flash disks). These disks are not that big that traditional SATA hard drives but they provide a superior performance. If your database is rather big to be placed on SSD disk you can still
Online ALTER TABLE in MySQL 5.6
Employee_Team +1 Vote Up -0Vote Down
This is the low-level view of data dictionary language (DDL) operations in the InnoDB storage engine in MySQL 5.6. John Russell gave a more high-level view in his blog post April 2012 Labs Release – Online DDL Improvements.

MySQL before the InnoDB Plugin

Traditionally, the MySQL storage engine interface has taken a minimalistic approach to data definition language. The only natively supported operations were CREATE TABLE, DROP TABLE and RENAME TABLE. Consider the following example:

INSERT INTO t VALUES (1),(2),(3);

The CREATE INDEX statement would be executed roughly as follows:

  [Read more...]
Optimal index size for variable text in MySQL
+2 Vote Up -0Vote Down

You often see databases with huge dynamic text fields, such as VARCHAR(255), TEXT, or as I recently was allowed to see the blanket use of LONGTEXT (max 4GiB) in order to be invulnerable from all contingencies. Things getting even worse when an index is used over such columns, because hey, there is an index. It makes things fast :-) Okay, jokes aside. Often you can save a lot of space and time, MySQL spends traversing the index when using a proper column type and index size.

Read the rest »

Using the Mysql FullText Index Search
+1 Vote Up -0Vote Down

Today let’s talk about a resource very useful on MySQL, the FullText Index and Search
This resource is very powerful, today on versions 5.5 is just available to MyISAM engine, but, like we can see on MySQL FullText documentation, it will be available also to InnoDB on MySQL 5.6

Usually when we want to search for a word or expression, we use LIKE ‘%word%’, in case we are looking for more than one word we use LIKE ‘%word1%word2%’, what many people don’t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with FullText Index
the syntax is easy, MATHC() … AGAINST (), where MATCH we specified the name(s) of column(s) which we are looking

  [Read more...]
VC funding for Hadoop and NoSQL tops $350m
+0 Vote Up -0Vote Down

451 Research has today published a report looking at the funding being invested in Apache Hadoop- and NoSQL database-related vendors. The full report is available to clients, but non-clients can find a snapshot of the report, along with a graphic representation of the recent up-tick in funding, over at our Too Much Information blog.

More on OR-conditions considered bad... And an apology..
+4 Vote Up -0Vote Down
In my recent post on OR-conditions I made a mistake, and I appologize for that. I made the statement that MySQL will only use 1 index per statement, whatever you do.

This is no longer true, as a matter of fact, and that has been the case since MySQL 5.0 and I should have checked. MySQL is actually able to use index_merge. An explanation why I didn't look for thi more carefully, yes an explanation, not an excuse, is that the optimizer doesn't seem to want to use this very often. Which is too bad.

So, with this in mind, and using the same table as in the previous post, let's look at index_merge in action. Or possibly, not so much in action. Let's recap what the table looks like:
CREATE TABLE `product` (
`id` int(11) NOT NULL

  [Read more...]
Annoyances, annoyances. Or Yet another HBF (Half Baked Feature)
+5 Vote Up -0Vote Down
About any product, be it computer hardware, software or any other product, has features that are annoying to some of us. But few products has so many features that are annoying to just about everyone as computer software. And among computer software, database software in particular seems to to have these features, which some people seems to like, and some just find annoying. And then there are features, or lack of them or implementation specific details that seems to annoy just about everyone. Things that work in a partuicular way because someone, somewhere, in some distant universe, had the notion that this was a good thing. Often features relating back to ancient times. And sometimes features that you just know work in a weirdo way because the person, if it was a person, figuring out the feature of the implementation of it, really must have been smoking  [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.

What is this MySQL file used for?
+7 Vote Up -0Vote Down

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of

  [Read more...]
Persistent index statistics for InnoDB
+5 Vote Up -0Vote Down

In browsing the BZR tree for lp:mysql-server, I noticed some rather exciting code had been merged into the Innobase code.

You may be aware that InnoDB will do some index dives when opening a table to get some statistics about the indexes that can help the optimiser make good query plans.

The problem being that this is many disk seeks. It means that on server restart, you have to spend a whole bunch of time seeking around the disk reading index pages.

Not any more.

There is now code merged in to store the calculated statistics in a table inside InnoDB so that these index dives don’t have to happen on startup.

Originally, this looked like it was going to make it into InnoDB+. The

  [Read more...]
Loose index scan vs. covered indexes in MySQL
+2 Vote Up -0Vote Down

Loose index scan in MySQL can really help optimizing “group by” queries in some cases (for example, if you have only min() and/or max() as your aggregate functions). For example, if you have this query (to find maximum delay for all US flights with departure on Sundays in 2010):

select max(DepDelayMinutes), 	carrier, dayofweek
from ontime_2010
where dayofweek = 7
group by Carrier,  dayofweek

the usual case will be adding a covered index on (dayofweek, Carrier, DepDelayMinutes). And MySQL will use this index fine (using index mean it will use the covered index):

mysql> explain select max(DepDelayMinutes), Carrier, dayofweek from ontime_2010
where dayofweek =7 group by Carrier, dayofweek\G
*************************** 1. row ***************************
           id: 1
  [Read more...]
Setting up slave, stripping indexes and changing engines, on the fly
+4 Vote Up -0Vote Down

Warning, the following is quite ugly, but does the job :)

A while back I needed to create an archive slave database from a half a terabyte myisam master and had space restrictions. I could not dump the db, load it, then drop keys (archive doesn’t support keys apart from a primary key on one column as of 5.1), alter engine etc (would take even longer than it took either way). So an ugly single liner came to mind and worked nicely too.

mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 | sed 's/ENGINE=MyISAM/ENGINE=archive/g' | grep -v '^ UNIQUE KEY' | grep -v '^ KEY' | perl -p0777i -e 's/,\n^\)/\n\)/mg' | mysql -uuser -ppassword -h127.0.0.1 -P3307 dbname

So what is it doing?
Broken down:
mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname

  [Read more...]
MySQL GIS – Part 4
+1 Vote Up -0Vote Down


Geo spatial indexes are what make this type of data valuable.  With shape and point data you can find relationships between object in our physical world.  How close is the lightning in the storm front?  What homes where hailed on? (WDT) What schools are in my city?  With a list of homes for sale, how fare are they from their nearest school?  What picture where take in this area. (TwitPic)

Lets start with a simple grid of coordinates by creating a table for it call geom, adding our data points in and out of our grid and then searching with a small bounding box. The grid looks like this.

0,0 0,1 0,2 1,0 1,1 1,2 2,0 2,1 2,2
  [Read more...]
Tokutek’s Fractal Tree Indexes
+2 Vote Up -3Vote Down

Tokutek’s Bradley did a session on their Fractal Tree Index technology at the MySQL Conference (and an OpenSQL Camp before that – but I wasn’t at that one), and my first thought was: great, now we get to see what and where the magic is. On second thought, I realised you may not want to know.

I know I’m going to be a party pooper here, but I do feel it’s important for people to be aware of the consequences of looking at this stuff (there’s slide PDFs online as well as video), and software patents in general. I reckon Tokutek has done some cool things, but the patents are a serious problem.

Tokutek’s technology has patents pending, and is thus patent encumbered. What does this mean for you? It means that if you look at their “how they did it” info and you happen to code something that later ends up in a related patent lawsuit,

  [Read more...]
Ineffective concatenated indexes
+2 Vote Up -0Vote Down

In MySQL significant performance improvements can be achieved by the correct use of indexes. It is important to understand different MySQL index implementations and one key improvement on indexes defined on single columns is to use multiple column or more commonly known concatenated indexes.

However it’s also possible to define ineffective indexes. This example shows you how to identify a concatenated index that is ineffective.

CREATE TABLE example (
  x  VARCHAR(10),
  y  VARCHAR(10),
  z  VARCHAR(10),
UNIQUE INDEX (a,b,c,d)

INSERT INTO example(a,b,c,d)
  [Read more...]
Duplicate indexes and tuples
+0 Vote Up -0Vote Down
At my current job, I have seen much over indexing in some of our production schemas.

Here is a quick rule of thumb; if you have an schema such as
CREATE TABLE blah ( col1 int default not null, col2 int default not null, primary key (col1,col2), key col1, key col2);
CREATE TABLE blah ( col1 int default not null, col2 int default not null,unique key (col1,col2), key col1, keycol2);

The index ‘key col1’ is redundant to the primary (or unique key) as col1 is the first in the other indexes tuple. Creating the col1 index will do nothing more than cause a big performance hit for updates and reduce the valuable useful space of the innodb buffer pool (if innodb is being used) intern making things slow.
451 CAOS Links 2010.02.02
+1 Vote Up -1Vote Down

Oracle’s plans for Sun’s OSS. The UK’s updated OSS strategy. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

Oracle’s plans for Sun’s OSS
# Oracle’s MySQL strategy slide.

# eWeek reported that database thought leaders are divided on Oracle MySQL.

# Savio Rodrigues and Computerworld on Oracle’s plans for MySQL, other open source assets.

# Zack Urlocker is leaving Oracle/Sun/MySQL.

# Red Hat’s Mark Little

  [Read more...]
Handy MySQL documentation indexes
+1 Vote Up -0Vote Down

I just discovered today in the MySQL 5.1 Reference Manual a handy set of additional indexes in the System Navigation section.

  • Index
  • Standard Index
  • C Function Index
  • Command Index
  • Function Index
  • Transaction Isolation Level Index
  • JOIN Types Index
  • Operator Index
  • Option Index
  • Privileges Index
  • SQL Modes Index
  • Status Variable Index
  • Statement/Syntax Index
  • System Variable Index

Perhaps they have been around for some time and I’ve not noticed, but there are much better then searching when you know the content type as per the index list on what you are searching for.

MySQL Librarian: Capturing Community Insights
Employee +1 Vote Up -0Vote Down

In the MySQL Community team, our charter is to serve the MySQL community — new and old MySQL users alike. One of the ways we do this is by facilitating information exchange between community members, where the new can learn from the old.

And there’s been lots of that information exchange going on, such as over mailing lists (in the early days the dominant vehicle), forums, and Planet MySQL.

One problem with this information exchange has been its ephemeral nature. The same questions pop up for many new users, and should they for some reason not be amongst the issues solved in the MySQL documentation, chances are you’ll have to

  [Read more...]
MySQL Indexing Considerations Of Implementing A Priority Field In Your Application
+0 Vote Up -0Vote Down


If you, like me, are building or thinking of implementing a MySQL-powered application that has any need for prioritizing selecting certain data over other data, this article is for you.


As a real world example, consider a queue-like video processing system. Your application receives new videos and processes them. The volume of incoming videos can at times be higher than the processing rate because the process is CPU bound, so occasionally a pretty long queue may form. You will try to process them as fast as you can but…

Note that I am using a queue here, so the the next item to be processed is a result of sorting by some sort of field in a ascending order, for example ORDER BY id or ORDER BY upload_date. I’ll pick the id sort here.

…suddenly, you

  [Read more...]
2/3 myisam_suggest: an AutoComplete tool for MySQL fulltext indices
+0 Vote Up -0Vote Down
As I’ve written in my previous post “1/3 Implementing an AutoSuggest feature using MySQL fulltext indices”, it’s possible to use the MySQL/MyISAM full-text index to extract search words for an AutoSuggest feature with great performance (because the index tree is used actually). This tool, called myisam_suggest, is my first implementation of this. Download Here: myisam_suggest.c [...]
How to pick indexes for order by and group by queries
+0 Vote Up -0Vote Down
First some of the things that you need to use and understand

Explain Syntax

Order by Optimization

Group by Optimization

Update: Updated errors.

Now some details that are usually missed. GROUP BY does sorting unless you tell mysql not to. GROUP BY has two optimization methods, loose index scan, and tight index scan.

Loose index scan, scans the entire table index, while tight index scan uses some sort of constraint. For large datasets that are accessed often and require some sort of group by, tight index scans are better.

So how to pick columns to create the optimal

  [Read more...]
FULLTEXT lesson/reminder of the day
+0 Vote Up -0Vote Down
I've been using MySQL fulltext indexes on a table where I keep a few varchar and one text column that is used for searches. I've had it defined as:

CREATE TABLE `items_text` (
  `item_id` bigint(20) NOT NULL,
  `fts` varchar(4) NOT NULL default 'grzr',
  `author` varchar(80) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `content` text NOT NULL,
  PRIMARY KEY  (`item_id`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `author` (`author`),
  FULLTEXT KEY `fts` (`fts`),
  FULLTEXT KEY `content` (`content`)

One of my colleagues pointed out he was experiencing slow performance with this query:

select count(*) from items_text where (MATCH (title, author, content) AGAINST ('+iron +man' IN BOOLEAN MODE))

I ran EXPLAIN just to make sure that the index was being used:

  [Read more...]
Variable's Day Out #14: log_queries_not_using_indexes
+0 Vote Up -0Vote Down


Applicable To MySQL Server Server Startup Option --log-queries-not-using-indexes Scope Global Dynamic Yes Possible Values Boolean Default False Category Performance, Monitoring, Best Practices


If you have slow query logs enabled (with --log-slow-queries), this variable will help interpret all those queries that are not using indexes as slow queries.


While designing a MySQL oriented application, I generally design my schema first without introducing any index (only exception being the PRIMARY ones). Get the application ready. Next enable

  [Read more...]
Using Sphinx for Non-Fulltext Queries
+0 Vote Up -0Vote Down

How often do you think about the reasons why your favorite RDBMS sucks? Last few months I was doing this quite often and yes, my favorite RDBMS is MySQL. The reason why I was thinking so because one of my recent tasks at Scribd was fixing scalability problems in documents browsing.

The problem with browsing was pretty simple to describe and as hard to fix - we have large data set which consists of a few tables with many fields with really bad selectivity (flag fields like is_deleted, is_private, etc; file_type, language_id , category_id and others). As the result of this situation it becomes really hard (if possible at all) to display documents lists like “

  [Read more...]
Variable's Day Out #2: key_buffer_size
+0 Vote Up -0Vote Down


Engine(s) MyISAM Server Startup Option --key_buffer_size=<value> Scope Global Dynamic Yes Possible Values Integer
Range: 8 - 4294967295 (4 GB) Default Value 131072 (128 KB) Category Performance


This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.

Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the

  [Read more...]
MySQL: Collation matters when using unique indexes
+0 Vote Up -0Vote Down

When using a uniqie index on a text field in mysql, the column collation setting is very important. The collation settings of a column does not only affect sorting and comparsion, but also unique indexes. So you can not insert "a" and "A" into a table that has a unique index on a column that has a case-insensitive collation. The mysql manual about collations: "A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set."

Here is an example:
The column text in table text1 has a case-sensitive collation (_cs suffix), the column in text2 has a case-insensitive collation (_ci suffix).

  • CREATE TABLE text1 (

  •   [Read more...]
    Showing entries 1 to 29

    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.