Sign in to follow this  

Optimizing my MySQL database.

This topic is 4110 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

So, I'm *really* not in the mood to go back to scavanging the internet for a tutorial that I can understand on the subject. So I decided to post here... What I'm trying to do is index my database so that commands such as... SELECT name,price,alcohol FROM bar WHERE alcohol > 5% ORDER BY price DESC LIMIT 0, 5; ...can be completed significantly faster. However, what I don't understand is how to make it indexed. I set something to be my PRIMARY KEY (I don't really understand what it did, but ok), and everything else I left blank. So I guess what my questions are, are....
  1. Is a key an index?
  2. The tutorials I found describe a few keys [UNIQUE|PRIMARY|{I forget}]. What do each do?
  3. What is the 'ALTER TABLE' command to make a field a key?
... inaddition, can you give me any advice on the subject?

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
well i think you should scavenge the net for tutorials. building indices (why/how) on mysql or rdbms's in general is a very common thing so tutorials are very easy to find.

http://www.google.com/search?complete=1&hl=de&safe=off&q=mysql+index+table&btnG=Search&lr=

i checked a few articles and tutorials on the first search result page. that should give you quite a good idea of how to go about speeding up your queries.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

This topic is 4110 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this