Jump to content
Larry Ullman's Book Forums

Rationale For Keys


Recommended Posts

I`m looking at the tables that are being created on page 169 of teh book.

 

In each table, Larry creates a Primary key(id) and also usually some other keys. For example in the carts table

 

Key 'product_type' ('product_type`, `product_id),
Key 'user_session_id' ('user_session_id')

 

I understand the syntax of these keys (thanks to another thread on the forum) but I don`t quite understand why these keys are created, and, conversely, why other keys were not created.

 

What wouldn`t you be able to do if these keys weren`t created?

 

Are these keys necessary for subsequent SQL statements, like a join?

 

I`m asking this so that, when I hopefully/eventually create my own MySQL databases, I will know what situations will require creation of extra keys.

 

Thanks if you can help

Link to comment
Share on other sites

Keys basically allow you to more quickly search on a given column.

 

I hear that it's best to create keys for columns that are commonly searched through. Of course, there is a tradeoff to having too many keys (performance-wise), so I suppose the art is having just the right amount of keys in the right columns.

 

Sorry I can't be more helpful, but I really don't know.

 

If you do some Googling and check out the article about keys on the MySQL home page, I think you'll have more than enough information.

Link to comment
Share on other sites

Keys, or indexes, improve the performance of queries. I talk about specific recommendations in my PHP & MySQL book and in my newsletter. You can also find out more by searching online or using the MySQL manual. The presence or absence of keys do not prevent you from doing anything.

Link to comment
Share on other sites

@Larry I will do more reading up on it as you suggest, but I wonder if you might detail a specific example

 

I have another post on this forum (Post where I ask how

 

ON s.id = sc.size_id

 

limits the results of the query from 30 to 10 with no duplicates. Looking at these tables, I see that the SC table uses a key for size_id.

 

If you have time, and inclination to answer that other post (someone has tried unsuccessfully so far), could you also explain, using this example, how that query`s performance was enhanced using the key size_id? For example, was this key necessary to help avoid the duplication in results that I describe in that post?

 

Thanks if you can help. As I am sure you are aware, it is quite a leap (at least for me :)) from reading the theory to understanding the more complex examples.

 

Cheers all.

Link to comment
Share on other sites

@Larry I will do more reading up on it as you suggest, but I wonder if you might detail a specific example

 

Yeah, a lot of what you're asking about is fundamental SQL/MySQL, the kinds of things this book assumes you already know. You seem to be doing well enough with the material, but for what it's worth, these aren't the kinds of questions I was expecting from this book.

 

If you have time, and inclination to answer that other post (someone has tried unsuccessfully so far), could you also explain, using this example, how that query`s performance was enhanced using the key size_id? For example, was this key necessary to help avoid the duplication in results that I describe in that post?

 

No, as I wrote previously, keys have no impact on the results of a query, just how efficiently those results are garnered. The duplication is avoided by the SQL command itself. With the key on size_id, the size_id in the one table will be indexed as a primary key. In the other table it's a foreign key. As the size_id will be the basis of a JOIN, MySQL will perform that query more efficiently if both columns used as the basis of the JOIN have indexes upon them.

 

An index, in case it's not clear and in case you haven't looked at the MySQL manual (or online) yet, is just a way of telling the database to pay attention to a column (or combination of columns). So an index on size_id (as a foreign key), is a way of saying "Pay attention to these values as I'm going to use them in later queries."

 

Again, the index (or key) is part of the table definition. It does not impact the results of a query*, just the efficiency of executing that query.

 

(* There is an exception in that a FULLTEXT index is required to perform a FULLTEXT search query, but I think that's the only exception).

 

 

Link to comment
Share on other sites

MikeMikeMike, I think both Matt and Larry have done a good job of explaining things to you, but I also realize that the questions they're answering are not the ones you are asking (or at least, I think that's the case). Please let me clarify.

 

What you're saying is that the following SQL query results 10 non-duplicated results, right?

 

SELECT gc.description, gc.image, CONCAT( "C", sc.id ) AS sku, CONCAT_WS( "-", s.size, sc.caf_decaf, sc.ground_whole, sc.price ) AS name, sc.stock 
FROM specific_coffees AS sc 
INNER JOIN sizes AS s ON s.id = sc.size_id 
INNER JOIN general_coffees AS gc ON gc.id = sc.general_coffee_id 
WHERE general_coffee_id =3 
AND stock >0 
ORDER BY name ASC 
LIMIT 0 , 30

 

However, if you remove the ON s.id = sc.size_id part, then you end up with 30 duplicate results, and the following query, right?

 

SELECT gc.description, gc.image, CONCAT( "C", sc.id ) AS sku, CONCAT_WS( "-", s.size, sc.caf_decaf, sc.ground_whole, sc.price ) AS name, sc.stock 
FROM specific_coffees AS sc 
INNER JOIN sizes AS s
INNER JOIN general_coffees AS gc ON gc.id = sc.general_coffee_id 
WHERE general_coffee_id =3 
AND stock >0 
ORDER BY name ASC 
LIMIT 0 , 30

 

And your question is why, right? As far as I can tell, the second query (without the ON s.id = sc.size_id part) is not a valid query. If you boil it down to the bare essentials, you have the following:

 

SELECT stuff FROM tableA INNERJOIN tableB

(more inner joins)

 

However, this is not valid. I think what is happening is the the SQL query parser (most likely the one in phpMyAdmin) is attempting to make sense of your query, but since it doesn't know what to join on, it's joining on all possible permutations, thus giving you duplicate results.

 

Actually, the truth is, I don't know the answer to your question, but it's an interesting question, and if I can find some time this evening, I will look into it more thoroughly and try to find you an answer.

 

I do suspect though that the issue is with the fact that you're not supposed to omit the ON clause in the query.

 

Edit: You're also getting 30 results, because you've limited the results to 30 via your query. In truth, there may be even more results. You may want to try removing the LIMIT clause from your query, and seeing how many results there really are.

 

Edit #2: Both Larry and Matt are right as well. Indexes (keys) do NOT affect your results. They affect the speed with which the query can return results. Really, you should read the section in the MySQL manual about keys (as both Larry and I suggested). Foreign keys are used to help speed the database up in retrieving join results across multiple tables.

  • Upvote 1
Link to comment
Share on other sites

Thanks for your help, HartleySan. I just answered his other post in more detail. Your answer here is the same thing Matt was saying and is also correct. This is basic JOIN stuff and the ON (or USING or WHERE) clause is required to enforce matching records when joining multiple tables.

Link to comment
Share on other sites

@Hartley,

 

With respect, I disagree that Matt was correct on the other thread.

 

Using Larry`s answer on that other thread, I show why Matt`s answer was incorrect. Matt was correct about the basic theory (which I wasn`t asking) but when He tried to explain the result, Larry`s answer shows he was 100% incorrect.

Link to comment
Share on other sites

Well, we all have different ways of understanding things, but I read Matt's post and understood it. I agree that he didn't spell it out 100% for your specific example, but all the information was there. His explanation of the Cartesian products was right on the money, and is exactly what you are experiencing.

 

Anyway, I'm not here to accuse or defend any one person, but I do think you were a little out of line to tell Matt he's wrong, when he was anything but. Also, these boards are a privilege, not a right, and I think it goes without saying that (just like anything else in life), if you have a bad attitude, people are less likely to help. Perhaps that wasn't your intention, but from my perspective at least, in reading your post and the wording you used, you did sound upset and were rather rude.

 

There's a difference between saying someone is "wrong", and disagreeing with them/asking them for further clarification. I'm sure Matt would have continued to help you out as best as possible to get to the answer you were looking for.

 

And as the book states on the back cover, this is not a beginner's book, which means that there is a lot less hand-holding and examples. The Internet is full of lots of great examples about how joins operate. Please don't make me Google the information for you, just to prove how easily it can be obtained. And, of course, there's no replacement for good ol' practice and self discovery.

 

If you had played around with the SQL queries some more and combined that with some online resources, you probably would have figured out what was going on. The way you made your posts though, it really seemed like you made no effort to take the time to very carefully consider what was being said by everyone before quickly posting a rude response.

 

All said and done, let's just call it water under the bridge, eh? In the future, we will all try to help you, but as my mom always used to say, "You get more flies with honey than vinegar."

Link to comment
Share on other sites

This was actually answered in the book and in several threads in the forum.

 

My understanding of indexes in MySQL is that anytime you are going to retrieve information from a table column such as using a WHERE clause in your SQL statements you should index that column. It saves time is the reason. Open up one of the many programming books available, especially the ones in the 800 to 900 page range, if there was no index or table of contents and you needed to know a specific thing, how would you find it? Read through every page and every word until you come to the part you need? What if it is on page 759? are you going to start at page 1 to begin your search?

 

Indexing allows MySQL or any database software to find things easier. It will still be found if you don't index, computers can go through information quicker than humans, but it may take longer than it would if there was a bookmark to the location.

 

Primary indexes are for table columns that lead what information is in the table, such as a record_id. Primary indexes columns need to hold unique content such as record id's where there is no duplicates. I have one table in a project database of mine that has no unique columns so I can't use a Primary index but since I use each column at some point in a WHERE clause each column in this particular table is set as an index.

 

I am not expert in SQL, I understand enough about joins that they bring two tables or seperate databases together and allow you to query the data, that's it though. I use a 3rd party tool to actually create complex SQL for me because I don't know how to do it myself or it would just take too long. I believe there is a QRY builder built into PhpMyAdmin, I haven't really used it much though.

 

 

 

 

I`m looking at the tables that are being created on page 169 of teh book.

 

In each table, Larry creates a Primary key(id) and also usually some other keys. For example in the carts table

 

Key 'product_type' ('product_type`, `product_id),
Key 'user_session_id' ('user_session_id')

 

I understand the syntax of these keys (thanks to another thread on the forum) but I don`t quite understand why these keys are created, and, conversely, why other keys were not created.

 

What wouldn`t you be able to do if these keys weren`t created?

 

Are these keys necessary for subsequent SQL statements, like a join?

 

I`m asking this so that, when I hopefully/eventually create my own MySQL databases, I will know what situations will require creation of extra keys.

 

Thanks if you can help

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...