Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [index]

A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns sorted but structures the data differently to allow faster access.

Filter by
Sorted by
Tagged with
2
votes
2answers
28 views

SQL - Leveraging an index on another column with the same ordering guarantee

I have a massive table that is only indexed by the auto-increment column, which is also populated with the current timestamp which is not indexed. CREATE TABLE MyTable ( ID BIGINT NOT NULL ...
0
votes
0answers
26 views

Do I still need to disable or drop column store index before insert and/or update on big tables?

As the title says, I thought that from MSSQL 2016 I don't really have to drop or disable ColumnStore indexes before ETL and rebuild it after. Any consensus?
22
votes
2answers
12k views

LIKE uses index, CHARINDEX does not?

This question is related to my old question. The below query was taking 10 to 15 seconds to execute: SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] ...
5
votes
1answer
300 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
0
votes
1answer
95 views

MySQL Finding index size for compound unique indexes

I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique ...
-1
votes
0answers
126 views

Record in table won't show in form

I have an Access 2010 database that has "frmCatalog" which is sourced from the Catalog table. The Catalog table shows a record with the primary key "Acq" = "5203." But the form does not show 5203. ...
4
votes
3answers
419 views

SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week

I have a very simple query: INSERT INTO #tmptbl SELECT TOP 50 CommentID --this is primary key FROM Comments WITH(NOLOCK) WHERE UserID=@UserID ORDER BY CommentID DESC against this table: CREATE ...
4
votes
2answers
1k views

Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished? I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until ...
0
votes
0answers
29 views

How can I get MariaDB to use indexes in a RANK() query?

Consider the following simple RANK() query. select project_id, group_id, rank() over (partition by group_id order by stars desc) as star_rank from project_metrics; I've added indexes to the ...
0
votes
1answer
33 views

MySQL Create Index Semantics: Clustered vs. Nonclustered?

I'm new to databases and specifically MySQL, so pardon me if this is a simple question. I'm trying to make sense of what happens with MySQL when I call CREATE INDEX. I know that primary keys are ...
-1
votes
1answer
25 views

How can PostgreSQL use multi-column index for column-specific lookups?

If I'm reading this article correctly, one can create an index on t(col1, col2) and use it to speed up lookups for col1. Storage and CPU-wise, I assume that having such a combined index is more ...
1
vote
1answer
56 views

SQL Server indexes and record deletion

I've inherited a large production database in which one of the tables has 72 million rows and is inserted/updated thousands of times during a day. These rows have built up over 3 years and they are ...
0
votes
1answer
33 views

Locating all records relevant for a specific date

I have a MySQL InnoDB table with about 1GB of records and growing. each record in this table has, among other fields two date fields : AddedDateRecord - The date the record was added (datetime). ...
0
votes
1answer
49 views

Why is this datetime index not being used?

I have a table like this: CREATE TABLE TestTable ( [TestTableID] [int] IDENTITY(1,1) NOT NULL, [IntField1] [int] NOT NULL, [IntField2] [int] NOT NULL, [IntField3] [int] NOT NULL, [...
1
vote
1answer
32 views

When SELECTing an expression that is an expression based index does the expression get processed? [closed]

I am asking this mainly for postgres expression based indexes but would be interested to know for other SQL implementations that have expression based indexes. Example: CREATE INDEX people_names ON ...
-1
votes
1answer
91 views

MariaDB: Duplicate entry for key on OPTIMIZE with no duplications

There is a table named contracts with following structure: MariaDB [db_name]> DESCRIBE `contracts`; +-----------------------------+--------------+------+-----+---------+----------------+ | Field ...
1
vote
2answers
217 views

How can I decide Rebuilding Index with DETAILED or SAMPLED on SQL Server

I want to rebuild my indexes and statistics on DB. How can I select this option on production maintenance. SQL Server Version : Microsoft SQL Server 2012 - 11.0.2100.60 (X64) (Build 7601: Service ...
2
votes
1answer
147 views

PostgreSQL: simple equality query returning no results for indexed columns

I have a very large table (around 140M rows) with five columns: CREATE TABLE IF NOT EXISTS citations ( citing_id text, citing_issn text, cited_id text, cited_issn text, year int ); ...
3
votes
4answers
5k views

SQL Server Indexing Performance in Unique/Duplicate column

It is said that if we create index on a column with more uniqueness than performance of that index will be more. But I believe that whether it is unique or not it will occupy same no. of blocks than ...
1
vote
3answers
72 views

Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

I have 2 queries SELECT * FROM sys.dba_indexes WHERE Table_Name = 'XDB$RESOURCE' and SELECT * FROM sys.dba_tables WHERE Table_Name = 'XDB$RESOURCE' Are there any reasons why a table wouldn'...
7
votes
2answers
11k views

Drop an index in postgresql

I had created an index wrongly and now I am trying to drop that index. Since the table is large, dropping the index is taking lot of time. Is there any other way to drop the index quickly?
1
vote
2answers
571 views

MongoDB does not allow to create unique partial index in field that contains null

I'm using MongoDB 3.6.9 (with Ubuntu) to save a set of documents that contain one field named rss_id and for some documents, this field has the value null. I want to create a unique index in that ...
2
votes
1answer
18 views

Postgres not using multicolumn index for queries on leftmost columns?

I'm working in Postgres 9.5.15 and have a table called project: id | integer | | not null | nextval('project_id_seq'::regclass) user_id ...
-1
votes
1answer
30 views

data compression does not show when scripting index on ssms

when I right click on SSMS and choose to script the create index: I get this script, without the data_compression: USE [APCore] GO /****** Object: Index [i_agencyId] Script Date: 07/11/2019 12:...
1
vote
3answers
135 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
0
votes
1answer
27 views

Why can't I create a index using column definition in MySQL?

Consider the following create table syntax CREATE TABLE test1 (f1 int primary key, f2 int, unique key(f2)); I can also write the above as follows CREATE TABLE test1 (f1 int primary key, f2 int ...
0
votes
2answers
34 views

How to optimize inner join query on one large table and one small table

I have table A with 15M records and table B with 5k records. I need to perform an inner join on both but the query time is considerably high. explain analyze SELECT distinct(a.student_id), b....
5
votes
1answer
273 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
3
votes
1answer
42 views

DBCC PAGE equivalent in Postgres

In SQL Server you could check specific index information using the DBCC PAGE command. Is there anything similar in postgres? And if there isn't, how would you get the same info then? This is simply ...
22
votes
3answers
21k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the ...
0
votes
2answers
254 views

Indexed range comparison of array key's value in row inside JSONB

This is for tests with results. The order and if the test was done can be vary per row. column jdata {"name": "Somedata", "array": [ {"name":"test0","value":4.6}, {"name":"test2", "...
0
votes
2answers
319 views

Find MySQL InnoDB index corruption

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c. When I run query: SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3; the ...
3
votes
1answer
40 views

SQL query with Like operator with Leading Wildcards

I have a query as follows: select top (30000) * from table1 where licenseplate like '%as43bv%' I have an index on licenseplate but the query will not hit the index due to leading wildcard. Since ...
0
votes
1answer
91 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
3
votes
1answer
32 views

Should a partial index with WHERE NOT NULL be used if queries use a JDBC prepared statement?

Suppose we create a partial index in Postgres that avoids useless NULL data: CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL; Should I write the SELECT query as: SELECT * FROM my WHERE ...
3
votes
1answer
45 views

Does the Query Optimizer Prefer to Query on Constants before Columns?

I think I have found the answer for this already but I am hoping to get some additional perspective. Assume we are JOINing two tables together on a shared column and each table then has a different ...
0
votes
1answer
26 views

Postgres: Can I associate index with FK constraint?

Postgres FK constraint and index are independent. Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by: WHERE NOT EXISTS (SELECT 1 ...
9
votes
2answers
491 views

Xml index, slow count of rows

I am not getting index seek in the following scenario. Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the ...
0
votes
1answer
88 views

MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze

I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I ...
-1
votes
1answer
371 views

MySQL 5.7 UPDATE slow due to not using index

We have an issue similar to this. Only, the solution there won't work for us as: We don't use multi-table JOINS, but UPDATE … WHERE `userid` IN (<list of 10k ids>); We currently "only" have the ...
3
votes
1answer
85 views

Why is my DB size increasing when rebuildling an index using sort in tempDB in SQL Server?

I am running Ola Hallengren's SQL Maintenance scripts using the following command: EXECUTE [dbo].[IndexOptimize] @Databases = 'MyDatabase' , @FragmentationLow = NULL , @FragmentationMedium = '...
2
votes
2answers
160 views

Can indices save a giant `WHERE foo IN (…)` query with thousands of entries in the query list?

I have some client code hitting my postgres instance with a giant SELECT statement that is matching against a list of IDs. The query ends up looking like this... SELECT "id","name","status","...
3
votes
0answers
32 views

MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN

Question Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
2
votes
1answer
2k views

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index “foo”?

We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: ERROR: index row size xxxx exceeds maximum 2712 for ...
0
votes
1answer
922 views

Cassandra querying without full primary key

As far as I understand there are no secondary indices with multiple columns, only single columns. So how do I solve the index problem I'm having? This is my table: CREATE TABLE IF NOT EXISTS logstv....
0
votes
1answer
238 views

Oracle over-indexed tables

I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more ...
1
vote
1answer
18 views

How safe are PostgreSQL CONCURRENTLY option

As I know CONCURRENTLY let DATABASE create an index without any lock though It could incur more CPU usage. I am planning to create an index in production DATABASE with the option. Before applying ...
4
votes
1answer
32 views

Prevent drop column with index on it in PostgreSQL

In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in ...
2
votes
2answers
144 views

Reasons against using local indexes

For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?
0
votes
1answer
28 views

PosgreSQL query slow when sorting on indexed timestamp colum

I've the following issue: the table below: CREATE TABLE public.current_read ( id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass), cube_id bigint NOT NULL, end_time ...