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 know quite precisely what you’re looking for when coming up with your Google search phrases. Of course, browsing Planet MySQL is a great way of keeping up to …

Introduction

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.

Example

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 need to process a video somewhere in the middle of the queue or an important video enters and …

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 [...]

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 …

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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

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:

mysql> explain …

Properties:

Description:

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.

Usage:

While …

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 “most popular 1-10 pages PDF documents in Italian language from the category “Business” (of course, non-deleted, …

Properties:

Description:

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 …

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).

PLAIN TEXT CODE:

1.  
2. CREATE TABLE text1 (
3.   `text` varchar(50) character set latin1 collate latin1_general_cs NOT NULL …