Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events.

How to express this? Should I create a view, a procedure, or a function which tells for every view ID whether it is archive (in order to use it in two above mentioned views)?

MySQL.

share|improve this question
    
Hm, is it worth to create these two dichotomy views? Maybe instead I should create just one view which has calculated archived flag? Then I may query it with WHERE archive or WHERE NOT(archive) –  porton Mar 5 '13 at 13:02
add comment

1 Answer

There is a very good reason to create two different views rather than to create a single view and query against the archive flag: indexes.

If you have two views, and the definition of each includes the criteria for determining whether archive is true or false, and the queries are properly written to take advantage of indexes, then they will perform better than if you evaluate whether each row is archived or not, because in the latter case, the server will be forced to evaluate the archive condition for each row and then filter those rows out of the final result based on the WHERE clause outside the view... because the optimizer cannot "see through" the view when you're deriving values.

For a relatively understated example, I have a history table with 151,534 records and an index on event_date. I want to find records from last June.

mysql> CREATE VIEW get_history_window AS SELECT id, amount, event_date, 
       IF(event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59',1,0) AS is_in_june 
       FROM history;
Query OK, 0 rows affected (0.04 sec)

mysql> EXPLAIN SELECT * FROM get_history_window WHERE is_in_june = 1;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | history | ALL  | NULL          | NULL | NULL    | NULL | 151534 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Oops. The server has to do a full table scan, and is going to read each of the 150,000+ rows of the table, in order to evaluate the IF() expression so it can know which rows properly satisfy my WHERE.

The optimizer cannot reverse-engineer my query to an extent that it will realize "oh, you just want the rows with event date within a specific range, so I'll just use the index for this." It has to do the comparison on each row.

However... if my view is more properly defined for the intended purpose:

mysql> CREATE VIEW get_last_june_events AS SELECT id, amount, event_date 
       FROM history WHERE event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59';
Query OK, 0 rows affected (0.03 sec)

mysql> EXPLAIN SELECT * FROM get_last_june_events;
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | history | range | event_date    | event_date | 4       | NULL | 1830 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Now, thanks to the fact that the index can be used, only 1830 rows are examined, cutting my actual query time dramatically.

Of course, the tables need good indexes and the queries inside the views need to be optimized to take advantage of them... you can easily get this wrong.

If, for example, my view were defined with WHERE YEAR(event_date) = 2012 AND MONTH(event_date) = 6 instead of the way I illustrated above, WHERE event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59' then I am right back where I started:

+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | history | ALL  | NULL          | NULL | NULL    | NULL | 151534 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

The reason for this, of course, is that when you use functions in a WHERE clause, this almost always prevents the optimizer from being able to use indexes on columns used as arguments to the functions. In the first wrong example, event_date was used as an argument to IF() and here it's used as an argument to YEAR() and MONTH(), so these queries are not optimal, but the one in the middle is.

Assume you wanted to find records more than 6 months old: By now, hopefully you will see that one of these is right and one of these is wrong:

WHERE DATE_ADD(event_date,INTERVAL 6 MONTH) < NOW();  -- wrong

WHERE event_date < DATE_SUB(NOW(), INTERVAL 6 MONTH); -- right

The expressions are logically equivalent and will produce the same result, but the first one is "wrong" in the sense that uses an indexed column as an argument to a function, preventing the index from being usable, while the second one uses a function to calculate a constant against which the indexed values of event_date can be compared.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.