sabaTEL.net

Java, MySQL, Avaya

User Management

It is very important to maintain and improve the performance of your database when possible, but you should never forget about the security aspect either.

MySQL bases all its security access on the ‘mysql’ database itself. Below you can see the users that I have created and from which box they can access the database. And afterwards I check what the account ‘reader’ is allowed to do:

  • Able to connect to MySQL as long as the ‘reader’ logs in from the same machine ‘localhost’ with the password stored.
  • Execute any SELECT statement to any table of the ‘world’ database.
The list of privileges available at the moment for MySQL is:

Advertisements

August 20, 2011 Posted by | MySQL | Leave a comment

EXPLAIN

The EXPLAIN statement is a valuable tool in terms of tuning your database, but before you get too excited, it is important to pay attention to all it can offer you and its limitations:

  • Shows MySQL’s estimated query plan.
  • Only works for SELECT queries.
The EXPLAIN output columns are:

field : (possible values)  description
id : (number) which SELECT the row belongs to.
select type : (SIMPLE or COMPLEX : primary, subquery, derived, union, union result)
  • SIMPLE, No subqueries or unions.
  • PRIMARY, The outer query
  • SUBQUERY, Numbered according to the position in SQL text
  • DERIVED, Subquery in the FROM clause executed as a temp table
  • UNION, Rows are spooled into a table, then read out with a NULL id in a row that says UNION RESULT

table : (table name) the table accessed or its alias.
type : (ALL, index, range, ref, eq_ref, const, system, NULL) ordered from worst to best by the ‘join type’. How MySQL will access the rows to find results.
possible_keys : (table columns) which indexes looked useful to the optimizer, the indexes that can help make row lookups efficient.
key : (table columns) which indexes the optimizer chose, but that does NOT mean they will be the most efficient indexes, different criteria.
key_len : (number) the number of bytes of the index MySQL will use
ref : (const, column name) which columns/constants from preceding tables are used for lookups in the index named in the key column
rows : (number) estimated number of rows to read, NOT the number of rows in the result set!
extra : (text) Any additional information that help us to understand the execution plan used.
  • Using index: covering index
  • Using where: server post-filters rows from storage engine
  • Using temporary: an implicit temporary table (for sorting or grouping rows, DISTINCT)
  • Using filesort: external sort to order results

August 20, 2011 Posted by | MySQL | Leave a comment