Mysql 4th Edition By Paul DuBois - Final Part

Couple of Days back i started reading Mysql 4TH Edition By Paul DuBois

I wanted to share few important quotations found from this book from the rest of the chapters from the book.

1) Queries take time—sometimes an annoyingly long time.And we, being impatient creatures, don’t like to wait, so we leave the abstract world of instantaneous mathematical operations on sets and look for ways to speed up our queries. Fortunately, there are several techniques for doing so:

a) Create indexes on tables to enable the database server to look up rows more quickly.

b) Consider how to write queries to take advantage of those indexes to the fullest extent,and use the EXPLAIN statement to check whether the MySQL server really is doing so.

c) Write queries to affect the server’s scheduling mechanism so that queries arriving from multiple clients cooperate better.

d) Tune the server’s configurable operating parameters to get it to perform more efficiently.

e) Analyze what’s going on with the underlying hardware and how to work around its physical constraints to improve performance.

2) MySQL uses indexes in several ways:

a) As just described, indexes are used to speed up searches for rows matching terms of a WHERE clause or rows that match rows in other tables when performing joins.

b)For queries that use the MIN() or MAX() functions, the smallest or largest value in an indexed column can be found quickly without examining every row.

c) MySQL can often use indexes to perform sorting and grouping operations quickly for ORDER BY and GROUP BY clauses.

d) Sometimes MySQL can use an index to read all the information required for a query. Suppose that you’re selecting values from an indexed numeric column in a MyISAM table, and you’re selecting no other columns from the table. In this case, when MySQL reads an index value from the index file, it obtains the same value
that it would get by reading the data file.There’s no reason to read values twice, so the data file need not even be consulted

3) Costs of Indexing

First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns.That is, indexes slow down most operations that involve writing.This occurs because writing a row requires writing not only the data row, it requires changes to any indexes as well.

Second, an index takes up disk space, and multiple indexes take up correspondingly more space.This might cause you to reach a table size limit more quickly than if there are no indexes.

4) By issuing an EXPLAIN statement, which tells MySQL to display some information about how it would
execute a SELECT query without actually executing it.To use EXPLAIN, just put the word EXPLAIN in front of the SELECT statement:

EXPLAIN SELECT * FROM tbl_name


About the Author

Paul DuBois is a writer, database administrator, and leader in the open source and MySQL communities. He has contributed to the online documentation for MySQL and is the author of MySQL and Perl for the Web (New Riders), MySQL Cookbook, Using csh and tcsh, and Software Portability with imake (O’Reilly). He is currently a technical writer with the MySQL documentation team at Sun Microsystems.