I came across this a little while ago; it’s a good little presentation with some interesting points I’d not considered before.

http://www.slideshare.net/techdude/how-to-kill-mysql-performance

If you’re an Anchor customer, I should point out that the ARCHIVE storage engine isn’t available in Redhat’s version of MySQL, which is a damned nuisance.

I’ve searched far a wide and all I wanted is a MySQL database schema that incorporated all the common relationships that tie into a relational database i.e one to one, one to many and many to many but haven’t had much luck.

So here you go a straight to the point post that will give you:

• a fully functional MySQL databse schema along with inserted dummy data
• a relational database with common relationships such as one to one, one to many and many to many
• a list of example select queries to use to see how you can create table joins between tables

To download the database please click here

Diagram Of The Database
The following diagram will help give you a good visual of all the tables in the database and what relationships are in place:

Select Queries
So you have got all your information in different tables (relational database) and you need to perform table joins. Below are a list of the most common types of table joins using the different relationship types one-to-one, one-to-many and many-to-many:

One-To-One

SELECT dog.id, dog.name, rfid_dog.bar_code AS rfid
FROM dog,rfid_dog
WHERE dog.id = rfid_dog.dog_id
ORDER BY dog.name ASC;


One-To-Many

SELECT dog.id,dog.name,breed.name AS breed
FROM dog,breed
WHERE dog.breed_id = breed.id
ORDER BY dog.name ASC;


Many-To-Many

SELECT breeder.name,breeder.address,breeder.phone,breeder.email,breed.name AS breedName
FROM breed,breeder,breed__breeder
WHERE breed__breeder.breed_id = breed.id
AND breed__breeder.breeder_id = breeder.id
ORDER BY breeder.name ASC;


And just like that you now have your head around relational databases!

If you have come to this point and your still excited and want to learn more please see our wiki article which goes into more detail about relational databases plus talks about optimisation techniques to speed things up.

I was setting up mysql-server for a customer recently and noticed something interesting – there’s a helpful script included with mysql called mysql_secure_installation. We thought about that for a moment and had a chuckle. Okay, that was a little unfair; it’s no secret that we prefer to use Postgres wherever possible, but the idea of having a “make it all secure” script isn’t too bad an idea, as long as it doesn’t produce a false sense of security.

(more…)