indexed-view

Is it possible to have an indexed view in MySQL?

I found a posting on the MySQL forums from 2005, but nothing more recent than that. Based on that, it's not possible. But a lot can change in 3-4 years. What I'm looking for is a way to have an index over a view but have the table that is viewed remain unindexed. Indexing hurts the writing process and this table is written to quite freq...

Does MySQL view always do full table scan?

I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour? The view is just a SELECT "All Columns From These Tables - NOT *" for the tables I have specified in the first query below. This is my explain output from...

alternate to indexed views

Whats the alternate approach to indexed views in sql server? Thanks, Salman Shehbaz. ...

Indexed view in Sql Server 2005 Error

I tried to add an index on a view in Sql Server 2005 an I got this error: "Cannot create index on view 'AllAssignmentNotes' because the view is not schema bound." I didn't want to put too much information here as it might be overload. Just wondering if anyone could give me some help. I went to the url the error gave me and got me nowhe...

How can i speed up this Indexed View?

Hi folks, I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie. Update: Proof of Solution at the bottom of this post. Schema This is what it looks like :- CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING ...

Is it possible to create an indexed view with SQL Server 2008 which selects from another indexed view?

Is it possible to create an indexed view with SQL Server 2008 which selects from another indexed view? create view V1 as (select 1 as abc) create view V2 as (select abc from V1 group by abc) ...

Need some help making an Indexed view with 2 COUNT_BIG's

Ok, I'm trying to make an indexed view that is against a simple table that stores the results of what people think is good/bad for a post. This is the results of a thumbs up / thumbs down, voting on posts. So here's my pseduo fake table :- HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, PostId INT NOT NULL, IsHelpful BIT NOT NULL...

Do Indexed Views Update During a Transaction?

Let's say I've got a SQL Server Indexed View, vwIndexedView, that reads from two tables, tbTableOne, and tbTableTwo. If I perform an action on one of the two tables inside a transaction, what happens to my indexed view? Is it refreshed immediately, or do I have to commit my transaction before the results will feed into it? For instance...

MS-SQL 2005 full-text index on a view with outer joins

I have a view that I'd like to apply a full text index on. It of course needs a unique clustered index to allow for the full text index. Unforunately, I learned that you cannot create an indexed view that contains outer joins. Problem is that my view needs these outer joins! Is there a way to make this happen, or am I going to be stuck...

SQL GROUP BY: Getting the most recently updated record for an indexed view

I'm trying to do row versioning using an indexed view, grouping records by their key and timestamp, and taking the max(timestamp) record. This is fine, but the query I have used (see the view below) does a self join, meaning it can't be used in an indexed view, which i think will be essential to performance. Is there a way to rewrite the...

Not sure if I understand what an Indexed View does behind the scenes, in SqlServer

Hi folks, I'm using sql server 2008 and have started to find using Indexed Views are helping me speed up some of my queries ... as my schema isn't basic. So, if i have a table that is the following... **ParentTable ParentId INT PK IDENTITY Name VARCHAR(MAX) **ChildTable ChildId INT PK IDENTITY ParentId INT (FK to the table above) Nam...

Query/Database Optimization: How to optimize this? (and should I use a materialized view?)

Hi -- I have a question on how to optimize a query. Actually, as I'm going to be running the query frequently, I was thinking of using a materialized or indexed view (is this a good idea here?) or denormalizing. Consider the following four tables (with irrelevant fields omitted): Users (int userId) Groups (int groupId) GroupMembershi...

Why do my indexes get dropped when I change an Index'd view's schema?

Hi folks, Server: MS Sql Server 2008 When i create an indexed view .. and i then alter the view's schema, the index's all get dropped. It's sooo annoying! Can someone explain why this is? At first I thought that it could be because the fields the index requires are not in the schema any more (we did just alter it, right?) .... but fo...

SQL Server 2005 Index Filter Feature

I was told that there is new a feature in SQL Server 2005 called index filters. What I want to do is add an Index to a column and have the index ignore null values. I can't find good information on this feature (maybe my source is wrong). Can anyone provide additional information on this feature? ...

How do indexes work on views?

Can someone please explain to me in simple English how an index on a view works? I have a fairly simple understanding of indexes on tables; how would indexing a view work differently from just letting the indexes on the underlying tables do their thing naturally? ...

Performance impact of indexed view in MS SQL Server 2008

Does anyone have experience with using indexed view in MS SQL Server 2008? I am trying to find out how does indexed view affect performance of insert / update statements, that are adding / updating rows in tables, which are being used by the indexed view (assuming the indexed view is a select joining a few tables together). Given that al...

Doing large updates against indexed view

We have an indexed view that runs across three large tables. Two of these tables (A & B) are constantly getting updated with user transactions and the other table (C) contains data product info that is needs to be updated once a week. This product table contains over 6 million records. We need this view across these three tables for ou...

Full-text index population performance on a SQL 2005 indexed view

I have created an indexed view: CREATE VIEW LogValueTexts WITH SCHEMABINDING AS SELECT ISNULL(LRVS_SLOG_ID*256+LRVS_IDX,0) AS ID,LRVS_VALUE AS Value FROM dbo.LRVS_LogRecordedValues WHERE LEN(LRVS_VALUE)>4 CREATE UNIQUE CLUSTERED INDEX IX_LogValueTexts ON LogValueTexts (ID) On SQL 2005 Standard SP3 it takes forever to populate a full-...

Problem with indexed view msql

hi I have problem with a counting column in my view. SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ColumnC, ModuloColAColB Query is similar to this MSDN example: http://msdn.microsoft.com/en-us/library/ms191432.aspx When I try to compile view I received error message like: "invalid ...

NHibernate and indexed views (materialized views)

Hi! Recently I was busy implementing paging functionality with NHibernate and things went smoothly with simple entities, but I hit a performance problem with those ones where multiple joins are required to acquire the requested page. Besides that, implementation would be much simpler if the queries could be performed by convention witho...