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...
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...
Whats the alternate approach to indexed views in sql server? Thanks, Salman Shehbaz. ...
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...
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? create view V1 as (select 1 as abc) create view V2 as (select abc from V1 group by abc) ...
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...
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...
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...
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...
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...
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...
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...
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? ...
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? ...
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...
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...
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-...
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 ...
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...