I've seen a lot of SQL code that uses an IN clause as a place to put a variable number of parameters, allowing the query to be more flexible. There are several downsides to this technique. This article discusses those downsides and explains how to avoid them.

MySQL doesn't allow referring to a table that's targeted for update in a FROM clause, which can be frustrating. There's a better way than creating endless temporary tables, though. This article explains how to update a table while selecting from it in a subquery.

I?ve recently written about techniques for archiving, purging, and re-indexing huge database tables. These techniques exploit both data structure and usage patterns. In this article I?ll develop that theme further, and explain how to write more efficient non-backtracking maintenance jobs when the update and insertion patterns permit.

This article is a quick pointer on MySQL's three available tools for UPSERT (update/insert) or MERGE queries.

There is a bug in MySQL that causes an UPDATE to fail with a unique index violation, even though the statement doesn't create duplicate values. In this article I'll explain when this bug can happen, and how to work around it.

In recent articles I explained how I've optimized queries on some large tables at my current employer, and how I've written archiving and purging jobs to trim the tables down to a manageable size. This article explains how I re-indexed some of those tables without taking the server offline.

Patrick released a new version of the perl MySQL connector. This .pm was my introduction to the mysql world, way back in the day. If you can program your way out of a perl brown paper bag and you haven’t used this tool yet, I recommend you write a hello world program to get familiar with it. Good stuff.

Dear DBD::mysql users,

This announcement comes a few days late, but DBD::mysql version 3.0004
(stable, production) and 3.0004_1 (dev) have been released!

Version 3.0004 is the production version with server-side prepare
statements turned off by default, and 3.0004_1 is the development
version with server-side prepare statements turned on by default.

The changes in 3.0004, as listed in the changelog:
  * Fix dbd_st_finish which closed the handle prematurely (Martin Evans)
  * Compile issues (Martin Evans)
  * Fix to dbd_bind_ph to deal with numbers (ints, floats) correctly
    (Alexey Stroganov)
  * Test changes - bind_param 41 and 42

The changes to …

To find a list of tables in a schema:

$ sqlcli -u TEST,TEST -d SYNCMANA "SELECT * FROM TABLES WHERE SCHEMANAME = 'TEST'"

To find a list of triggers on a table:

$ sqlcli -u DBSERVICE,SECRET -d SYNCMANA "SELECT * FROM TRIGGERS WHERE TABLENAME = 'T'"

To describe a table’s structure:

$ cat me && echo "-----" && 
   loadercli -d SYNCMANA -u TEST,TEST -b ./me && echo "-----" && 
   cat tabledef.txt
dataextract for dataload table T
outstream file 'tabledef.txt'
outstream file 'idontcare'

-----
Loader protocol: '/home/cjcollier/.sdb/loader/log/loader.prt'
Loader packages: '/home/cjcollier/sdb/loader/packages'

User TEST connected to database SYNCMANA schema TEST on local host.
dataextract for dataload table T
outstream file 'tabledef.txt'
outstream file 'idontcare'
Successfully executed

-----
//
CREATE TABLE "T"
(
        "I"  Integer  NOT NULL,
        "C"  Varchar (32) UNICODE,
        PRIMARY KEY ("I")
)
 NO …

I just updated my SQL reserved words checker to include a few new words from MySQL 5 that it wasn't picking up.

I use the tool when I'm designing a database table, or column. It can tell you if a value is a reserved word in MySQL, PostgreSQL, Oracle, or SQL Server.

MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.

To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:

ALTER TABLE news ADD FULLTEXT(headline, story);

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:

SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');

The result of this query is automatically sorted by relevancy.

MATCH

The MATCH function is used to …