mySQL collation question

bookbuyer2000

For years, I've always used phpMyAdmin to create my mySQL databases -- very simple: I type the name of the new database and click the, "create database," button.

Now, with my brand new webhost, there's a new version of phpMyAdmin which allows me to choose:

a.) mySQL "connection" collation
b.) "create database" collation

There are dozens and dozens of choices, including:
binary
ascii_bin
ascii_general_ci
latin1_gin
latin_general_ci
utf8_general_ci
utf8_unicode_ci

Beings I now have a choice, which is the fastest most efficient collation schema to pick?

(I don't know if it helps, but my mySQL database is an offer-pricelist consisting of 100,000 rows, with 10 columns consisting of varchar, text, decimal(4,2) and one key.)

Thank you!!

NogDog

The collation has to do with how character/text fields are sorted. The basic collation type should match the character set being used. My general recommendation for reasonably wide compatibility would be to use "utf8" for the character set and for collation either "utf8_general_ci" for case-insensitive sorting (the "ci") or "utf8_bin" if you want sorting strictly by the characters' binary codes.

See Chapter 9 of the online MySQL manual for lots more info.

MarkR

The fastest most efficient collation to pick is one which has the same character set as you're using - otherwise you will end up with either unwanted conversions, or total garbage in your database.

You should use one which is compatible with your character set - the rest depends on what sort order you want. utf8_general_ci is a good choice for applications using utf8 as their charset.

Mark

bookbuyer2000

Thank you. But, I think I have a dumb question: WHAT character set am I using? (I've never paid attention to character sets).

The mySQL database will consist of 100,000 rows and 10 columns (text, varchar, decimal). Here's a (fictitious) sample what would be in the 10 columns (column name followed by what's in the column):

Item_description: Yellow and Green Striped Widget (must come with: 4 bolts)
Item_production_date: 2006
Price: 1.50

It appears the fanciest "characters" in all the 100,000 rows and columns is a parenthesis and a colon or semicolon.

So, what would be the most efficient Character Set?

NogDog

I really doubt there's any difference worth worrying about unless you get into a mutli-byte character set, and even then I doubt it matters if you're using the basic "latin" characters that are represented by a single byte, anyway. I would probably use utf8 with the utf8_general_ci collation, but you'd be OK with latin1 and the latin1_general_ci collation as well.