MySQL Full-Text Search Is Broken!

…and has been for 8 years.

I came across MySQL bug #2095 today:

full-text search for words containing hyphens won't work
Submitted: 11 Dec 2003 6:15
Hyphen '-' characters break literals at the moment.
A search for something like "GATA-D22S690" finds
all entries containing GATA and not the full 
hyphenated text.

~ Link

MySQL disputes the fact that this is a bug.

But in reality – as the submitters points out – there are many cases where hyphens are part of words. The example I ran into tonight was product model numbers (eg. “00-17” or “j-35”).

The MySQL docs recommend recompiling or modifying one of two system files.  None of these options are feasible in a shared hosting environment.

I can’t believe this bug exists. As far as I’m concerned this is a complete and utter deal breaker for any application that needs to search non-word strings containing hyphens. I’ve been exclusively using MySQL for my entire career and I now know that a bunch of sites I’ve worked on in the past 10 years were launched with this bug.

 .

DIGG: 4000% PERFORMANCE INCREASE BY SORTING IN PHP RATHER THAN MYSQL

To scale at Digg they followed a set of practices very similar to those used at eBay. No joins, no foreign key constraints (to scale writes), primary key look-ups only, limited range queries, and joins were done in memory. When implementing the comment feature a 4,000 percent increase in performance was created by sorting in PHP instead of MySQL. All this effort required to make a relational database scale basically meant you were using a non-relational database anyway. So why not just use a non-relational database from the start?

[via High Scalability]

.

How To: Exclude Words Like “An, A, The” From Alphabetized MySQL ORDER

When ordering lists of names or titles it’s sometimes desirable to exclude articles or other words from the order clause (eg. you want “The Burning Hell” to show up before “Great Lake Swimmers” in a list ordered by name). Early on in my career I must have assumed it was not possible and never bothered to look into again because I don’t recall ever ordering a list like this.
Anyways. Here’s how you do it:

SELECT name FROM artists ORDER BY TRIM( LEADING "a " FROM TRIM( LEADING "an " FROM TRIM( LEADING "the " FROM LOWER( name ) ) ) )

[thanks metafilter].