Posted by mikeytown2 on June 8, 2011 at 11:03pm
Been doing some slow query debugging and GROUP BY/DISTINCT has turned one of my views from taking 0.0049 seconds to 1.7768 seconds. This view also has LIMIT 0, 1 slapped at the end of it, so MySQL could be smarter about this. But in the mean time I could use PHP to do the Group By/Distinct logic. Has someone done this before?
Comments
LIMIT clauses only affect
LIMIT clauses only affect what rows are sent to the client, the server still needs to do all the grouping/sorting/etc. DISTINCT can often be rewritten to use GROUP BY which is generally faster, sometimes by an order of magnitude. Keep in mind that by adding DISTINCT or GROUP BY you may need to add/tweak indexes. You could do the GROUP BY / DISTINCT in PHP in a views hook, I see no issue with that.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Test Results
Tested the same query, one with GROUP BY, another time using DISTINCT and another time using none and another variant not using ORDER BY. GROUP BY was the slowest in this case. When using ORDER BY with GROUP BY or DISTINCT, EXPLAIN gives me this on the node table
Using where; Using temporary; Using filesortWhen using ORDER BY or GROUP BY, EXPLAIN gives me this on the node table
Using whereWhen using DISTINCT, EXPLAIN gives me this on the node table
Using where; Using temporaryWent down the index rabbit hole (FORCE INDEX() and friends) and came out empty handed on the other side.
If I were to eliminate one of these I would go for GROUP BY/DISTINCT as this would then allow me to jump out of the loop early once the LIMIT conditions are met. If I got rid of ORDER BY I would need to sort the entire result set in PHP and that would be slower. In any case making this extensible so one can pick which SQL command(s) to drop and use in PHP would be nice; but for starters I'm going to pick DISTINCT.
Thanks for the feedback! if you got any other thoughts let me know.
DISTINCT indexes
Interesting. And sorry for the redundant stuff -- our posts crossed paths. So for DISTINCT, did you verify that all the columns are in the same index? Or are they from different tables?
Query
Server version: 5.1.42-MariaDB (go CentOS... latest in the same branch is 5.1.53)
Here's the query with all 3 things commented out so you can tell how I tested it.
SELECT#DISTINCT node.nid AS nid,
node.nid
node_data_field_multimedia_image.field_multimedia_image_fid AS node_data_field_multimedia_image_field_multimedia_image_fid, node_data_field_multimedia_image.field_multimedia_image_list AS node_data_field_multimedia_image_field_multimedia_image_list, node_data_field_multimedia_image.field_multimedia_image_data AS node_data_field_multimedia_image_field_multimedia_image_data, node.type AS node_type, node.vid AS node_vid, node_data_field_story_mainimage.field_story_mainimage_fid AS node_data_field_story_mainimage_field_story_mainimage_fid, node_data_field_story_mainimage.field_story_mainimage_list AS node_data_field_story_mainimage_field_story_mainimage_list, node_data_field_story_mainimage.field_story_mainimage_data AS node_data_field_story_mainimage_field_story_mainimage_data, node.title AS node_title, node.created AS node_created
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node node_users ON users.uid = node_users.uid
AND node_users.type = 'profile'
LEFT JOIN nodequeue_nodes nodequeue_nodes_node ON node.nid = nodequeue_nodes_node.nid
AND nodequeue_nodes_node.qid =3
LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid
LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid
LEFT JOIN og_access_post og_access_post ON node.nid = og_access_post.nid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
INNER JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN content_type_multimedia node_data_field_multimedia_image ON node.vid = node_data_field_multimedia_image.vid
LEFT JOIN content_type_story node_data_field_story_mainimage ON node.vid = node_data_field_story_mainimage.vid
WHERE (
(
(
node.status <>0
)
AND (
node.type
IN (
'images', 'multimedia', 'story'
)
)
AND (
node.promote <>0
)
AND (
nodequeue_nodes_node.nid IS NULL
)
)
AND (
term_data.name = 'Living'
)
)
#GROUP BY node.nid
#ORDER BY node_created DESC
LIMIT 0 , 1;
Like I stated before, by only using one or the other (GROUP BY/DISTINCT or ORDER BY) I get a huge improvement on the MySQL end. If I use both (GROUP BY/DISTINCT and ORDER BY) the query is a lot slower. The test index on the node table had these fields in it
status, type, promote, created, nid, vid, uid.I know that is not a standard
I know that is not a standard views query, but could you time the following query (I want to know if my sub-query thing works here, too):
SELECTnode.nid
node_data_field_multimedia_image.field_multimedia_image_fid AS node_data_field_multimedia_image_field_multimedia_image_fid, node_data_field_multimedia_image.field_multimedia_image_list AS node_data_field_multimedia_image_field_multimedia_image_list, node_data_field_multimedia_image.field_multimedia_image_data AS node_data_field_multimedia_image_field_multimedia_image_data, node.type AS node_type, node.vid AS node_vid, node_data_field_story_mainimage.field_story_mainimage_fid AS node_data_field_story_mainimage_field_story_mainimage_fid, node_data_field_story_mainimage.field_story_mainimage_list AS node_data_field_story_mainimage_field_story_mainimage_list, node_data_field_story_mainimage.field_story_mainimage_data AS node_data_field_story_mainimage_field_story_mainimage_data, node.title AS node_title, node.created AS node_created
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node node_users ON users.uid = node_users.uid
AND node_users.type = 'profile'
LEFT JOIN nodequeue_nodes nodequeue_nodes_node ON node.nid = nodequeue_nodes_node.nid
AND nodequeue_nodes_node.qid =3
LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid
LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid
LEFT JOIN og_access_post og_access_post ON node.nid = og_access_post.nid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
INNER JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN content_type_multimedia node_data_field_multimedia_image ON node.vid = node_data_field_multimedia_image.vid
LEFT JOIN content_type_story node_data_field_story_mainimage ON node.vid = node_data_field_story_mainimage.vid
INNER JOIN (SELECT node.nid FROM
node node
LEFT JOIN nodequeue_nodes nodequeue_nodes_node ON node.nid = nodequeue_nodes_node.nid
AND nodequeue_nodes_node.qid =3
LEFT JOIN term_node term_node ON node.vid = term_node.vid
INNER JOIN term_data term_data ON term_node.tid = term_data.tid
WHERE (
(
(
node.status <>0
)
AND (
node.type
IN (
'images', 'multimedia', 'story'
)
)
AND (
node.promote <>0
)
AND (
nodequeue_nodes_node.nid IS NULL
)
)
AND (
term_data.name = 'Living'
)
ORDER BY node_created DESC
LIMIT 0 , 1;
)
) AS foo on foo.nid = n.nid
LIMIT 0 , 1;
That seems to also eliminate the DISTINCT and GROUP_BYs.
I am interested how fast this would be.
Best Wishes,
Fabian
Interesting Results
(282 total, Query took 0.0755 sec)
SELECT DISTINCT node.nid,node_data_field_multimedia_image.field_multimedia_image_fid AS node_data_field_multimedia_image_field_multimedia_image_fid, node_data_field_multimedia_image.field_multimedia_image_list AS node_data_field_multimedia_image_field_multimedia_image_list, node_data_field_multimedia_image.field_multimedia_image_data AS node_data_field_multimedia_image_field_multimedia_image_data, node.type AS node_type, node.vid AS node_vid, node_data_field_story_mainimage.field_story_mainimage_fid AS node_data_field_story_mainimage_field_story_mainimage_fid, node_data_field_story_mainimage.field_story_mainimage_list AS node_data_field_story_mainimage_field_story_mainimage_list, node_data_field_story_mainimage.field_story_mainimage_data AS node_data_field_story_mainimage_field_story_mainimage_data, node.title AS node_title, node.created AS node_created
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node node_users ON users.uid = node_users.uid
AND node_users.type = 'profile'
LEFT JOIN nodequeue_nodes nodequeue_nodes_node ON node.nid = nodequeue_nodes_node.nid
AND nodequeue_nodes_node.qid =3
LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid
LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid
LEFT JOIN og_access_post og_access_post ON node.nid = og_access_post.nid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
INNER JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN content_type_multimedia node_data_field_multimedia_image ON node.vid = node_data_field_multimedia_image.vid
LEFT JOIN content_type_story node_data_field_story_mainimage ON node.vid = node_data_field_story_mainimage.vid
INNER JOIN (
SELECT DISTINCT node.nid AS nid FROM
node node
LEFT JOIN nodequeue_nodes nodequeue_nodes_node ON node.nid = nodequeue_nodes_node.nid
AND nodequeue_nodes_node.qid =3
LEFT JOIN term_node term_node ON node.vid = term_node.vid
INNER JOIN term_data term_data ON term_node.tid = term_data.tid
WHERE (
(
(
node.status <>0
)
AND (
node.type
IN (
'images', 'multimedia', 'story'
)
)
AND (
node.promote <>0
)
AND (
nodequeue_nodes_node.nid IS NULL
)
)
AND (
term_data.name = 'Living'
)
)
ORDER BY node.created DESC
#LIMIT 0 , 1
) AS foo on foo.nid = node.nid
#LIMIT 0 , 1;
Outputs the same as
(282 total, Query took 1.2756 sec)
SELECTDISTINCT node.nid AS nid,
#node.nid,
node_data_field_multimedia_image.field_multimedia_image_fid AS node_data_field_multimedia_image_field_multimedia_image_fid, node_data_field_multimedia_image.field_multimedia_image_list AS node_data_field_multimedia_image_field_multimedia_image_list, node_data_field_multimedia_image.field_multimedia_image_data AS node_data_field_multimedia_image_field_multimedia_image_data, node.type AS node_type, node.vid AS node_vid, node_data_field_story_mainimage.field_story_mainimage_fid AS node_data_field_story_mainimage_field_story_mainimage_fid, node_data_field_story_mainimage.field_story_mainimage_list AS node_data_field_story_mainimage_field_story_mainimage_list, node_data_field_story_mainimage.field_story_mainimage_data AS node_data_field_story_mainimage_field_story_mainimage_data, node.title AS node_title, node.created AS node_created
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node node_users ON users.uid = node_users.uid
AND node_users.type = 'profile'
LEFT JOIN nodequeue_nodes nodequeue_nodes_node ON node.nid = nodequeue_nodes_node.nid
AND nodequeue_nodes_node.qid =3
LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid
LEFT JOIN node node_og_ancestry ON og_ancestry.group_nid = node_og_ancestry.nid
LEFT JOIN og_access_post og_access_post ON node.nid = og_access_post.nid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
INNER JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN content_type_multimedia node_data_field_multimedia_image ON node.vid = node_data_field_multimedia_image.vid
LEFT JOIN content_type_story node_data_field_story_mainimage ON node.vid = node_data_field_story_mainimage.vid
WHERE (
(
(
node.status <>0
)
AND (
node.type
IN (
'images', 'multimedia', 'story'
)
)
AND (
node.promote <>0
)
AND (
nodequeue_nodes_node.nid IS NULL
)
)
AND (
term_data.name = 'Living'
)
)
ORDER BY node_created DESC
#LIMIT 0 , 1;
If you noticed I added in DISTINCT to your query & subquery because I wanted to test this on the entire set (got rid of the limits as well); they appear to return the exact results. If I didn't add DISTINCT to the subquery the first and 2nd rows are swapped (nodes have the same creation timestamp). Interesting results but the question is can this be programmatic changed, or will tuning at this level always require manual intervention; how hard would it be to place various joins in a sub query programmatically? The one thing I like about doing DISTINCT is it seems like a simpler optimization to code for.
I was planning on having a checkbox on the view config that would signal this view wants to use PHP to filter out duplicates. I would then rewrite the query; taking note of the LIMIT (short circuit the loop) and get rid of the DISTINCT; or never check Distinct in the first place (devil is in the details). It's "simple enough" that it could be generalized and work when needed (like this query).
Nice! That was the result I
Nice!
That was the result I wanted to see. I made now a module out of this:
http://drupal.org/sandbox/Fabianx/1184598
Instructions are:
- Download and install views_optimized
- Add some hooks to a custom module
Here is an example for my above example:
<?php
/*
* Implementation of hook_views_optimized_view_optimize().
*/
function mymodule_custom_views_optimized_view_optimize($view) {
if ($view->name == 'events') {
return TRUE;
}
}
/*
* Implementation of hook_views_optimized_view_get_fields().
*/
function mymodule_custom_views_optimized_view_get_fields($view) {
if ($view->name != 'events') {
return;
}
return array(
'vid' => array(
'table' => 'node',
'field' => 'vid',
'alias' => 'vid',
),
'node_data_field_date_delta' => array(
'field' => 'delta',
'table' => 'node_data_field_date',
'alias' => 'node_data_field_date_delta',
)
);
}
/*
* Implementation of hook_views_optimized_view_get_orderby().
*/
function mymodule_custom_views_optimized_view_get_orderby($view) {
if ($view->name != 'events') {
return;
}
return array(
'node_data_field_date.field_date_value ASC'
);
}
?>
For your case, you would need to change it to:
<?php
/*
* Implementation of hook_views_optimized_view_get_fields().
*/
function mymodule_custom_views_optimized_view_get_fields($view) {
if ($view->name != 'your-view') {
return;
}
return array(
'nid' => array(
'table' => 'node',
'field' => 'nid',
'alias' => 'nid',
)
);
}
/*
* Implementation of hook_views_optimized_view_get_orderby().
*/
function mymodule_custom_views_optimized_view_get_orderby($view) {
if ($view->name != 'your-view') {
return;
}
return array(
'node.created DESC'
);
}
?>
I know this is by far not finished and for example the orderby hook could not be needed if I did traverse the fields and resolved this to [table].[field] or just added the field "node.created as node_created" to the fields.
However it works and it allows to make views queries faster without much manual coding.
I hope that helps you mikeytown and perhaps some others here, too!
If it does not work out of the box, I'd be glad to see what query you got out of it.
Best Wishes and have fun!
Fabian
GIT
Thanks Fabian. I went to check it out and the sandbox was empty.
git clone http://git.drupal.org/sandbox/Fabianx/1184598.git views_optimized
Cloning into views_optimized...
remote: Counting objects: 7, done.
remote: Compressing objects: 100% (7/7), done.
remote: Total 7 (delta 0), reused 0 (delta 0)
Unpacking objects: 100% (7/7), done.
warning: remote HEAD refers to nonexistent ref, unable to checkout.
Hi, You'll need to use the
Hi,
You'll need to use the 6.x-1.x branch:
git clone --branch 6.x-1.x http://git.drupal.org/sandbox/Fabianx/1184598.git views_optimizedBut I'll re-add the master branch to make default checkout working, too. (done)
Best Wishes,
Fabian
Thanks
Thanks Fabian. My knowledge of git extends to all of about two commands :)
First issue
http://drupal.org/node/1185260
Other than this, works quite well! Thanks again for releasing this code.
DISTINCT / GROUP BY
Before spending too much time rewriting queries, make sure your particular query wouldn't be equivalent to MySQL as GROUP BY or DISTINCT.
http://dev.mysql.com/doc/refman/5.5/en/distinct-optimization.html
Also: "When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows." So it needs to consider the entire set, but LIMIT can actually, uh, limit how far it goes into the set.
The biggest factor with GROUP BY / DISTINCT is whether MySQL needs to create a temporary table to do the crunching.
http://dev.mysql.com/doc/refman/5.5/en/group-by-optimization.html
So it's possible to optimize the existing query by (a) identifying whether GROUP BY is actually faster in your case (although it may not be), and (b) seeing if you can keep MySQL from creating a temporary table to process the request.
Are you confident that you'd get better performance doing your GROUP BY / DISTINCT on the PHP side? That means you'd have to pull over the whole result set for each query. Given the data set size and network bottlenecks, that could be an expensive decision.
Approach
This is an interesting thread, thanks for the info to date. This brings me to an interesting question which is related enough that I'll drop it into the same thread.
I have a number of views-6-x-3.x-dev queries that are what I consider to be unruly. One view is joining the activity module tables to the user's content profile (to display their photo/name) to the activity node (to display a few CCK image fields from that node.) As you can imagine, the view works but it's sloooow with 10 JOINs and a WHERE. There's another version of the same view that mixes in user relationships just to make it extra fun.
So I was considering rewriting it. My question is this -
1) Should I continue to use views, and use the hooks to modify the query? That seems sub-optimal since any changes to the view would also require reviewing the hooks.
2) Another idea I had was to use customfield for the profile stuff. I have it sitting in a cache table already (with a centralized function to store things like firstname/lastname and imagecache presets) so it's accessible via a key/value cache query. In that case, I can get rid of all the joins to the content profile table so I'm left with just the activity+node.
3) Rewrite the query entirely, similar to a process Mikeytown2 is describing of tweaking different queries until I find the optimal one. In that case, I guess I'd just drop this into its own php function and call it from there. This has the most flexibility for performance purposes but also potentially drives outside of the Drupal APIs and has more opportunity for breakage. For example, if a cck single value field changes to multivalue field and a new table gets created. I can handle that part, just something to be aware of.
4) I've read a lot about the Materialized Views API and how it's used for the tracker2. It seems quite interesting, but limited in what it can do and hasn't been updated in a while for D6. Otherwise, nice concept.
5) Extend ApacheSolr_views to include support for Activity, and move the entire thing into Solr queries. This is sort of the nuclear option and requires more work than the others.
Sorry to piggyback on this issue with a different one, it just seemed related to the question of query optimization. The real question is where to apply the query optimization. Thanks.
1) You can change the query
1) You can change the query and still be able to use arguments or exposed filters. It is just that the query rewriting gets more difficult then.
2) That is a good thing and you can also do it in your theming layer unless you need the different columns for a table for example.
But if you have a basic style, you can just override the --fields theme one and be able to assign new fields first.
3) Yep, that is a possibility, but quite similar to 1).
4) Never heard of it. Seems nice.
5) For D7 there is facet module, which works with any fields. (Really nice.)
I found another optimization, which is really query specific, but worked very fine for me: From 0.5s with indexes to 0.03:
The trick is to separate Content and Filters / Arguments.
select SQL_NO_CACHE n.title,r.body,foo.* from node n inner join node_revisions r on n.vid = r.vid inner join (select n.vid,field_date_value,field_date_value2 from content_field_date node_data_field_date inner join node n on node_data_field_date.vid = n.vid WHERE (node_data_field_date.field_date_value2 <= '2012-05-30' AND node_data_field_date.field_date_value >= '2010-05-30') AND n.type in ('event') AND n.language in ('en') order by field_date_value limit 0,10) as foo on n.vid = foo.vid limit 0,10;Inner Query: Filters + Sorts + Limits
select n.vid,field_date_value,field_date_value2 from content_field_date node_data_field_date inner join node n on node_data_field_date.vid = n.vid WHERE (node_data_field_date.field_date_value2 <= '2012-05-30' AND node_data_field_date.field_date_value >= '2010-05-30') AND n.type in ('event') AND n.language in ('en') order by field_date_value limit 0,10;Outer Query: Fields + Limits
select SQL_NO_CACHE n.title,r.body,foo.* from node n inner join node_revisions r on n.vid = r.vid inner join (INNER_QUERY) as foo on n.vid = foo.vid limit 0,10;For small numbers of limit or count this query is much faster than the one produced directly from views, however you need to know which fields are "unique" and which can be re-gotten via the join.
Here one vid could have several dates (via delta), so those needed to be gotten from the subquery. (or one could have gotten the delta, too and join it in again, but that would probably waste some cycles as the information is there already for the filters anyway.).
The inner query is really optimized and the outer one then works fast too. For me joining in the node_revisions with body and then sorting this was really really slow ... (0.5s)
For the count query of course also only the inner query (without the sort) needs to be used.
Of course it always depends on the query needed, but if its the sorting that is slowing it down and you don't have SOLR, this can help.
Update: And works fine with left joining on delta instead, so it could be kind of generalized.
Best Wishes,
Fabian
One Question to the original
One Question to the original post.
@mikeytown2: Why did you need to add the DISTINCT / GROUP BY at all?
Shouldn't the LIMIT 0,1 take care of it or are there other cases where you need different limits? Or is it for the count(*) clause?
Or was this added by adding a node access module automatically? (If yes, there is a core patch using exists instead ...)
Best Wishes,
Fabian
A big problem with Views
A big problem with Views queries like this is the following:
A DISTINCT or GROUP BY always creates a temporary table.
A temporary table includes all columns in the SELECT clause.
Views tends to load all its data from the SELECT query (although Views in Drupal 7 no longer does this, and does an entity_load() afterwards)
This has two bad side-effects for performance:
The size of the dataset written to the temporary table is much larger, meaning it has a far higher chance of being written to disk.
If there are text or blob fields in the query, then the temporary table is always written to disk
So it would be worth looking at removing all extraneous fields from the query except those in the DISTIINCT/WHERE/GROUP BY/ORDER BY and see if that helps.
Thanks
Thanks catch, fabianx, and peter. That's quite helpful.
At least in my case, I do have a tmpfs mount of a ramdisk with temp tables pointed to it, so there are no disk write issues as a bottleneck. But what I am dealing with are potentially large tables, with nodes numbering into the hundreds of thousands, so use of temp tables at all with the no index+all columns+large data set is going to be far from ideal regardless of anything else. And I'm stuck with D6 for quite a while so no D7 views benefits would be available unless they are backported to views-6.x-3.x. Same is true for facet since most of my site is based around Solr.
Back to my original question - if views queries are sometimes sub-optimal, what's the preferred alternative way to interact with the DB? It sounds like directly assembling my own optimized queries via db_query() in PHP functions would be the way to go and is probably faster and simpler. Combining that with Fabian's suggestion to split queries, some of the more static subquery results could be cached in key/value land to make them even faster.
My short term needs do not involve users manipulating query results, so exposed filters+sorts are not involved. That may be an influence in the decision.
Hey catch, Great explanation!
Hey catch,
Great explanation! That is why joining with node_revisions and fetching body field is sooo slow :-): It creates a temporary table on the disk.
That is why my subquery method worked so fine for me (see above). While it still created the temporary table, it could create it in memory.
Thanks.
@catch: So views in D7 is doing kind of the subquery I had to find out the nids, and then use entity_load_multiple to load the rest of data?
How does this work with other tables? Or is it just two queries?
One of the condition and one for the data? Is that even faster than using a temporary table and joining with that one?
Best Wishes,
Fabian
D7 views now only adds fields
D7 views now only adds fields (well to be accurate, field API fields, not sure about any others) to the query if they are required by something other than the SELECT clause. So if you GROUP BY a field it'll be added, but if you don't it's not.
The end result of that query will just be an array of entity IDs. From there it can do entity_load($entity_type, $ids); entity_load() does whatever the entity controller in use does , so either core's multiple load + field cache, or you could install entity_cache which has a chance of grabbing everything from memcache if the cache is warm.
This means you have two round trips - one for the list of IDs, and one for the entities.
The reason it does this is not because of performance, it's because to keep the old pattern views was having to reimplement half the entity and field APIs just to avoid doing the entity_load(), which ended up more trouble than it was worth. However it also has the effect of removing all the extraneous columns from queries like this.
I haven't actually used Views in D7 yet so I don't know what the actual queries look like at the moment. The issue this was changed in is http://drupal.org/node/1002744
Views – significant issue
A temp table is used if sql command includes GROUP BY, DISTINCT, aggregate functions, UNION, or other inputs that break the one to one relationship of view to base table.
- TEMP TABLE views are not updatable because of the above point.
- Temporary tables have no indexes so table scan run slower.
- Views can be updatable even if they have a JOIN but updates must be in one table not both.
- Views can be used instead of column privileges which impact performance and prevent usage of the query cache.
- A View can not have a trigger associated with it.
--
Linux: Web Developer
Peter Bowey Computer Solutions
Australia: GMT+9:30
(¯`·..·[ Peter ]·..·´¯)