Jump to content
Larry Ullman's Book Forums

Some Tips When It Comes To Mysql Queries


Recommended Posts

1. Always name your tables the same way:

There are best practice rules for naming tables. They should always be lowercase, split by underscore ( _ ) and named in plural. If you need to build tables for several purposes, (forums, shops, fruits) prefix them so the appear next to each other.

 

Examples:

forums_users

forums_posts

shops_products

shops_prices

shops_availability

 

2. Use normalization rules(!!!) when creating a structure for tables

Larry explains the different forms of normalization very good in this book. Read it thoroughly, UNDERSTAND it, and plan your tables well. The rules are really not that hard to understand, and will allow you to cross-reference tables in an easy way later on. It will make you understand how the data you are using are working. It will make your systems a lot easier to build on and to introduce new features. I promise you, THIS is how you become a wizard when it comes to working with data in several tables.

 

3. Use white space(!) and use UPPERCASE for mysql functions (See tip #4!)

When your queries becomes increasingly more complex, you should really follow these tips. To demonstrate, I will give you two versions of the exact same code:

 

CREATE VIEW view_goals_per_game AS

SELECT league.season AS season,
league.id AS league_id,
league.name AS league_name,
goals.match_id AS match_id,
clubs1.name AS hometeam,
clubs2.name AS awayteam,
players.id AS player_id,
CONCAT( players.fornavn, ' ', players.etternavn ) AS player_name,
goals.goal_time AS goal_time,
games.kickoff_time AS kickoff_time

FROM abc_players_goals AS goals

INNER JOIN cnk_soccer_games AS games ON ( goals.match_id = games.id )
INNER JOIN cnk_soccer_clubs AS clubs1 ON ( games.club1_id = clubs1.id )
INNER JOIN cnk_soccer_clubs AS clubs2 ON ( games.club2_id = clubs2.id )
INNER JOIN abc_players AS players ON ( goals.player_id = players.id )
INNER JOIN cnk_soccer_league AS league ON ( league.id = games.league_id )

LIMIT 0, 1000

 

create view view_goals_per_game as select league.season as season, league.id as league_id, league.name as league_name, goals.match_id as match_id, clubs1.name as hometeam, clubs2.name as awayteam, players.id as player_id, concat( players.fornavn, ' ', players.etternavn ) as player_name, goals.goal_time, games.kickoff_time from abc_players_goals as goals inner join cnk_soccer_games as games on ( goals.match_id = games.id ) inner join cnk_soccer_clubs as clubs1 on ( games.club1_id = clubs1.id ) inner join cnk_soccer_clubs as clubs2 on ( games.club2_id = clubs2.id ) inner join abc_players as players on ( goals.player_id = players.id ) inner join cnk_soccer_league as league on ( league.id = games.league_id ) limit 0 , 1000

 

Whick one would you like to maintain? :blink:

4: Save your queries in a text editor

Yes! It sound idiotic, right? It's not. Think of this happening: You accidentally delete, modify or overwrite a query you've used a lot of time on. It will save you a lot of time, tears and the life of a few keyboards! This is also leading up tip number 5.

 

5. Use views instead of customizing your dataset in PHP(!)

This is a real life-saver. Think of it as including ONE central PHP file instead on chancing 10 documents every time you make a change. The views should be written to display AS MUCH information as possible. Try to think of every scenario you may want to use the data. Views are, with a few exceptions, for displaying data; hence it's name.

 

NOTE: After the creation of a view, you CANNOT modify it. This is why you should follow step 4.

 

- Views are really easy to create:

[u][i]CREATE VIEW the_name_of_the_view AS[/i][/u]
SELECT *
FROM table1

 

The query used in tip #3 is a view. It would allow you to sort by a specific league, match_id, player_id or by kickoff_time. The view is used to display statistic about the goal scorer in a football match. (soccer for americans) The table for saving goal statistic has three rows(!). Match_id, player_id and goal_time. This is good data normalization, and minimizes redundancy and make for consistent data. That means the table players_goals need to be linked with other tables that holds data about the specific match and the specific player. This is the reason for create a view.

 

Don't see the point of this?

There really is one - I promise! By writing a view that is general and display a lot of data, I can write SIMPLE queries to get different results:

 

1. Last five goal scorers with name of both teams, player name and goal time

SELECT hometeam, awayteam, player_name, goal_time
FROM view_goals_per_game 
ORDER BY kickoff_time DESC

 

2. Display players with most goals in descending order

SELECT player_name, COUNT(*) as goals
FROM view_goals_per_game
GROUP BY player_id
ORDER BY goals DESC

 

3. Display all goals by a specific player

SELECT hometeam, awayteam, player_name, goal_time
FROM view_goals_per_game
WHERE player_id = 10
ORDER BY kickoff_time DESC

 

4. Find all goals in a specific match

SELECT hometeam, awayteam, player_name, goal_time
FROM view_goals_per_game
WHERE match_id = 837

 

Does it start to make some sense? Instead of repeating and modifying the same hard-to-grasp code over and over again (see tip #3), use a view to make your life simpler. :)

 

Hope this has helped someone. I have been thinking about writing a lot of different tips when it comes to MySQL. I've been thinking about writing a guide to joins for example. I really don't know if this interests someone or not.

 

Hoping others will also share tips and experiences when it comes to MySQL. :)

  • Upvote 4
Link to comment
Share on other sites

This is a really awesome post, and deserves to be stickied, if that's possible on these forums. This is a lot of sage-like advice from a very knowledgeable guy that has obviously learned through experience. I especially like the bit about views. Too true!

 

Even with the most simple sites, it really is amazing how taking the time to properly plans things at the beginning really pays off. Thanks for the post, Antonio.

 

I also agree with saving all your queries in a text file. This is especially true for when using stored procedures/prepared statements.

 

Anyway, thanks for this post, and I hope everyone reads it!

Link to comment
Share on other sites

Hello Antonio,

Thanks for sharing your excellent thoughts.

 

1. Always name your tables the same way:

There are best practice rules for naming tables. They should always be lowercase, split by underscore ( _ ) and named in plural. If you need to build tables for several purposes, (forums, shops, fruits) prefix them so the appear next to each other.

 

I agree that coming up with a consistent naming scheme is vital, and is true for the columns as well. I generally split by underscore, but when I do OOP, I don't. In non-OOP code I do some_var; in OOP I would do someVar. If the database is going to be interacted with from an OOP perspective, then naming the tables and columns consistent with OOP is better (in my opinion).

 

3. Use white space(!) and use UPPERCASE for mysql functions (See tip #4!)

 

I prefer to use uppercase for all SQL keywords, too. Just be careful not to put any white space between a MySQL function name and its opening parentheses.

 

4: Save your queries in a text editor

 

Very good tip!

 

5. Use views instead of customizing your dataset in PHP(!)

 

Another very good tip. VIEWS have helped me out of a lot of jams. In fact, I'm working on a project now where normalization created four tables that are hard to JOIN and the solution was not to de-normalize but to use a VIEW.

 

Thanks again!

Link to comment
Share on other sites

A question about views, since I've never used them:

 

Do they reside on the database side just like stored procedures? I ask, because I'm wondering if they create a persistent virtual table that can be quickly referenced, or if they have to re-perform the necessary joins everytime they are queried.

 

Thanks.

Link to comment
Share on other sites

Yes, exactly, VIEWS reside in the database and can be queried as if they were real tables. I believe MySQL will automatically update the VIEW when the data in the underlying query tables changes. I don't believe MySQL re-does the JOIN with every query of the VIEW, as that would defeat the purpose.

Link to comment
Share on other sites

When should you use them? I've read how Larry uses stored procedures to calculate distances to shops. But when should you really use them? What needs should you have to take them to use?

 

I started this thread to share tips and tricks here. Anyone else got something good to share? :)

Link to comment
Share on other sites

I specifically use stored functions in situations where very complex logic needs to be applied to column values, such as the mathematical calculation of distance on the earth based upon latitude and longitude. I use stored procedures for complex, multipart transactions and/or for improved security and/or for improved performance and/or for better separation of logic from presentation. I've written about this some in a newsletter:

http://www.larryullman.com/2011/07/04/what-is-larry-thinking-42-doing-what-i-do-part-3/#qa

 

Other things I would recommend would be to look into MySQL's storage engines and how it uses indexes. Understanding both goes a long way towards making the most of a database.

Link to comment
Share on other sites

Stored functions seems awesome!

 

Will check it out, as I have a query I REALLY need to improve on. The logic is not that hard in these calculation, but the query is not easily understandable.

 

Btw: Congratulation on finishing your new book for print, Larry. ;)

Checked for the amazon widget in the article, but I couldn't find one. Adblock. Sorry. :P

Link to comment
Share on other sites

  • 2 months later...

Trying to figure out a point about view:

 

As far as I understood, I can write a complex query in a view, which can generate large amount of data. And then, I can write a smaller query on that view? Am I correct? Please let me know.

Link to comment
Share on other sites

Antonio - regarding joins - go for it! This is a difficult concept to understand at first but once you start to get into them, the power is amazing, especially in a correctly normalized database. I also find the 'SQL' window in phpmyadmin very useful, where you can try out queries before including them into php texts.

Link to comment
Share on other sites

I just wanted to say thanks to Antonio Conte for his explanations about views. I hadn't understood their real use until then, and now I couldn't do without them!

 

@Max: have you tried views? When you have a query with lots of JOINs, a view is magic, because, once you've created it, you no longer have to refer to multiple tables but just to one. You just work hard once on your JOINs, and then you no longer have to think about them (except for saving the original query in a separate file, as Antonio Conte mentioned, just in case you have to recreate it one day).

Link to comment
Share on other sites

Hi Josee,

 

No, views are new to me, but look very powerful. Isn't there a danger of clogging up the server - presumably the view has to be re-created (or updated) - see Larry's post 23 August 2011 - 5:39 AM - with a large amount of data every time there is an enquiry?

 

Just one question - don't understand the bracketed u and i ..are they just some extraneous html??.........

 

CREATE VIEW the_name_of_the_view AS

SELECT *

FROM table1

Link to comment
Share on other sites

  • 4 months later...

[u][i]CREATE VIEW the_name_of_the_view AS[/i][/u]

SELECT *

FROM table1

 

I'm still on chapter 10 of the book. Are creating views addressed in later chapters? As easy it is to create as mentioned, I would like to learn about it in further detail. Ofcourse, finding it in this book is my first place of preference.

 

Thanks,

Mark

Link to comment
Share on other sites

 Share

×
×
  • Create New...