Search Results

If laurenz's suggestion of enforcing limitations is not possible for your product, because users will kick back or other stake-holders within the client (as you are not likely talking directly to the …

Would creating an nonclustered index with the same column(s) as the clustered index be the most efficient? … What does the optimizer do in this case - does it still make a plan to include these "bad" indexes that just reference the clustered index, or will it know to ignore the with(index) request? …

Is there a way to tell postgres to skip that index validation, because "I swear to god, it's unique" There would be no point, because without properly creasing three index next time you insert or update … Similarly when changing data in a table with a foreign key referring that index. This would have to happen for every relevant data change. …

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.html … “the table remains available for read and write operations while the index is being created”. …

The INCLUDEd columns do not have to be kept in a stable order so if you update those columns (without changing their size if variable, or updating the others covered by the index) things do not need to … Just what it says: the value is copied into the index so the index is larger, and if there is an index scan or partial scan more pages will probably need to be accessed. …

With an index on Sales.UID it will likely scan users and be able to join using sales more efficiently because the joining field is pre-sorted by the index. … If you are likely to search by date rather than listing all sales, then an index with Sales.Date as the first covered column might be more helpful. …

Unique constrains, like any other index, can convert multiple columns in the same table - so to achieve what I think you are asking for you need to create a unique constraint on userId, repoId. … See When should I use a unique constraint instead of a unique index? for more discussion about that similarity and when you might choose one over the other. …

expense of a little disk space & memory due to the larger index. … In other DBs (MS SQL Server, Prosgres, ...) you can reduce the space hit by removing any index you have on just company_id and the compound index will be used instead (assuming company_id is the first …

If you turn the index off so that you have a heap, when you readd the clustered index it will have to reread the data, perform the same sort, and rewrite the pages in the new order - so it will be as expensive … , probably more so, than the initial insert with the clustered index turned on. …

So in this example if you later add an index on only value you effectively have an index containing everything again (i.e. your original concern). … Off the top of my head I'm not sure if this would behave as an index on value including location, device, timestamp, type or an index on value, location, device, timestamp, type. …

If the inserts are roughly in clustered index order, or are a bulk inserted (so can be ordered by the engine) then keeping the clustered index but dropping any non-clustered indexes and recreating them … If you are caring about time and not space (so the internal fragmentation due to extra page splits is not a big issue) then even with arbitrarily ordered individual inserts keeping the clustered index …

Looking at the query: most of your LEFT JOINs are not really LEFT JOINs because of the predicate ON TRUE which makes them behave as CROSS JOINs. Your query is considering every row that comes out of: …

That removes the quick solution of "index the most commonly touched columns, and let the rest still scan" so the only other quick solution is an index on each of the columns. … The query planner will hopefully pick the most selective index option each time reducing the number of rows than need everything else to be checked. …

Further note that in every circumstance where a skip-scan is better than a full index scan, having an index on the second column to use would be more efficient, often significantly so, meaning that while … it uses some other NC index To explain why that particular index is selected and used instead of any other, we would need to know how that index and all the other indexes are defined, and perhaps see …

index is the only solution. … Perhaps go for an online index build if possible - that will take longer but won't completely block part of the application while it does the job. …