HWZ Forums

Login Register FAQ Mark Forums Read

Xampp mySQL Primary Key, Unique Key, Index Key.

Share This Page
HardwareZone.com on Facebook
Reply
 
LinkBack Thread Tools
Old 03-03-2012, 04:24 AM   #1
Senior Member
 
Join Date: Mar 2009
Posts: 721
Xampp mySQL Primary Key, Unique Key, Index Key.

Primary key no need to explain.
But what is unique key and index key?

I did a google and everything that comes up just beat around the bush, beat here... beat there... beat somemore... No simple english.

I just want to confirm.
If I have a table with 2 keys, and the keys are Primary key in another table.
This 2 keys in this table should be Unique key right?
OlympicDreamer is offline   Reply With Quote
Old 04-03-2012, 11:33 PM   #2
Master Member
 
silver09's Avatar
 
Join Date: Aug 2000
Posts: 4,267
Primary Key is unique and to identify.

Unique is happens only once in the table but not used to identify (business rules)

Index key? You mean index? So that the DB system can use this column to quickly look for data when query based on this column.
__________________
Freelance Programmer for hire.
silver09 is offline   Reply With Quote
Old 09-03-2012, 03:32 PM   #3
Junior Member
 
Join Date: Dec 2004
Posts: 71
Index is design to speed up the search results, so at anytime, when you use the "WHERE" in your mysql statement, if Mysql know that column has been indexed, it will load the whole rows of value (you need to specify which column to be index) into the memory and search/retrieve for similar keywords compare to lookup through million of rows on a slow harddisk (HDD/SSD). (It's similar to Windows search which your C:\ can be indexed so that it will speed up the search result rather than scan through your harddisk sector by sector)

Unique key mean think of it as our "NRIC" or "User email" columns must contain strictly unique value, when the user is trying to submit a form with a duplicated value where that column has already insist on accept only unique value, it will be rejected by MySQL rule and notify an error message to the user.

Do consider read this as well.
http://www.xaprb.com/blog/2009/09/12...-key-in-mysql/

In many case, NOSQL database is popular and use by many popular website, do consider MongoDB for storing in document structure and proven to perform much easier and better than MySQL. It will benefits you when you know how to manage the most advanced HadoopDB which is used by Google and Facebook for storing very large data.

Last edited by yanlilei64; 09-03-2012 at 04:14 PM..
yanlilei64 is offline   Reply With Quote
Old 23-04-2012, 12:53 AM   #4
Senior Member
 
davidktw's Avatar
 
Join Date: Apr 2010
Posts: 2,416
Take note, my pointers here are not to challenge or revoke any of the answers that were given. Rather I would hope to contribute by shedding more light into the matter, and if possible, educate some whom is plague by this issue. For completeness, go take up a database management course offered in your institute. It will discuss on the basic of relational database and how they works.

Index is design to speed up the search results, so at anytime, when you use the "WHERE" in your mysql statement, if Mysql know that column has been indexed, it will load the whole rows of value (you need to specify which column to be index) into the memory and search/retrieve for similar keywords compare to lookup through million of rows on a slow harddisk (HDD/SSD). (It's similar to Windows search which your C:\ can be indexed so that it will speed up the search result rather than scan through your harddisk sector by sector)
To be more precise, how database works is query string is parsed and compiled into a query tree. Using this query tree, database optimize by re-ordering the query without changing the original semantics and access index instead of performing table scans which is costly. There are unique indices and/or partial indices. There are also functional indices which invoke functions or procedures in the database which compute complicated indices on the fly. Refer to the database you are using for more details.

The key in indexing is they are relatively small compared to the data table. Given sufficient memory to the database, it is possible to completely cache all the indices in memory for fast access, hence a lot of times, large quantity of memory is more beneficial than fast storage system for database, because database are mostly read instead of write. Index are stored as B-Tree in the memory. If you have studied data structure, you will knows binary tree requires logarithmic time complexity in all its operations. This means a 1 million entries of index will take at most log2(1 million) =~ 20 operations to find it.

Even large indices can be big, hence to further minimize the size of indices, database design can opt for partial index where only certain portion of the column is indexed instead of the whole value. This slow down a search a tiny little bit, but does save a lot of memory for other indices to be fitted in the memory. For example, for some reason, you decided to store "TRUE" and "FALSE" in the column, this is a bad candidate for index, but it can be better if you just index the first character. It cardinality of the index did not get smaller because you index just the first character, but it definitely save a lot of space.

In many case, NOSQL database is popular and use by many popular website, do consider MongoDB for storing in document structure and proven to perform much easier and better than MySQL. It will benefits you when you know how to manage the most advanced HadoopDB which is used by Google and Facebook for storing very large data.
We see a lot of NoSQL spring to live recently in the database whole. I read an article awhile ago, mentioning that NoSQL has been around before RDBMS was made possible by Codd in 1970, but RDBMS took over as the de-facto standard for a long time and still is now.
As I found, it is also iterated in the wiki found @ NoSQL - Wikipedia, the free encyclopedia

While I don't agree NoSQL is better than RDBMS, I do agree there are substantial benefits from using it. I see some good usage where it is structural yet not strictly adhering to schemas. I also see that NoSQL with the absence of ACID behavior brings better writing efficiency. One good usage is NoSQL as a logging database where search can be easily achieved later.

But don't go with the flow just because it's popular. You have to know what your data is and how they are utilized. Don't just use the more popular tool and in the end you need to twist and turn your applications to keep up with its deficiencies. Some NoSQL can't even guarantee you read-commited isolation, at which the responsibility has to be shifted up the stack to be handled by application. It can brings about more complexity in the application, because you have chosen an inappropriate database system.

I believe HadoopDB and such similar database are not your common database. They do not entirely fit into the common workflow that you see in web applications. I believe HadoopDB are around to facilitate the Hadoop distributed computing workflow which are large map and reduce operations. I could be wrong though. But there are distribution and aggregation workflows which are much different from your usual web applications.

I believe Facebook main storage is still on MySQL, much modified for their usage. They have contributed the changes back the community, but I'm not so sure they are suitable unless you operate at Facebook scale. Fast for Facebook can be slow for you, because their changes I would believe is for scalability. Large scale performance have very different parameters versus a small scale load, so don't take it as-is.

For good database design, it goes further than just a single system. You have to consider how to architecture a series of databases. Would you use clustering or use replication design. Would you go for synchronous replication or asynchronous replication for better performance but possible out-of-sync situation. Given an asynchronous data update, how would your application cope with such behaviour.

I recently watched this video @ YouTube. I believe it will be wise to share with your guys. It is found @ Scalability at YouTube - YouTube

You would be able to pick up some good advice there.

"Consistency and Scalability don't goes well together"

If you need to "cheat", perhaps it would be wise that a user who submit the data gets to see it immediately, but others can see it later. Others wouldn't know it has been submitted until it's shown right ? But the submitter will feel something is wrong, if the data doesn't show up on the next refresh. I would place this under a smart way to store data in the session and the database too You think about it .
davidktw is offline   Reply With Quote
Reply
Important Forum Advisory Note
This forum is moderated by volunteer moderators who will react only to members' feedback on posts. Moderators are not employees or representatives of HWZ. Forum members and moderators are responsible for their own posts.

Please refer to our Terms of Service for more information.


Thread Tools

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On