MySQL: Views

Its been a long time since I have posted anything, hopefully interesting. In this post we’ll see Views in MySQL. Its not just MySQL, every database has it, we are just using MySQL. I’ll try to explain it in the most simplest terms I can.

Views are just like tables but there is a big difference. Views are made by querying the actual tables. Its like you form a query and save it as view in your database. So basically views are virtual tables which provide sort of abstraction, remember sort of, because they are created by querying actual tables. So whenever we select from views it runs the saved query and gives the resulted tuples. It will be more clear when we see the example. Every organization which uses database declare views and set privileges for it according to category of users which we call permissions. Plus views are more secured obviously, in most cases, because users are not interacting with actual database.

Now, technically views are just stored queries. They just makes our job easier when it comes to the coding part. You create queries and save in database and when needed that data just call select statement for that view.

Required: MySQL

Using:

Consider tables table1 and table2. Please ignore the order of the tables. Declaration of table is for understanding properly and not actual defination.

table1(
  id int primary key,
  name varchar,
  dept_id int foreign key references table2.id,
)

table2(
  id int primary key,
  department varchar,
  location varchar,
)

Below is the definition of view employees and selecting from view.

CREATE VIEW AS employees(
  SELECT a.name, b.department, b.location FROM table1 a, table2 b WHERE a.dept_id = b.id
);
SELECT * FROM employees;

Explanation: There is nothing to explain for the syntax, its self explanatory.

Syntax is almost same in all databases. There are further more things about views like updating views, which I do not recommend. This is because views can only be updated when it is derived from a single table.

For example consider the above view if we try to insert in the view what will be the value of dept_id in table1. Answer is null but we cannot have null in dept_id column clearly, it must have some value because every name must be associated with some dept_id.

There more options like algorithm, with check options etc. You should explore more about views and about creating views.

MySQL: Query Optimization Tips Part I

Writing SQL query is easy but it can use lots of server resources which is utmost important for huge databases, where minimization of execution delay is important, like Facebook – if they don’t update your status very frequently it will cost them users. Let’s start:

1. Most important for query optimization is INDEX. Make sure you define index in your table for the columns which you use more often than others or create for every possible column.
2. Use EXPLAIN statement to determine which indexes are used for a SELECT statement.

These were the general tips. For speeding up queries on MyISAM tables:

SELECT queries

1. Use ANALYZE TABLE after loading data on it. This updates a value for each index part that indicates the average number of rows that have the  same value. For unique obviously it is always 1. MySQL uses this to decide which index to choose when JOIN is used.
2. Sorting a table by index is good when reading all rows of the table according to the index.

WHERE clause

1. Removal of unnecessary parentheses. eg:

   (a AND b AND c) OR (a AND b AND c AND d)

   instead of

   ((a AND b AND c) OR (a AND b AND c AND d))

2. Constant folding eg:

   b>5 AND b=c AND a=5

   instead of

   (a<b AND b=c) AND a=5

3. Constant expression used by indexes are only used once so try to use them as more as you can.
4. COUNT(*) without WHERE clause on a single table fetch the result directly from MyISAM table information which is pretty fast.
5. Constant tables are read first before any other tables. Constant table are:
   • Empty tables or table with one row.
   • A table with which WHERE clause is used on PRIMARY KEY or UNIQUE index.
6. JOIN statement is slower that any other. It checks all the possibilities for join to give best result. If ORDER BY or GROUP BY is used and all the columns come from same table then this table is preferred first in joining.
7. In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.
8. LIMIT also executes faster than query without it.

I hope you found this helpful. We’ll continue with INSERT statements optimization in next post.