New answers tagged

1 vote

Is there a performance penalty when there are two unique indexes on a table with all of the colums in one index are also in the other index?

So, those are two different indexes, with different key structures. Yes, #1, on A,B, is replicated within #2, but, the addition of the column C means that it also must be unique, combined with A&B....
0 votes

How to index a table using column of another table having relation with it?

An indexed view is effectively a multi-table index. The server will automatically maintain this view in line with the main tables CREATE OR ALTER VIEW dbo.PilotByCompany WITH SCHEMABINDING AS SELECT ...
  • 7,691
0 votes

Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query?

To find a B-Tree depth get an index_id from sys.indexes and run DBCC IND(0 /*current db*/, 'your-table-name', 1 /*index_id*/) Physically B-trees are stored within 8K pages like everything else in Sql ...
1 vote

How to index a table using column of another table having relation with it?

I want to get Pilots in a company named 'Boeing'. How should I write my CREATE INDEX in SQL Server to optimize my search queries? Given a query like: SELECT c.CompanyName, p.Name AS PilotName FROM ...
  • 24.7k
0 votes

How to index a table using column of another table having relation with it?

Assuming your query is going to look something like this: SELECT p.[Name] FROM dbo.Company as c INNER JOIN dbo.Pilot AS p ON c.[CompanyID] = p.[CompanyID] WHERE ...
  • 36
1 vote

SQL Server 2014: Why is Clustered Index Rebuild MUCH faster than initial index creation?

Whether you're creating a clustered or a nonclustered index, the operator which does the lion's share of the work appears to be the sort. But when you rebuild the index -- clustered or nonclustered, ...
  • 36
0 votes

Force Index does not work on aws mysql

Row constructors such as (a,b,c)) IN ((1,2,3), (5,6,7)) were not efficiently acted on. As a workaround, I suggest you insert those 3400+ rows into a temp table with the same 3 column names, then do ...
  • 70.5k
5 votes
Accepted

Postgres: Create unique index with conditional

Yes, the syntax is almost what you imagined. They are called partial indexes (or filtered) and can be unique. CREATE UNIQUE INDEX event_unique_ower_uqx -- index name ON table_name (event_id) ...
1 vote
Accepted

'Group by' on indexed TEXT columns not working

This is yet another example of why "prefix" indexing is rarely worth declaring. If those TEXT columns can be limited in size and change to VARCHAR(...), then you can avoid the prefixing and ...
  • 70.5k
1 vote

Creating an index or not creating an index

Even with both indexes, a few insert per second is a low insert rate, so it should not become a bottleneck, and 100k rows is not that much to create a severe performance drop. But to correctly balance ...
5 votes

What's the use case of disabling an index?

How about the use case where the overhead of creating entries in an index during bulk updating is too costly, and therefore disabling the index, bulk update, reenable/reindex, would actually be ...
  • 151
5 votes

What's the use case of disabling an index?

AMtwo covered non-clustered indexes well. As for clustered index, that is a pretty weird thing to do. The only potential use-case I can think of is if you want to make sure that no-one can access the ...
19 votes
Accepted

What's the use case of disabling an index?

Disabiling an index (or all indexes on a table) can be very handy when you will (or might) need to replace the index. For example, if you are removing a (seemingly) duplicate index, you can disable ...
  • 15.1k
0 votes

Can a redundant index improve SELECT performance?

Assuming each table has PRIMARY KEY(id) (which is unclear from the phrasing in the question), you effectively added INDEX(a, id) Meanwhile, left join other O on O.a = M1.a turns into INNER join ...
  • 70.5k
0 votes
Accepted

Can a redundant index improve SELECT performance?

The MariaDB 10.4 InnoDB table magic has an index on (a, b, c), the columns are INT, the PK is not part of this index. ... A developer added a new index in the magic table on just (a) (which is a ...
0 votes

MariaDB 10.6 join query 10 times slower than 10.3

It turned out to be a problem with the physical host of my VPS. After moving the VPS to a physical server that matched the old one, query speed has improved dramatically, and is now faster than on ...
1 vote

Unique constraint on partitioned table

I know that Oracle has "global indexes" and hence can do this; I cannot say anything about the others. Adding this feature would be not easy at all, because it requires a global index: a ...
  • 35.3k
0 votes

Mysql/Mariadb - Speed up query with varchar columns already indexed but index its first 2 characters (USxxxxxxxx, CAxxxxxxxx, etc)

WHERE col LIKE 'US%' will [probably] use the index that you already have, and use it efficiently. I say "probably" because if more than (about) 20% of the table matches, the index will be [...
  • 70.5k
1 vote

Unique index on 3 columns with 2 mutually optional

This looks like flawed requirements. I suggest you split and have two tables, one with (customer, phone) to store the phones and another with (customer, facebook_id) to store the facebook ids of each ...
5 votes
Accepted

Index Design: clustered index using a DATE

Any issues with date as the leading column in a clustered index (considering the above)? None that I can think of off the top of my head. It's typically good to cluster on a column that is a non-...
  • 26.7k
-1 votes

How to index WHERE (start_date >= '2013-12-15')

You must use the index in Postgresql. It uses the BRIN method. CREATE INDEX some_date_idx_brin on items USING brin('created_at'); BTREE provides high performance, but it uses low disk space. Create ...
1 vote
Accepted

MySQL add key to a table that is in use

IIRC mysql has supported online secondary index creation for some time, assuming you are using a decent table type like InnoDB: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations....
5 votes
Accepted

Maria DB - Is there a point in indexing each uniq index cols separately?

I'll assume you are using InnoDB, the default storage engine. The indexes on individual columns colb and colc may be used in a query searching on those columns. The three-column index cannot be used ...
  • 10.5k
6 votes
Accepted

Index on expression (regular expression pattern matching) is not used in query

To be fair, your index on a boolean expression basically works, too. The point is this: if there is a large percentage of "email" rows, no index is going to help (much) - except for special ...
2 votes
Accepted

Adjusting Index for Better Performance

Agreed with everyone's comments so far, most importantly with index tuning is understanding your queries use cases so you know what indexes are being used, what indexes are missing and could be ...
  • 26.7k
0 votes

Creating a specific index tree structure from keys that have the tree-structure baked in? Indexing H3 Hexes

I would consider these two options if it was me: Option 1: depend on your data can aggregate or not, let say it can sum (or count). Take a look at this image Let say each of these 7 children cells (...
  • 133

Top 50 recent answers are included