http://learning little abot MYSQL :
What is MySQL?
MySQL is a SQL (Structured Query Language) database server. SQL is the most popular database
language in the world. MySQL is a client server implementation that consists of a server daemon
mysqld and many different client programs/libraries.
The main goals of MySQL are speed and robustness.
The base upon which MySQL is built is a set of routines that have been used in a highly demanding
production environment for many years. While MySQL is currently still in development it already
offers a rich and highly useful function set.

See the ‘CREDITS’ file in the distribution for persons that have been involved in the MySQL
project.
History of MySQL
We once started off with the intension to use mSQL to connect to our own fast low level (ISAM)
tables. However, after some testing we came to the conclusion that mSQL was not fast or flexible
enough for our needs. This resulted in a new SQL interface to our database but with almost the same
API interface as mSQL. This API was chosen to ease porting of third party code.
It is not perfectly clear where the name MySQL derives from. Our base directory and a large amount
of our libraries and tools have had the prefix ’my’ for well over 10 years. However, Monty’s daughter
(some years younger) is also named My. So which of the two gave its name to MySQL is still a
mystery, even for us.
The main features in MySQL
Multi-threaded.
C, C++, JAVA, Perl, Python and TCL API’s. See section MySQL client tools and API’s [p 81]
Lots of column types like: signed/unsigned integers 1,2,3,4,8 bytes long, FLOAT, CHAR,
VARCHAR, TEXT, BLOB, DATE, SET and ENUM types. See section Column types. [p 31]
Join optimiser with one-sweep multi-join (all joins made in one pass).
Full function support in the SELECT and WHERE parts. Example: select
column1+column2 from table where column1/column2 > 0
SQL functions are implemented through a very optimised class library and should be as fast as
they can get! Usually there shouldn’t be any memory allocation at all after the query
initialisation.
Full support for SQL GROUP BY and ORDER BY. Support for group functions (SUM, MAX and
MIN).
A privilege and password system with is very flexible and secure. Allows host based verification.
All password traffic on the net is encrypted.

What is the current MySQL version?
You can always check http://www.tcx.se/ for the latest version of MySQL.
How do I get MySQL?
On the Internet, try using a web browser to http://www.tcx.se/
Or ftp to ftp://ftp.sunet.se/pub/unix/databases/relational/mysql/. That isSunet’s (Swedish University
Network) FTP archive in Sweden.
7
Should I get MySQL in source or binary distribution?
If you want to read (and/or modify) the C and C++ code that makes up MySQL you should always get
a source distribution. The code is always the ultimate manual. The source distribution also contains
more tests and examples than the binary distribution.
For most people who want to run MySQL on a platform that has binary releases, a binary version of
MySQL is more convenient. However, TcX’s binary release is not compiled in the same way as the
source release so there are some differences in where support files are located.
Which operating systems does MySQL support?
We use GNU autoconf so it will be possible to port to all modern systems with working Posix threads
and a C++ compiler. The client code requires C++ but not threads. We use the software ourselves
primarily on Solaris (currently 2.5.1) and some on RedHat Linux 4.2 (kernel 2.0.30).
A working Posix thread library is needed for the server. On Solaris 2.5 we use SUN PThreads (the
native thread support in 2.4 and earlier versions are not good enough) and on Linux we use Linux
Threads by Xavier Leroy @email{Xavier.Leroy@inria.fr}.
A good web page about different thread implementations is http://www.humanfactor.com/pthreads/.
The MySQL distribution includes a patched version of Provenzano’s Pthreads from MIT (see
http://www.mit.edu:8001/people/proven/pthreads.html) in thedistribution. This can be used for some
operating systems that does not have posix threads.
We have also tried to use another user level thread package named FSU Pthreads (see
http://www.informatik.hu-berlin.de/~mueller/pthreads.html). Thisimplementation is being used for the
SCO port.
See the thr_lock and thr_alarm programs in the mysys directory for some tests/examples of these
problems. More information can be found in the ‘PORTING’ file in the distribution.
What languages are supported by MySQL.
mysqld can give error messages in the following languages: Czech, Dutch, English (default), French,
German, Norwegian, New Norwegian, Polish, Portuguese, Spanish and Swedish.
To start mysqld with a language use the --language=lang or -L lang switch:
mysqld --language swedish mysqld --language /usr/local/share/swedish
The language files are located (by default) in
‘ mysql_base_dir/share/ LANGUAGE/’
8
How/when will you release updates?
We are going to use the following policy when updating MySQL:
Each minor patch will increment the last number in the version string. When there are new features or
minor incompatibilities with previous versions, the second number in the version string will be
incremented.
On the rare occasions when a fatal bug is found that can’t be avoided we will make new binary
releases for Solaris & Linux as soon as possible. Other people may make binary releases for other
systems but probably less frequently.
For other fatal bugs we will make patches available as soon as we have located and fixed the bug.
For non crucial but annoying bugs we will make patches available if they are sent to me,
otherwise we will combine many of them into a bigger patch.
When there are more then about 10 patches we will make a new full source release.
When we have made a lot of changes we will make a new source and binary release. (About once
a month?)
What is UNIREG ?
Unireg is our tty interface builder, but it uses a low level connection to our NISAM (with is used by
MySQL) and because of this it is very quick. It has existed since 1979 (on Unix in C since ~1986).
Unireg has the following components:
One table viewer with updates/browsing.
Multi table viewer (with 1 scrolling region)
Table creator. (With lots of column tags you can’t create with MySQL) This is WYSIWYG (for
a tty). You design a screen and Unireg prompts for the column specification.
Report generator
A lot of utilities (Quick export/import of tables to/from text files, analysis of table contents...)
Powerful multi table updates (which we use a lot) with a BASIC like language with LOTS of
functions.
Dynamic languages (at present in Swedish and Finnish). If somebody wants an English version
there are a few files that would have to be translate.
The ability to run updates interactively or in a batch.
Emacs like key definitions with keyboard macros.
All this in a binary of 800k.
The convform utility. Changes .frm and text files between different character sets.
The pack_isam utility. Packs a NISAM table (makes it 50-80% smaller). The table can be read by
MySQL like an ordinary table. Only 1 record has to be decompressed / access. Cannot handle
BLOB:s or updates (yet).

What functionality is missing in MySQL.
The following functionality is missing in the current version of MySQL. For the priority of new
extensions you should consult: http://www.tcx.se/TODO
Sub-SELECT. The following will not work in MySQL:
SELECT * from table WHERE id IN (SELECT id from table2)
MySQL only supports INSERT ... SELECT.... Independent sub-SELECTs will be
availably in 3.22.0. One can use the function IN() in other context thought.
MySQL doesn’t yet support sqlSELECT ... INTO TABLE..... Currentlyl MySQL only
supports SELECT ... INTO OUTFILE....
Outer joins. LEFT OUTER JOIN will be availabe in 3.22.0.
Transactions is not supported. MySQL will shortly support atomic operations which is like
transactions without rollback. With atomic operations you can make a bunch of
insert/select/whatever commands and be guaranteed that no other thread will interfere. In this
context you won’t usually need rollback. Currently you can do this with the help of the LOCK
TABLES/UNLOCK TABLES command. See section Lock tables syntax [p 48]
Triggers is not supported. The planed update language will be able to handle stored procedures,
but without triggers. Triggers usually slow down everything, even for queries when they aren’t
needed.
The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors
CREATE TABLE commands: It doesn’t do anything. The FOREIGN KEY syntax without ON
DELETE .. is mostly used for documentation purposes. Some ODBC applications may uses
this to produce automatic WHERE clauses thought, but this is usually easy to override. FOREIGN
KEY is sometimes used as a constraint check, but this check is in practice unnecessary if one
insert rows in the tables in the right order. In MySQL one can go around the problem that ON
DELETE ... isn’t implement by adding the approative DELETE statement to the application
when one deletes record from a table that has FOREIGN KEY. In practice this is as quick (in
some case quicker) and much more portable than using FOREING KEY Foreign keys is
something that makes life very complicated, because the foreign key definition must be stored in
some database and then the hole ’nice approach’ by using only files that can be moved, copied
and removed will be destroyed. In the near future we will extend FOREIGN KEYS so that the at
least the information will be saved and may be retrieved by mysqldump and ODBC.
MySQL doesn’t support views, but this is on the TODO.
Some other SQL has -- as start comment. MySQL has # as the start comment character, even if
the MySQL command line tool removes all lines that starts with --. MySQL will not support this
degenerated comment style because we have had many problems with automatic generated SQL
queries that has used something like the following code:
20
UPDATE table_name SET credit=credit-!payment!
Where instead of !payment! we automaticly insert the value of the payment. What do you think
will happen when ’payment’ is negative ? Because 1--1 is legal in SQL, we think is terrible that
’--’ means start comment. If you have a sql program in a textfile that contains -- comments you
should use
replace " --" " #" < text-file-with-funny-comments.sql mysql database.
instead of the normal
mysql database < text-file-with-funny-comments.sql
You can also change the -- to # comments in the command file with:
replace " --" " #" -- text-file-with-funny-comments.sql
and change them back with:
replace " #" " --" -- text-file-with-funny-comments.sql
What standards does MySQL follow?
Entry level SQL92. ODBC level 0-2.
What functions exist only for compatibility?
GRANT. See section GRANT syntax. (Compatibility function). [p 49] This always succeeds. You
should use the MySQL privilege tables. See section How does the privilege system work? [p 26]
CREATE INDEX. See section CREATE INDEX syntax (Compatibility function). [p 49] This
always succeeds. You should create your index with CREATE TABLE. See section CREATE
TABLE syntax. [p 36] You can also use ALTER TABLE. See section ALTER TABLE syntax [p
37] .
DROP INDEX. See section DROP INDEX syntax (Compatibility function). [p 49] This always
succeeds. You can use ALTER TABLE to drop indexes. See section ALTER TABLE syntax [p
37] .
Limitations of BLOB and TEXT types
If you want to GROUP BY or a ORDER BY on a BLOB or TEXT field, you must make the field into a
fixed length object. The standard way to do this is with the SUBSTRING functions. If you don’t do
this only the first max_sort_length (default=1024) will considered when sorting.
SELECT comment from table order by substring(comment,20);
How to go cope without COMMIT-ROLLBACK
MySQL doesn’t support COMMIT-ROLLBACK. The problem with COMMIT-ROLLBACK is that
for it to handle this efficiently it would require a completely different table layout than MySQL uses
today. MySQL would also need extra threads that does automatic cleanups on the tables and the disk
usage space needed would be much higher. This would make MySQL about 2-4 times slower than it
is today. One of the reasons that MySQL is so much faster than almost all other SQL databases
21
(typical times are at least 2-3 times faster) is the lack of COMMIT-ROLLBACK.
For the moment, we are much more for implementing the SQL server language (stored procedures).
With this you very seldom really need COMMIT-ROLLBACK besides being able to do many more
things without losing any speed.
Loops that need transactions can normally be coded with the help of LOCK TABLES and one doesn’t
need cursors when one can update records on the fly.
We have transactions and cursors on the TODO but not quite prioritised. If it is implemented it will be
as a option to CREATE TABLE. That means that COMMIT-ROLLBACK will only work on those
tables and only those tables will be slower.
We at TcX have a greater need for a real fast database than a 100% general database. Whenever we
find a way to implement these without any speed loss we will probably do it, but for the moment there
is many more important things to do. Check the TODO for how we prioritise things at the moment.
Customers with extended mail support can alter this slightly, so things may be reprioritised.
The current problem is actually ROLLBACK. Without ROLLBACK you can do anything with LOCK
TABLES. To support ROLLBACK MySQL would had to be changed to store all old records that was
updated and revert everything back to the starting point if ROLLBACK was issued. For simple cases
this isn’t that hard to do (the current isamlog could be used for this), but if one wants to have
ROLLBACK with ALTER/DROP/CREATE TABLE it would make everything much harder to
implement.
To avoid using ROLLBACK one can do:
LOCK TABLES ...
- Test conditions.
- Update if everything is ok.
UNLOCK TABLES.
This is usually much faster, but not always. The only thing this doesn’t handle if someone does a kill
on the process...
One can also use functions to update things in one operation. By doing all updates relatively and/or
only update those fields that actually have changed one can get a very efficient application.
For example, when we are doing updates on some customer information, we only update the customer
data that has changed and only test that not any of the changed data, or data that depends on the
changed data, has changed in the original row. The test for change is down with the WHERE clause in
the UPDATE statement. If the record wasn’t updated we give the client a message: "Some of the data
you have changed has been changed by another user", and then we show the old row versus the new
row in a window. The user can then decide which version of the customer record he should use.
This gives us something like ’column locking’ but actually even better, because we only update some
of the columns with relative information. This means that a typical update statement looks something