[UPHPU] MySQL questions?

Mac Newbold mac at macnewbold.com
Tue Apr 27 12:54:52 MDT 2004


Thanks to everyone for the great response. I'll probably only have time to
go in depth on the top 3-4 suggestions that people wanted to hear about,
but if I can, I'll try to get material together on all of it, so that even
if we don't get to it in the meeting, the material is around somewhere for
people to look at.

The topics that will make up most of the discussion are (in order by
number of people interested):

Votes   Topic
10      Optimizing - server itself and queries (incl. indexes)
6       Advanced SQL - JOINs (incl. inner/outer joins)
5       Database design

Other stuff I'll try to cover in less detail or in shorter time:

4       Database Table Types and usages
4       Install/run/admin - esp. w/apache+php (incl. ip access/user perms)
4       Importing/exporting, putting data in/getting data out (LOAD/OUTFILE)
5-2     MySQL Replication (Clustering / High Availability / Scalability)
3       Caching MySQL Queries
2       Security Concerns (partly covered on list)
2       Fine grained (table/row) access control

I know the 3.23.xx line best, so if someone else knows the 4.x line, I'll
welcome your comments and input. (I haven't migrated any of my servers to
4.x yet since it requires dumping and reloading all the databases.)

If any of you have other references I should be looking at or that others
might find helpful, please pass them along.

References, and areas I'll focus on:

#1: The MySQL Manual - I've found it to be up to date, accurate, complete,
and very detailed. You can view it online at www.mysql.com , or you can
download it in PDF (3.5MB) or HTML. It's about 1250 pages (30 page Table
of Contents!), so don't try and print it unless you _really_ mean it. You
can also buy it from Barnes and Noble.

Optimization:
MySQL Manual, Chapter 7 "MySQL Optimization"

Advanced SQL: JOINs, etc.
MySQL Manual, Section 14.1.7.1 "JOIN Syntax" + 14.1.7.2 "UNION Syntax"
MySQL Manual, Section 14.1.8 "Subquery Syntax"
 - 14.1.8.11 - How do do subquery stuff without real subqueries
MySQL Manual, Chapter 19 - Stored Procedures and Functions (in 5.0)
Triggers (coming in 5.1) see 1.8.5.4

Database Design:
I'll work mostly from "Database System Concepts" (Silberschatz, Korth,
Sudarshan, 3rd or 4th edition) and from lecture notes from the UofU's
Databases class, taught by Prof. Gary Lindstrom (my bias - it's the class
where I learned a lot of this). The lecture notes can be found at:
http://www.cs.utah.edu/classes/cs5530/schedule.html

Database Table Types:
MySQL Manual, Section 15.1 MyISAM, Chapter 16 InnoDB
(I'm not planning to talk about MaxDB, since it's a whole different engine)

How to Install/Run/Administer a MySQL server:
Ch2 "MySQL Installation", Ch5 "MySQL Database Administration"
http://www.devshed.com/c/a/MySQL/The-MySQL-Grant-Tables

Importing/Exporting, and Getting Data in and Out:
Section 14.1.5 "LOAD DATA INFILE Syntax"
Section 14.1.7 "SELECT Syntax" (SELECT INTO OUTFILE)
Section 8.8 "The mysqldump Database Backup Program"
Section 8.10 "The mysqlimport Data Import Program"
Command-line data import/export methods

Replication:
Ch 6 "Replication in MySQL"

Caching MySQL Queries:
Section 5.10 "The MySQL Query Cache"

Security Concerns:
Section 5.3 "General Security Issues"
http://www.php.net/manual/en/security.database.php
http://www.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc
http://www.php.net/manual/en/function.get-magic-quotes-gpc.php
http://www.php.net/manual/en/function.addslashes.php
http://www.php.net/manual/en/function.mysql-escape-string.php
http://www.php.net/manual/en/function.mysql-real-escape-string.php

Fine grained (table/row) access control:
Section 5.5 User Acct. Maintainance, Section 14.5.1 Acct. Mgmt. Statements
http://www.devshed.com/c/a/MySQL/The-MySQL-Grant-Tables

Basics:
A good place to start is the MySQL Tutorial, Chapter 3 in the manual
You can also google for SQL tutorials and get a bunch of decent ones.

Thanks,
Mac

--
Mac Newbold		MNE - Mac Newbold Enterprises, LLC
mac at macnewbold.com	http://www.macnewbold.com/



More information about the UPHPU mailing list