Tomorrow, August 22 at 10:00am PDT, I’ll present a webinar called Full Text Search Throwdown.  This is a no-nonsense performance comparison of solutions for full text indexing for MySQL applications, including:

• LIKE predicates and regular expressions
• MyISAM FULLTEXT indexes
• InnoDB FULLTEXT indexes
• Apache Solr
• Sphinx Search
• Trigraphs

I’ll compare the performance for building indexes and querying indexes.

If you’re developing an application with text search features, this will be a very practical and informative overview of your technology options!

Register for this free webinar at  …

Today, Percona announces that Live Virtual Training is now an option for busy professionals.

We set out to develop a training solution that balances the time to deliver the content with the amount of time an attendee can participate in a live virtual training (LVT) session. The goal was to ensure there was still time in the day to allow the attendee to complete his or her work. Percona’s LVT offering extends the days of delivery while reducing the time the attendee needs to be in class each day. Our LVT sessions are a combination of live virtual sessions and remote labs,  blended with online learning. How does this work?

We ask that attendees set aside 2-hours each day for 5-days for the live virtual sessions. This is the time when the Percona instructor delivers the lecture, shows examples, and answers questions. After the live session is over, attendees have a remote lab that will take, on average, 30-minutes to complete. …

I presented a webinar today about SQL Injection, to try to clear up some of the misconceptions that many other blogs and articles have about this security risk.  You can register for the webinar even now that I’ve presented it, and you’ll be emailed a link to the recording, which will be available soon.

During my webinar, a number of attendees asked some good questions, and I wasn’t able to answer them all before the hour was up.  Here are the questions and my answers.

Tobin C. asked:
Q: Does the use of Parameters (particulary OdbcParameter class in .NET) qualify as an appropriate security mechanism for normal WHERE interpolation? Or should the input be validated before creating a parameter?

Yes, the OdbcParameter class should be safe.  The OdbcParameter abstracts query …

I wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our schema and the overall performance. There are different possibilites and we’ll explore the two most common here. User Statistics from Percona Server and pt-index-usage.

User Statistics

User Statistics is an improvement on Percona Server that adds some tables to Information Schema with useful information to understand the server activity and identify the source of the load. …

Having duplicate keys in our schemas can hurt the performance of our database:

• They make the optimizer phase slower because MySQL needs to examine more query plans.

• The storage engine needs to maintain, calculate and update more index statistics

• DML and even read queries can be slower because MySQL needs update fetch more data to Buffer Pool for the same load

• Our data needs more disk space so our backups will be bigger and slower

In this post I’m going to explain the different types of duplicate indexes and how to find and remove them.

Duplicate keys on the same column

This is the easiest one. You can create multiple indexes on the same column and MySQL won’t complain. Let’s see this example:

mysql> alter table t add index(name);
mysql> alter table t add index(name);
…

Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this case we were not allowed to make any query or application logic changes so we chose to implement the Barracuda file format and utilize compressed rows as this appealed to me for this mostly-read application. One quick way you can see if your rows will benefit from compression would be to read Peter Zaitsev’s blog post and execute:

SELECT AVG(LENGTH((`colTextField`)) FROM `t1` WHERE `id` < 1000

compare this to:

SELECT AVG(LENGTH(COMPRESS(`colTextField`))) FROM `t1` WHERE `id` < …

I’m excited by all of the recent developments surrounding the Percona Live MySQL Conference and Expo! Our own Baron Schwartz will moderate the Diamond Keynote Panel entitled “Future Perfect: The Road Ahead for MySQL” which will feature a panel of MySQL industry leaders, including: Sundar Raghavan, director product management at Amazon; Paul Mikesell, CEO of Clustrix; a representative from HP; and, a representative from McAfee. The Diamond Sponsor Keynote Panel will take place at 9:30 a.m. on Thursday, April 12th and provide insight into the future of MySQL technology, adoption, and the ecosystem landscape. I am also very pleased to introduce two new sponsors including McAfee which recently joined as a Diamond Sponsor and AOL which …

The Percona Live MySQL Conference & Expo is going to be awesome! Great speakers, an A-list of sponsors, countless opportunities to engage with the community, and an enthusiastic crowd of MySQL users ensure this is going to be a great event. The conference features 72 breakout sessions, keynotes by leading industry luminaries, an optional day of 16 tutorial sessions, a bustling exhibit hall, and numerous opportunities to connect with other community members.

I am pleased to announce the conference Birds of a Feather sessions and Lightning Talks. Birds of a Feather sessions will be Tuesday and Wednesday nights following the evening receptions. Lightning Talks will be …

SHOW PROFILES shows how much time MySQL spends in various phases of query execution, but it isn’t a full-featured profile. By that, I mean that it doesn’t show similar phases aggregated together, doesn’t sort them by worst-first, and doesn’t show the relative amount of time consumed.

I’ll profile the “nicer_but_slower_film_list” included with the Sakila sample database to demonstrate:

mysql> SET profiling=1;
mysql> pager cat > /dev/null
mysql> SELECT * FROM nicer_but_slower_film_list;
997 rows in set (0.18 sec)

The query consumed 0.18 seconds. Where did the time go?

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000032 |
| checking permissions | 0.000005 |
... snip ...
| init                 | 0.000021 |
| optimizing           | 0.000003 |
| statistics …

Wouldn’t it be nice if you could get expert advice on your SQL queries to find problems in them, the same way that programmers can use lint-check tools to warn about bugs in their C?

if ( execute = 1 ) {
   launch_missile();
}

Such a simple mistake, but it’s the kind of thing that James Bond movies are made of, isn’t it? Well, a lot of SQL queries have similar bugs, and thanks to Miguel Trias, now there’s a tool to help you find them. This is the second addition to our online suite of tools for MySQL users. You paste a query, it tells you what’s wrong with it. Simple as that.

Find the bug in this query:

select * from t1
   left join t2 using(id)
where t2.created_date < 2012-02-15;

Do you see it? Congratulations! I've analyzed that query with the tool, and shared the results with you. …