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
3
votes
4answers
174 views

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
1answer
38 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?
0
votes
1answer
34 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 ...
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 ...
1
vote
1answer
57 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 ...
-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 ...
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 ...
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). ...
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
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'...
0
votes
2answers
35 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....
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 = '...
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 ...
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 ...
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 ...
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 ...
0
votes
0answers
31 views

FTS is slower on tsvector than text

I tested the performance of searches on two columns : text, tsvector. Somehow it appears that tsvector is slower than text. Also, there's some incosistency with planning time and execution time. It ...
1
vote
1answer
27 views

composite index on bigints

I have a User Relations table, which basically stores all the one-directional relations of users. For example , User A follows user B ; User X blocks User Y User ids: the id of users are bigints. It'...
0
votes
1answer
12 views

How to optimize count(*) using only foreign key index on PostgreSQL

I have a web app that extensively uses data tables that connect to the database to fetch data. The problem though, it always needs first to count the total results before it could paginate the data by ...
1
vote
1answer
30 views

Best Indexes For Group By + Join + Where Condition

I have a database that includes the tables shown below: CREATE TABLE metadata ( keyword_id bigint NOT NULL, campaign_id bigint NOT NULL, org_id bigint NOT NULL, keyword character ...
0
votes
0answers
25 views

When does setting the SORT_IN_TEMPDB option actually improve performance? [duplicate]

My question is related to index rebuilding, mainly the SORT_IN_TEMPDB option. BOL states that: Even if other users are using the database and accessing separate disk addresses, the overall ...
1
vote
1answer
84 views

Do I need to update an index include columns every time I add a new column

I work on a team where we have millions of records of data, but not 100s of millions anything crazy. We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
4
votes
3answers
420 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 ...
0
votes
1answer
33 views

Multiple vs Single COUNT on same col + Multiple vs Single col index

I have two very simple questions 1. Multiple vs Single COUNT on same col I need to perform CASE on an aggregate column like this: SELECT tbl_students.school, COUNT(tbl_students.id) as students, ( ...
0
votes
0answers
62 views

ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1. ...
1
vote
2answers
52 views

Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
2
votes
1answer
62 views

Understanding when to remove Order By or Sort operator from Query/plan

While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way. For below DB test schema-- ...
1
vote
1answer
32 views

Should I add my index after the table is populated or before (MySQL. Aurora)

I know this has been asked in the past, but sometimes things change over time and I wanted to double check. I have a table with about 9 Billion rows. Should I add the index before inserting the data ...
2
votes
1answer
102 views

Index rebuild not working with SQL Server 2016 [closed]

I have some index with very high fragmentation, 85-100% and rebuilding does not help The only clue I have is that it may not work if the data file is fragmented on disk and that could very well be ...
0
votes
1answer
60 views

How to improve performance for table JOINS involving millions of rows

I am dealing with a performance issue for a very simple structure involving simple queries. Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary ...
0
votes
0answers
17 views

How should I add index for this query

I have a stored procedure as follows, how should I add index for it?CREATE DEFINER=`root`@`%` PROCEDURE `SelectUsage4`( IN p_ids MEDIUMTEXT , IN p_locationIDs MEDIUMTEXT , IN ...
0
votes
0answers
41 views

IndexMaintenance fails on error

We are using the Index Maintenance of Ola Hallengren (https://ola.hallengren.com) solution for a couple of years now. While setting up some new SQL servers we are seeing some strange behaviour in the ...
1
vote
1answer
43 views

Improve query performance of subquery with MAX()

I am looking to migrate our support database from MS SQL Server to Postgres and most of it has gone OK. However, some of the queries seem to be slower on PG than on the SQL Server. We have a ticket ...
0
votes
3answers
51 views

How to free used space from disabled index?

I've come across a need to free used space from a large number of disabled indexes. Is it possible to make SQL Server release the used space from these indexes without a drop/create for each index? ...
-1
votes
1answer
27 views

trigram index for integer field

I am trying to optimize query with the clause: WHERE LOWER(CAST(\"my_table\".\"id\" as TEXT) LIKE '%456%' I decided to create a trigram index for it. CREATE INDEX index_name ON my_table USING gin (...
0
votes
0answers
40 views

Duplicated values found in unique indexed column

I have a database table with an unique index idx_1(USER_ID, COURSE_ID), however I still found some entries with the same USER_ID and COURSE_ID (both not null). And the other columns of the duplicated ...
-3
votes
1answer
39 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
0
votes
2answers
70 views

Help error message inside dynamic T-SQL

I have a requirement to come up with reporting for indexes for our whole production environment: I found the below script online and modified it according to my requirement and trying to execute it ...
1
vote
2answers
40 views

MYSQL: How to optimize a date sorted table with a triple primary key index

I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began ...
1
vote
0answers
61 views

oracle 11g select distinct issue

I have a big table (61mil rows) that has an indexed not-null column ID. I want to select the distinct values of that column. I expected the select distinct id from bigtable ; to run fast, making ...
0
votes
1answer
20 views

show extended index equivalent for MySQL 5.7

If you look at this documentation for show index in version 8.0, it says SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] The ...