Fine-tuning FULLTEXT: Adding word characters
Perhaps you want "." or ":" or "-" or something to be treated as a word character so that you can do a fulltext match against an IP or MAC address.
If you want to change the set of characters that are considered word characters, you can do so in two ways...
* Modify the MySQL source ... and recompile MySQL.
* Modify a character set file: This requires no recompilation.
Thus sayeth the Manual. What it doesn't say is just what to modify in that character set file. I had to ask MySQL developer Alexander Barkov for help to get it right.
In the user comments on that manual page, John Navratil has provided most of the answer, but there's an important step missing: adding a new collation.
Choosing index prefix length
It can be handy to index just part of a long character column. The strings might vary enough in just the first few characters to get the same cardinality out of a partial index as a full index would give, and the partial index takes up much less disk space and memory.
But how long should that prefix be? Should it be the same cardinality as the original data? Within 10%?
I dunno; somebody else can blog about that decision. I'm just interested in a query that finds the number which satisfies your requirements.
I have a table called 'numbers' with an int field 'num' that just holds values 1 through several thousand.
Then:
SELECT num AS prefixLength, COUNT(DISTINCT LEFT(stringField, num)) AS prefixCardinality, dt.originalCardinality FROM yourTable JOIN numbers JOIN (SELECT COUNT(DISTINCT stringField) AS originalCardinality FROM yourTable) AS dt GROUP BY prefixLength HAVING prefixCardinality >= .90 * originalCardinality LIMIT 1;
Adjust the HAVING clause according to your needs.
Power Play, Eating a Hole in my Pocket
The budget is a big obstacle in getting to Skokomish. We're trying to save nearly 1/3 of my income. We're not big spenders as a general rule - we rarely buy name brand anything, we don't spend a lot on entertainment, and we don't carry credit card balances so our only interest payments are the home mortgage and a student loan. Still, there doesn't seem to be much accumulating in the savings account at the end of each month.
I looked at last month's bills. There were two that immediately stood out where we could find the savings we need: electricity and food.
The power company took $400 last month. Most of that was to run the air conditioner; we've turned the temperature up 5 degrees and all the ceiling fans on high. It costs 76 cents to dry a load of clothes; the clothesline that sterilizes and bleaches our cloth diapers will see more use. My computer burns about $7 each month just sitting on all night; I've started shutting it down before bed. We already use CFLs instead of incandescent bulbs; we might burn those even less by adjusting to a 'dawn to dusk' schedule instead of a 'mid-morning to well after dark' schedule.
Food cost us about $1500 last month - $300 apiece, and that includes the baby still on just milk. About 2/3 was groceries, and the rest was eating out. For the next year, we're going to eat out only Sunday lunch and maybe dinner, and all other meals at home. We're going to keep our monthly grocery bill under $250, and we're not going to sacrifice adequate nutrition and variety while doing it. How? We're starting by eating through everything we have stockpiled in the house before we make another shopping trip.
A Goal
I have a goal. I'm going to move my family to a farm.
Obstacle: We have no knowledge of farming. We kill houseplants through neglect and poor placement. I've read enough Steinbeck to know that even those raised on a farm may fail miserably.
Facility: The property we like is on Skokomish Farms, which is being designed as a community where homeowners with no farming experience can learn and benefit from a professional's experience.
Obstacle: Even the cheapest lots at Skokomish Farms are prohibitively expensive, and that's just for the land, with no house. Our mortgage is upside down, so selling our house now would leave us still in debt and with nothing for a down payment.
Facility: We have set in place a budget that would allow us to pay off our house in about three years - or make a reasonable down payment on the farm.
Obstacle: That budget requires some extremely frugal living and some sacrifices we are not accustomed to making.
Facility: My family is committed to the dream. My wife came up with the idea in the first place, and she has the stubborn tenacious personality to see it through. My oldest daughter is excited about the idea and clearly understands that one of the sacrifices will be eating in instead of out.
Obstacle: Even living as cheaply as we can, we despair that the properties at Skokomish will be taken before we can build the required capital.
Facility: The USDA and FHA have some loans available that might pave the way - if we qualify for them.
Who’s there?
While playing under the dining room table:
Eden (squeaky voice): You can't come in!
Eden (deep voice): Why not?
Eden (squeaky voice): Because you're a monster!
Eden (deep voice): Oh. Ok.
Careful with those required files in Ruby
Getting Ruby to talk to MySQL was pretty easy in irb:
C:\>irb
irb(main):001:0> require 'rubygems'
=> true
irb(main):002:0> require 'mysql'
=> true
irb(main):003:0> db = Mysql.connect('localhost', 'root', 'password', 'test')
=> #<Mysql:0x2cc5270>
irb(main):004:0>
But then I tried the very same code from a file:
C:\>ruby mysql.rb
./mysql.rb:3: uninitialized constant Mysql (NameError)
from C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
from C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
from mysql.rb:2
That require 'mysql' line looks in the current directory first ... and I'd used the name mysql.rb for the file I was coding ... so my code just included itself, and not the gem I wanted. Doh!
Workbench loses its crutches
When MySQL Workbench was still in alpha and beta stages, some dark murmurs in the community suggested that the OSS (free) version was crippleware, part of an evil plot to make you pony up $99 if you wanted to get any real benefit. Closed source and profits and bears, oh my!
The new GA release of Workbench gets some of those "crippled" features back. In particular, the OSS version now allows connections to a live server, so you can reverse and forward engineer without having to go through a dump file. I also have it on good authority that the OSS version will be able to print diagrams directly, instead of you having to export an image.
If you see any merit in a visual diagram of your database, but you wrote off Workbench based on earlier experiences, give it another try now.
Incentive
Eden: I'm not hungry. I don't want to eat dinner. What's that on the counter?
Daddy: Chocolate milk.
Eden: Am I going to drink some?
Daddy: No, you said you didn't want anything.
Eden: .... I think I'm hungry now.
Flour Power
(two months ago)
Iona: Daddy, am I going to marry you when I get big?
Daddy: No, you'll marry someone else, and the two of you will have your own family.
Iona: (in tears) I don't want to leave! I want to stay with you and mommy!
(last week, after passing the display cakes in the bakery)
Iona: Daddy, what kind of cake did you and mommy have at your wedding?
Daddy: I'll show you a picture when we get home.
(a few minutes later)
Iona: Daddy, I think it will be ok if I marry someone else and we have our own family.
errno: 121 (Duplicate key) with CREATE TABLE
Trying to create a table, and getting something like this?
ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 121)
Discovered that perror 121 says this?
MySQL error code 121: Duplicate key on write or update
Really confused how you might get a duplicate key error while creating a table?
If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:
SELECT constraint_name, table_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY' AND table_schema = DATABASE() ORDER BY constraint_name;
(If you're still on 4.1 or earlier,
mysqldump --no-data yourDbName | grep CONSTRAINT
to get a similar list)
Thanks to [raymond] on Freenode.