Mysql Keys/Index

stryker1080

Hi i just started programming PHP with Mysql and i am able to make some fairly simple databases. I was reading up on setting Keys (primary etc...) but i dont seem to understand how i could use these. I know that if you want to search for example 'stryker' in a table with username colum and if there was about 1,000,000 entries it would take long using :

mysql_query("SELECT * FROM users WHERE username='stryker'");

I read that you can set you table col's to primary keys or indexes. But is there anything special you have to do to make it search faster? If i set my username colum to be a index, would it find it right away? THe book explained it that it would be faster because its like an index, it looks up where that username is rather than going through each entry and checking... So basicaly the question is, if i just set my username column to index do searches become faster automatical (but at cost of space...) ?

silversurfer

If an index makes your search faster depends on the amount of data you want to store in that table and on how often this column is used in your querys.
So if you have many users (~>5000) and you search very often for a username you should use an index. This will need some space on your disk, but this will only be around some KB for small tables.

You can test the performance with and without an key, by stopping the time the query needs to finish.

What's more important, is that you have an unique key in the user table e.g. the usrid. When you say, that this column is a primary key, mysql will automatically set an unique index on it.

I would not use the username as the primary key, cause this will force you to show it in links and other places on your page.

Also have in mind, that each time you update the column or insert/delete a new/old row the indexes on that table needs to be updated too (what is done by mysql). This could take some time, if the table is big (~>1.000.000).

Hope could help.

silver...

stryker1080

When you add stuff to the table doe things get automaticaly added to the index? How do i make this index, im using phpmyadmin to control most of the sql stuff like managing the database. Do i just set my username column to be index, and it will automaticaly makes indexes for whatever is in my username column?

silversurfer

The index is completely managed by mysql. The only thing you have to do is to add one to the column/s you think they need one.

With the phpMyAdmin this is normaly done by looking at the table structure an the clicken on one of the links on the left of each column. You se waht will be this link do, by holding the mouse over the icon for an short moment. Just add the index and thats all. You don't have to care about the rest. Mysql is doing it. With phpMyAdmin you can also easily see the size of the index. The summarization of the space actually needed by this table is also shown in the view of the structure.

silver...

stryker1080

Ok awesome thanks alot 🙂 Just one last question. Whats the diff between primary, unique and index. Index would be better for searches, unique makes it so that each field has to be diff but what does primary do? What did you mean that you can see the primary in all the link?

silversurfer

Ok,

an index just makes your searches faster and improves your database performance when searching in big tables. Unique is usefull, when you what to make sure, that the values in the colums, or the combination of values from diffrent rows are unique as well, e.g. the username or email. The primary key identifies each row in a table, e.g. the usrid or personalid of an employee. E.g. the combination of forename,surname,street,streetnumber,zip,city and country would identify a person, but this key would be to big in an seach clause. You would always have to perform a searches where you have to aks for all these criterias. For that reason you normaly invent the abstract primary key "personid" or how ever you will call it.

To explain the whole thing would take to much time. If you want to have more information about it, search for database normalization at google or your prefered search engine.

About the visibilty of the primary key: Sometimes you need links like ?...&someprimarykey=valueforthiskey.
If the key is the username you have the problem, that you bring the username to the frontent(browser). The links an data are normally cached on the client computer, so the username in your links will be cached also. If the user, that username belongs, logs out and leaves the computer how it is, the follwing person could read the username just out of the link and probably, if the first one has choosen an very password, log into your system. For that reason you should prevent in first line to show primary keys in the frontent. E.g. using Sessions. But if you can't prevent, it try to never show information that can be used directly on your site(Login,etc.). For that reason you should use the userid instead of the username in links and therefore you need the an additional primary key for an user.

silver...

stryker1080

Thank you 🙂