Optimizing my MySQL database.

[google] "sql tutorial"

http://www.w3schools.com/sql/sql_create.asp

-me

Quote:

1. Is a key an index?

You can also have indices that are not keys

Quote:

2. The tutorials I found describe a few keys [UNIQUE|PRIMARY|{I forget}]. What do each do?

Primary keys are always unique, all the data will be sorted by the primary key by default. A Unique key means that there are no equal keys allowed. The other key (which I don't remember, too) allows that.

Quote:

2. What is the 'ALTER TABLE' command to make a field a key?

Don't know, guess one of the links posted above should explain this.

You could also edit the keys with the myPhpAdmin for the mySQL database.

Christian

Palidine, I highly doubt you'll find a tutorial that I haven't already read.

Quote:

Original post by MrSparkle You can also have indices that are not keys

You have just provided contradicting facts to my explanation of the universe MySQL. If I was a religious zealot... actaully, lets not get into that.

Instead, can you give me a hand with this...

The "rows" listing doesn't appear to be going down very much, after adding the index.

I've always seen the terms "key" and "index" used to mean basically the same things. "Key" is more common in database theory, mostly as part of more specific terms like "primary key", "candidate key", "superkey", etc; while "index" is more common in implementations, but they often use "key" to mean the same. Whenever you have a non-unique index (not PRIMARY KEY or UNIQUE), it's not a key in the database-theory sense - it's just a trick to make the implementation faster - but I don't think anybody would mind if you informally called it a key.

PRIMARY KEY and UNIQUE indexes work the same as each other, except (at least in MySQL) primary keys can't be NULL. And you can only have one primary key per table, and it's conventional to make that be an ID number, and it's what you generally use when referring to individual rows. Unique indexes require each row to have unique values for the key (er... indexed) fields, while normal indexes don't - that's useful for data integrity (e.g. making sure no two characters have the same name) and allows the database system to do a bit more query optimisation.


I think the "1 row set in (0.00 sec) <- AWESOME" is just the time it takes to do the EXPLAIN, not to execute the actual SELECT, so it's not a very useful number.

The "Using index" means it's using the index and the order by optimisation is being used, so it doesn't have to do a separate sorting pass after collecting the data. "Rows" is the number it thinks it has to examine before it applies the sorting and limit, but the amount of work per row is much higher in the "Using filesort" case (where it has to read all the rows then sort them) than in the "Using index" case (where it's already sorted and it just has to take the first five of them). (It doesn't know (or care) precisely how many rows there are, which is why it jumps from 1468 to 1220 to 1206 when really there's 1203 - it's just interested in the general shape of the database so it can work out the fastest way to perform the query.)

Here's the difference:

Index: An way for the sql database engine to quickly locate data in a column. Like an index at the back of a book. You can index multiple columns in a table, but even though indices speed up SELECTs, they slow down INSERTs, UPDATEs, and DELETEs, as the database has to reindex any columns that have changed.

Primary Key: The unique identifier for a given row. Usually (but sometimes naively) implemented as an auto-incrementing integer column. The primary key is most always indexed.