New answers tagged

0

You cannot have an index on actions that uses columns in events, so yes, you probably won't be able to make this query fast without some denormalization. The best option is to store the date on actions as you suggest – you could use a trigger to keep the values synchronized, then you won't run the risk of inconsistencies: One AFTER trigger on events updates ...


2

MySQL does not support included columns. However, if you use the InnoDB storage engine (which is the default), all columns are physically included in the primary key. In other words, the primary key is the table. If you define the primary key as PRIMARY KEY (id) the rows will be physically ordered by id; but for all queries that use the primary key, the ...


2

Looking at the MySQL Documentation, the glossary indicates this about Covering Indexes: An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than ...


0

I can't see it being used for a predicated query. It may be used for a scan, however, if the estimated cost were lower than the alternative. For a scan the cost is largely governed by IO. So if two access paths (i.e. indexes) both provide enough information to support the query the optimizer is likely to prefer the one which is narrower. The clustered index ...


2

Just to make it clear: SQL Server can scan the index, but not seek. We should avoid using terminology such as "using" an index. There's a big difference between an index seek and an index scan. But sometimes a non-clustered index scan is better than a table scan (for a clustered table we use the term "clustered index scan" instead of &...


2

In my case the leading column in the index is unique ... it strikes me it has all the information to know its unique. It does not. That primary key does not guarantee that either a or b are unique, just that all combinations of a & b are. There could be many rows for which b = 1 is true, maybe all of them, maybe none of them. When you search for a ...


0

As the comment by JSapkota says, the answer exists on StackOverflow: Can Indices actually decrease SELECT performance? Yes, albeit very slightly - so slightly that it would be justified to also answer "No". If you have an index which might be considered for a query, but is not useable, the optimizer will waste a short time pondering whether and ...


5

Why the dropped index is still there? In fact that index is no longer there, the column is the one still there and what was once a Clustered Index is now a Heap. Did I got the dependency error above because of this still-existing index? No, because the index no longer exists. If not, is there something else I should check? I'd like to avoid dropping the ...


1

Handling NULLs is tricky, sure. You could get around needing two indexes with either a computed column, or by updating the NULLs in your current column to some canary value. ALTER TABLE dbo.MyTable ADD foofighter AS ISNULL(foo, 'canarystring'); CREATE INDEX fooey ON dbo.MyTable (bar) INCLUDE(foofighter) WHERE foofighter IN ('string01', 'string02', '...


1

As already answered by Mr. Josh Darnell in detail about the decision made by optimizer and details on parallelism due to pre-sorted data, I would like to add my bit in the answer that, we mostly rely on logical reads for measuring performance and not the time because time varies a lot based on many factors like load on the server, network, disk etc(You can ...


2

Looking at the ratio of elapsed time to CPU time for both queries, we can see that the "no index" query benefited from parallelism - CPU time was about 3 times greater than elapsed time. After adding the clustered index to Table_B, you got a serial version of that execution plan (CPU time and elapsed time were about equal). Or maybe an entirely ...


1

IMHO, Creating Clustered or NonClustered index on IsDisabled is bad idea. As we know Column should be Selective enough to qualify for Index. IsDisabled=0 is not at all Selective.Many rows will be return if IsDisabled=0 is use in predicate. So Optimizer will always prefer Index Scan over Index Seek as cost will be less in this case. Even if in some case it ...


2

First, testing is your buddy. Run tests on this. Let's avoid talking too much opinion. This question could be taken as a purely opinion driven one. Instead, let's talk about some of the mechanics of choosing a clustered index. You get one clustered index on a table because the clustered index defines data storage. Since the clustered index is where the data ...


1

The query needs to check whether it can "see" each row, based on transaction_isolation_mode. So, there are is no shortcut like you suggest.


1

found this table has 800,000 rows and does not have any indexes Is there any safer and more sufficient way to solve this problem? Only really re-emptive work: Proper design work up-front so there are no large tables with no indexes, or not common queries that are not well-supported by the existing indexes. This might be out of your hands if you don't work ...


1

Introduction I do not have the time for extensive testing, but can suggest from where to start. If you rewrite the query in a more symmetrical manner, to emphasise that both entities are joined to a two-dimensional cross-section of MyJoinTable: SELECT E1.Field, E2.Field FROM MyJoinTable JT JOIN Entity1 E1 ON E1.Id = JT.Entity1Id JOIN Entity2 E2 ON E2.Id = JT....


5

Access to a row using a non-clustered index, which idx_name is, requires extra random I/O: b-tree lookup finds the clustered (primary key) index value, then you need to go and fetch the actual row from the clustered index. The alternative is a sequential scan of the clustered index itself, which does not incur that extra I/O cost and is also simply more ...


1

It has to scan the whole index. Look at the output of: SHOW INDEX FROM user_actions; You will see the approximate cardinality of each index. The approximation is based on a few random dives into the index, not an exhaustively checked exact number. The approximation is sufficient for balancing.


2

Why do you believe using variables instead of parameters will improve performance? With local variables, the plan is optimized for unknown values using average density values gleaned from statistics to estimate row counts. With parameters, OTOH, SQL Server uses the actual parameter values provided for the initial compilation to estimate row counts. This ...


3

Why does the following query not use b.a's index? Because the index only stores the UUID value of a, not the result of the expression a::text To make the index eligible for use, you would need to cast the other column to the data type stored in the index, e.g. select * from b join c on b.a = c.a::uuid; Note that this doesn't guarantee that the index ...


0

The index can be SEEKed if you search on Col1 (first column). It can possibly be scanned if you search for the other columns, but if it is depends on whether it covers the index, estimated selectivity and stuff like that. And even if it would be used, a scan is never as effective as a seek (rest being equal).


1

But after the run, the fragmentation is still very high. Is that okay? What could be done? The reason lies in the last column of the output you have pasted. See the column page_count. Unless you have page_count value > 2000 there is no point in rebuilding, reorganizing and updating stats of that index. This is because and I am quoting from article I ...


2

Bottom line: fragmentation is irrelevant for small indexes. Never bother about it for indexes less than some 1000 pages, or perhaps we should say 10000 nowadays. If you remember cassette tapes, we always have some noise in the background (my analogy, perhaps works better in Swedish). Tip1: don't use single quotes for column names in SELECT list. It divert ...


1

Attempting to implement the same, I ended up using this to approximate at a decent level of accuracy: WHILE (@NonLeafLevels > 1) BEGIN DECLARE @TempIndexPages FLOAT; -- TempIndexPages may be exceedingly small, so catch any arith overflows and call it 0 BEGIN TRY SET @TempIndexPages = @NumLeafPages / POWER(@IndexRowsPerPage, @...


2

Use bigint values generated by a single sequence. Numbers are quite easy to say over the telephone. Forget about the requirement of enforcing database-wide uniqueness. Unless someone manually messes with the data, the sequence will guarantee the requirement. The performance cost of enforcing such a requirement with database means would greatly outweigh its ...


3

Your query must be using an index on "id" to scan the index in the implied order, and then filtering out everything where "user_id" does not equal 123, stopping after it finds 31 rows which survive the filter. Going in one direction it quickly finds 31 such rows, going in the other direction needs to filter out a large number of rows ...


1

There is a discussion about this in the documentation (https://www.postgresql.org/docs/current/indexes-ordering.html): By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on a column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can ...


0

It will work just fine. Whether the size and performance is worth it? That depends on too many things. Why not just create the darned thing on a test server?


0

... a full text index for a column whose values are json ... usually contain Guid (or UUID) values. Is it a good idea to build a full text index for this column? I would say "No". A full text index makes all of the words in the field available for searching. I don't think that's what you want. You want to be able to search for those Guid/Uuid ...


1

And why don't you simply create b-tree index on created_at column? Or compound (created_at+kpi) ? In the second case all the information will be taken during the index scan and no need to read blocks from the table itself. But if the time range between X and Y is rather big, then the optimizer might think that full table scan is cheaper anyway. In some ...


Top 50 recent answers are included