FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


What exactly does indexing mysql do?





ammonkc
I'm not really clear on what mysql uses an index for. I've always thought of the primary key as the index. What does indexing do in mysql?

also, do I need to set a Foreign key like I set primary keys? I've never really figured out how to designate a foreign key (if that is possible), but all of my queries and joins seem to work fine. will setting a foreign key or indexing my db's improve the performance of mysql or help to optimize my queries?
kv
indexing creates search indexes in the database. It will make your queries run faster when you search on the indexed field.

Foriegn keys are not required for joins. It is required only for enforcing referential integrity. When you have FKeys, you don't run into the problem of having a record point to a non existing record in other table or a record which is referenced by other records being deleted.

I don't think phpmyadmin has way to create FKeys, though mysql innodb tables support them. You can write sql to create them though.
ammonkc
that makes sense. Is it bad that I'm not using Fkeys in my schema? and how can I create Fkeys with something like MySQL Administrator? or yourSQL or something like that. or even a php script.

I also don't understand how to create an index. how does that go? should I just create an index for each field?
thanks for the help
Nyizsa
Indexing is useful with huge databases, and frequent queries sorted on something else than the primary key. For example you have an online game with a lot of users. The primary key is the userid. Now you want to add a "Hall of fame" page, where all of the users listed, ordered according to points. This query will run frequently. So, instead of doing the ordering everytime, just index your database on points, and schedule a re-index every midnight. This way the query will use the index and will be MUCH faster.
To put it simply: it is not neccesary for every database, only if the above circumstances apply. So don't worry, nothing's wrong with not indexing, but feel free to play with it if you're interested.
Related topics
How much time do u spend on computer?
Control or Freedom of Internet
Federal 'Hate Crimes' Bill Threatens Religious Freedoms
Does the php support utf8?
Candel Power
Open Content
form submit
land of the free uses torture
Strange Problem with apache
Does the mysql support GBK coding?
maybe i'm daft...
mySQL and external connections
How does this page work without javascript?
How does software communicate with hardware?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.