Best products from r/mysql

/u/chammit gave you the best answer. I'd like to expand on it so that you have a few ways to look at 'linking' tables.

I've worked on distributed order management systems and a bunch of other stuff. The basic relationship you need to understand is the many-to-many relationship. To use your example, a product has a set of attributes. One type of product is a screwdriver. A specific screwdriver may be in one and only one inventory location.

Screwdrivers can be in one or more inventory locations. In one sense, this sounds like one-to-many, but it doesn't scale. If we only had one product and could guarantee that we'd never have more than one product, the inventory location would just be another column on the product table. But this is not realistic.

Products may exist in one or more inventory locations.

An Inventory location may stock one or more products.

These two statements declare the many-to-many relationship. Many-to-many relationships are always resolved through a 'link' table that maps the PK from one table in the relationship to the PK in the other table. Each product in the product table might have one record in the link table for each store.

Which leads to this question: how many screwdrivers are in the Canadian store? Which leads to this: where should the model store the value for how many screwdrivers are in a store? That value belongs in the link table. So do pricing, the effective dates for sales, replenishment thresholds (time to reorder number), etc.

Here are some synonyms that I've run across in specific situations. This will help you understand what people are talking about. Just remember that all these tables resolve a many-to-many relationship, with one potential exception.

Here's the possible exception: Crosswalk table. This is used to maintain the mappings of one system's names for things to another system's different names for the same things. It's used in integration APIs, ETL for data warehouse and integrations feeds. It's more often the case that this table only has one mapping record for each term, to no many-to-many relationship exists, even though it could.

Other synonyms: bridge table, associative table.

Note that if you look at an invoice detail table, it is resolving the many-to-many relationships between several entities; product, customer, store, date. The same can be said for any detail table in a master-detail model, any child in a parent-child model. The phrase, 'master-detail', I believe is common in GUI design, while 'parent-child' is common in backend development. They mean the same thing from the point of view of the table structure.

Your modeling skills are off to a good start. This is because you asked the right question: "How many screwdrivers are at the Canadian store?" I attempted to share how I would break down your question to arrive at a modeling solution.

Edit: My recommended book: https://www.amazon.com/Data-Model-Patterns-David-Hay/dp/0932633749

The other posters have some great suggestions. Ditch the Unique index on ID but keep the Primary index, ditch the "searchloc" and "searchchunk" indexes in their current form, I don't think they will be working the way you think they will be.

Have you tried benchmarking the types of queries you want to use? Try filling the table with a few million random records and then run the types of queries you'll be using in your application. Then experiment with different indexes and see what works and what doesn't.

If you could give us a few examples of the queries you'll be using then it would be easier to suggest appropriate indexes.

Another thing you might want to consider is making the ID column an unsigned integer. I'm guessing you'll start the numbers in that column at zero and go up from there. You're wasting lots of space by allowing for negative numbers in that column and not using them.

The others are suggesting using InnoDB. Personally I think MyISAM is still useful in some applications, but whatever table type you choose, decide based on your application requirements, storage engine strengths and results of your benchmarking if appropriate. Don't just pick one and hope for the best.

One type of index you may want to consider would be a spatial index. You'd need to combing the x and y columns into the spatial column. Again, this suggestion depends entirely on the types of queries you plan to make. I believe InnoDB has just introduced spatial columns in a recent MySQL release, the MyISAM storage engine has that capability for some time.

If you only plan to query by 16x16 blocks then make a separate map of all the 16x16 chunks and give each of them a unique ID. Then add an extra column to your heatmap table called "chunkID" or whatever and index that. You may see much better performance if your index covers only one column. You can create a spatial index of the centroids and extent of all the 16x16 chunks if you need greater versatility or need to select groups of these chunks at a time.

Do you really need to index the "z" column at all? will you ever be querying things like "select foo from heatmap where z>150;"? If your where clauses won't include "z" then exclude it from your indexes. Again, it's hard to make suggestions like this without knowing what type of queries you plan to run.

One book that I found very useful for this sort of thing is this -> http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287 I got a lot out of it and saw huge performance increases after implementing some of the suggestions in that book.

The reason I'm saying all this is that I was recently in a situation where I had to manage similar data to what you describe, but my datasets had several hundred billion rows. My first pass was like your suggestion above, but it was useless above about 10 million rows and ground things to a halt. I realised the the indexes really did nothing when I did some benchmarking to find out why the queries were running slowly. My second pass used spatial indexes on the x,y column but the size of the index became way too big when the database grew to hundreds of millions of rows and I had to abandon that approach too. Eventually I partitioned the points into tiles, and made one table for each tile of points, then stored the extent of the tiles in a separate table and indexed the tile locations and extents using spatial indexes. I actually ended up with no indexes at all on the points tables at all (this was the surprising result of a lot of benchmarking). I found that MySQL was loading and unloading indexes in and out of memory so often that it was affecting disk performance. My database had hundreds of thousands of tables, but that didn't seem to affect performance, in fact it seemed to be optimal for our application. Your mileage may vary of course.

I've studied for the 5.0 Developer cert, and the 5.6 Developer cert, and I've also mentored about 100 students through these exams. Most/all of the students were new to database development, so I had to teach them from zero to Oracle Certified in the space of about 10 weeks.

To echo what /u/justintxdave said, the 5.6 Developer exam is tough. A lot of the questions are deliberately obtuse in my opinion and seem designed to catch you out. Plus they have questions on Java talking to MySQL, PHP talking to MySQL and Microsoft .NET talking to MySQL. This seems a little unfair to me - if you're a PHP developer, you're unlikely to know .NET and vice-versa.

The 5.0 Certification Study Guide is still a good resource, but you need to be careful to identify those things that have changed from 5.0 to 5.6 - e.g. DATETIME, TIMESTAMP, SQL MODES, and there are topics in the new Developer exam that were in the old DBA exam - transactions, foreign key constraints etc. The sample questions on the DVD of 5.0 book are a good practice tool.

Here's the list of topics I studied for the 5.6 exam, and what books/resources I used.

Study Guide refers to the 5.0 Certification Study Guide , and the relevant chapter numbers.

Head First SQL is a very basic beginners guide to SQL that you probably don't need.

I also used Paul du Bois's MySQL Developer's Library to fill in some blanks.

There is was a real need for an official 5.6 Certification book, but at this stage an 8.0 cert is probably just around the corner. I always had a notion I might write a 5.6 book, but life got in the way :)

Best of luck with the exam!

The Oracle training is outdated and irrelavant. The Percona training is up to date and very good. But both are aimed at DBA's, sysadmins, and application developers.

For your needs, you need to learn SQL, and learn to get useful information out of alien data sets.

Start with the basics:

• http://www.tizag.com/mysqlTutorial/
  
  You Can download some example databases:
  
  
• http://dev.mysql.com/doc/index-other.html
  
• http://chinookdatabase.codeplex.com/
  
  I suggest you buy a book called 'The Art of SQL':
  
  
• http://www.amazon.com/The-Art-SQL-Stephane-Faroult/dp/0596008945
  
  SQL is essentially math and programming. Having a good book for reference is highly recommended.
  MySQL workbench is a pretty handy tool. If you are going to connect to multiple databases (Oracle, MySQL, MS SQL, DB2 etc) then maybe look at a Navicat or RazorSQL license.
  
  For when you are feeling more confident with SQL, either look at learning VBA in Excel or Python and the PyData Libraries:
  
  
• http://www.amazon.co.uk/books/dp/1449319793

I started down this path about 12 years ago, and had a lot of luck with PHP & MySQL for Dummies by Janet Valade. It was good about teaching me the necessary pieces for coding basic PHP and having it speak to a MySQL database, which I was able to build off of. What the edition I used did not teach was how to properly sanatize strings or how to properly secure the DB, but you can learn security hardening separately.

Check your local library to see if they have a copy of it somewhere.

Best of luck!

Lots of other people have commented on specific MyISAM or InnoDB tuning options, but I haven't seen anyone post resources other than MySQL Manual or mysqlperformanceblog.com. I strongly encourage you to get the book High Performance MySQL (it is in 3rd edition). It is packed with real world examples and in-depth discussion of specific tuning models and various ways of re-architecting your database infrastructure.

Regarding your stall diagnosis, I recommend you look at the pt-stalk tool from Percona Toolkit -- among the 30 or so data points it collects, one is the output of SHOW ENGINE INNODB STATUS which will help you understand if there are any queries waiting on locks, or mutexes held, etc. Using pt-sift against the the stalk collection also provides a very simple overview of that collection period.

If you have a hard time understanding the output from SHOW ENGINE INNODB STATUS, or want a second opinion, contacting a company such as Percona is a viable option -- you could buy as little as 1hr of a Consultant's time to review your pt-stalk collection and talk about options for improving performance.

In general though: if you don't know what a variable does, don't change it. Defaults are normally sane. Perhaps start with a configuration generated from Percona Online Tools and then adjust variables as needed, specific to your workload and hardware.

Disclaimer: I work for Percona.

I was kind of thrown into MySQL for trial by fire. The company that I worked at as an Operations Engineer needed someone to focus on the databases. I volunteered and, through learning as I went, I picked up a lot.

I recommend a few things. First of all, pick up High Performance MySQL. It is a great read and will give you some very useful insights as to how MySQL operates internally. I think I have 4 or 5 copies sitting around the house.

Next, download the Sakila database. Poke around in there and see what you can find. Google "Sakila Database Exercises" and I'm sure you will find a lot of exercises to do on that dataset.

Personally, I learn best two ways. First, by trial and error. I have to get my hands in a system and try things to see what works, how it works and so on. Next, I learn from other people. Don't be afraid to google and ask about things. There are TONS of resources out there to help people learn mysql.

Great, thanks. A few days ago I asked on here about recommended MySQL books, one of the ones suggested was Bill Karwin's SQL Antipatterns. I've been reading through it and one of the topics discussed were intersection tables so I've been trying to put what I've learned into practice.

The book all MySQL DBAs should read: http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287

As DBAs tend to do a lot of scripting/coding, its possible your friend has a favorite text editor/ide they use, you may want to get them a license for that. A video game (if they like them, which they likely do). Something that will keep the away from a computer.

Funny teeshirts: http://www.zazzle.com/mysql+tshirts (not saying these are all funny, but some may be)

Certs are there to show that you have a good grasp of certain concepts, not as learning tools.

Something like O'Reilly's High-Performance MySQL book would be a great way to get the knowledge part down, but nothing beats hands-on experience.

Hey, I'm a MySQL 5 Certified Developer.

All I did was I got the textbook, took notes, downloaded and installed MySQL, and practiced.

http://www.amazon.com/MySQL-5-0-Certification-Study-Guide/dp/0672328127/ref=sr_1_1?ie=UTF8&qid=1377799467&sr=8-1&keywords=mysql+certification

Hope that helps.

For the lazy: https://www.amazon.com/_/dp/1449314287

The book is good but now a bit outdated.

It's due out in April:
http://www.amazon.com/MySQL-Database-Administrator-Guide-1Z0-883/dp/0071775781/

http://www.amazon.com/MySQL-Database-Administrator-Guide-1Z0-883/dp/0071775781/ref=sr_1_1?ie=UTF8&qid=1404838226&sr=8-1&keywords=Exam+1Z0-883