Been on a course - brushing up on my SQL with the rest of the department and finding out some very interesting little tit bits which will speed things up at head office no end. I had always considered for example that stored procedures were really an organisational tool designed to reduce duplication of queries on the client side. Interestingly it turns out that they carry a good improvement in performance because the query is essentially precompiled and all the execution planning and checking is already done. Could make for a busy Monday :o)
Having got home I decided to look at some of the details of the MySQL databases which are less well used than the Microsoft SQL databases, and definitely less critical, but still in need of efficient design. While reorganising things a little I found out a couple of slightly less well documented issues with creating foreign keys on a MySQL database which is already in use and therefore carrying a little baggage shall we say.
A couple of things seem to cause problems and errors 105 and 121 seem to crop up a lot
1. ensure that columns to be associated are of exactly the same type, for example a column of type INT will not be forced into a liaison with a column of type INT(10) unsigned.
2. Ensure that all columns will match exactly, for example if one column has a NULL entry it is not going to conform to the relationship and it will ruin your evening.
That's just a couple of things, I'll keep jotting about whatever else drops out of the woodwork. Next week I will post the first installment of the main event - taking our main MS SQL databases to demon tweaks - exciting stuff :o)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment