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
1 vote
3 answers
26 views

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

I have two tables, Pilot and Company. My queries will be searching for the Pilots using the CompanyName. For example, I want to get Pilots in a company named 'Boeing'. How should I write my CREATE ...
-1 votes
1 answer
44 views

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

I have a table where rows consist of a few GUID ids and then a huge byte array of data. There is a partitioned clustered index on this table, which orders data by the GUIDs. The table is large, ...
  • 9
1 vote
0 answers
26 views

Clustered Index being used over non-clustered with same key

I have a table that has a clustered index (CI) on the field filename. It also has a non-clustered index (NCI) - With the key column of filename and 5 other columns in the includes. (There are ...
  • 19
-1 votes
0 answers
12 views

Dynamic Table Name versus Index Column

Many companies I've worked for, mostly those using MySQL, have created patterns where they have one "Type", but multiple Table Names to discriminate some detail. For example: - TableName: ...
  • 99
1 vote
1 answer
22 views

Force Index does not work on aws mysql

I have a table sales with a compound index on (user_id, eastern_date, state) CREATE TABLE sales ( id int not null auto_increment, user_id int not null, eastern_date date not null, ...
  • 113
3 votes
1 answer
233 views

Postgres: Create unique index with conditional

I have a table in Postgres with records of type [event_id, user_id, status]. The user is scoped per event, so I have a unique index on [event_id, user_id] so that a user has a unique status. The ...
  • 131
1 vote
1 answer
26 views

'Group by' on indexed TEXT columns not working

I'm creating the following table in MySQL: CREATE TABLE group_by_test ( txt1 TEXT, txt2 TEXT, txt3 TEXT, val INTEGER, INDEX (txt1(10), txt2(10)) ); I then insert a bunch of rows, ...
  • 145
0 votes
1 answer
28 views

Creating an index or not creating an index

I have a SQLite database and a table, which can grow up to 100.000+ rows. I need to query data in the table really (!) fast. There are two ways to query the data. I have an index of type string, ...
  • 167
15 votes
3 answers
2k views

What's the use case of disabling an index?

I just learned that indexes can be disabled on a table. Even the clustered index can be disabled. After that, users can't access the index or the data if clustered index. When will we disable an index?...
2 votes
2 answers
71 views

Can a redundant index improve SELECT performance?

Is it possible that adding a redundant index improves the performance of a query? I encountered a case like that several days ago and since then I've been scratching my head: how? The MariaDB 10.4 ...
-1 votes
0 answers
12 views

Index Maintenance Recommended Settings for Ola Hallengren Maintenance [closed]

What are the recommended settings for Ola Hallengren's maintenance utility for Index Optimization?
  • 27
0 votes
1 answer
16 views

Unique constraint on partitioned table

PostgreSQL has a limitation on unique constraints on partitioned tables: Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation ...
0 votes
1 answer
40 views

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

On my project (MariaDB 10.3), I use a table for storing data per ISIN code (a financial asset identifier). For instance, "US3696041033" for General Electric. I have 4 Millions rows in my ...
5 votes
1 answer
59 views

Index Design: clustered index using a DATE

Every once in a while, one needs a check. This is such a time. I have tables that are used for reporting. They are deleted/inserted once a day or multiple times a day to "refresh the data" ...
  • 181
0 votes
1 answer
30 views

Unique index on 3 columns with 2 mutually optional

Given this table create table contacts ( customer int not null, phone char not null, facebook_id char not null /* some other fields */ ) I'd like to make sure there can't be two rows ...
  • 101
0 votes
1 answer
49 views

MariaDB 10.6 join query 10 times slower than 10.3

I have two VPS servers (LEMP) running Wordpress sites. Specs: Old 4GB RAM Ubuntu 18.04 MariaDB 10.3 New 8GB RAM Ubuntu 22.04 MariaDB 10.6 I migrated a site and found that it was around 10 times ...
2 votes
0 answers
33 views

Indexing 250M URLs for infix search in PostgreSQL

I have a very large Postgres table with about 250M rows, and a text column url. I want to be able to search against this in a performant way, eg. SELECT url_id FROM urls WHERE url ILIKE '%somewebsite....
  • 173
-1 votes
0 answers
16 views

Question on Multiple indexes on same relation

When we create a index on Ordering Key of a relation, The data and disk blocks get rearranged as per the key order with a block anchor for each block if we create a clustering index ( ordering non-key)...
  • 1
0 votes
0 answers
27 views

Postgres query plan different on local and prod. Not using index and bad row estimation

Confusing index-use question, I have a database that looks like this: +-----------------+------------------+-----------+----------+--------------+-------------+ | Column | Type | ...
0 votes
1 answer
14 views

MySQL add key to a table that is in use

I am running MySQL server v5.7.18 on CentOS 7. One of the tables is used as operations log for a web API, and has grown to 20 million rows. Users search this table from a front end app which runs a ...
  • 103
3 votes
1 answer
46 views

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

We are running 10.2.44-MariaDB-log MariaDB Server. Right now I am creating tables with this structure Primary('id') UniqueIndex('colA', 'colB', 'colc'); Index('cola') Index('colb') Index('colc') I ...
-1 votes
0 answers
9 views

Interupt during rebuild

What happens if rebuild query gets interuptted.Does it begin with transaction?. I want to perform Rebuild without using any options.Options like resumable online index rebuilds
0 votes
0 answers
29 views

Index slows down query fetch time

Short question: does index affects fetch time somehow? (using terminology of MySQL Workbench, see description) Details I have a large table (10M rows and will be more) and I need too run group by + ...
4 votes
1 answer
140 views

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

In my PostgreSQL 12.8 database, I have a relatively simple table the_table with a column value with type varchar: CREATE TABLE public.the_table ( id uuid DEFAULT gen_random_uuid() NOT NULL, ...
-1 votes
0 answers
12 views

MySql 5.7 excuted query as abnormal

When mysql executed my query, even the explain output is minimal at rows, but take more time than i use another key with large rows. with primary key it took about 800ms, below is result of explain ...
0 votes
0 answers
33 views

What should I choose between jsonb array and jsonb map for faster querying?

I have a jsonb column which will consist of some keys and the rows will be searchable using those keys. Considering that I will be applying GIN index on the jsonb column what should be the ideal ...
2 votes
1 answer
60 views

Adjusting Index for Better Performance

I've just started my journey as a dba and I figured out using a script that my database indexes are a mess. Here they are: And here is what the script tells me about them: Can you give me insights ...
-1 votes
0 answers
27 views

Indexing a varchar() values in Oracle

I've inherited a poorly-designed database with >400M rows spread across many tables. The data values being imported are strings, but the key values are all GUIDs (or UUID). The IT people were so ...
  • 99
6 votes
7 answers
1k views

What is the benefit of indexing assuming that SQL server has huge memory to accomodate the entire database in memory?

I understand that index seek allows the server to quickly go to the desired page(s) by looking up the index, therefore the benefit is obtained by not having to read all pages of the queried table from ...
  • 2,874
8 votes
2 answers
514 views

Index seek to specific multi-column key, then get some rows in lexicographical order

Consider the following sample table with a multi-column index: create table BigNumbers ( col1 tinyint not null, col2 tinyint not null, col3 tinyint not null, index IX_BigNumbers ...
  • 681
0 votes
2 answers
48 views

Optimizing IN clauses with thousands of integers

I have a search engine that outputs the id's of the elements to show in the search results, or to use in an "analytics" module. In this one, I perform a lot of different queries to show ...
0 votes
2 answers
44 views

Difference between PRIMARY KEY and SERIAL (with index)

Is there any difference between a table with a field defined as userId serial PRIMARY KEY and a table with a field defined as userId serial and then having an index created on the field userId? ...
1 vote
2 answers
68 views

MySQL: Improve performance on large table for dynamically built query with composite index correctly

Assuming that I have a MySQL table with ~ 30 million entries and 40 columns I have an highly active query (5 queries/second) which is quite slow (avg. ~ 20 seconds) and has a high number of rows ...
  • 215
0 votes
1 answer
72 views

Detect corrupted Indexes

How to detect corrupted indexes in PostgreSQL? When I pull data from a column with primary key attribute in a table with where condition, it brings incomplete results. Likewise, when I sort with order ...
3 votes
2 answers
406 views

SQL Server: What's the Difference Between a 'Singleton Lookup' and a 'User Lookup'?

Microsoft describes the [user_lookup] counter in dm_db_index_usage_stats as the 'Number of bookmark lookups by user queries.' It describes the [singleton_lookup_count] in ...
-1 votes
1 answer
51 views

Deadlocks caused due to Keylocks and Pagelocks

I have a database on which select, update and insert queries are run. At times it gave me deadlocks (key locks) while updating and selecting data from the table, it considered the select query to be ...
3 votes
2 answers
586 views

Why MySQL 8 doesn't return any rows when using a table with a descending PRIMARY KEY, 2 indexes, and valid join conditions with existing rows

Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering. When I ...
0 votes
2 answers
54 views

Key value alongside SQL table

I have a SQL (MSSQL) table storing data like the following, with id as primary key. id col1 col2 col3 data valid 1 A B C 1 2 A B C 0 10 X Y Z 1 15 X Y Z 0 75 A B C 1 99 X Y Z 1 The kind ...
0 votes
1 answer
19 views

Does MySQL store the secondary indexes and the clustered index in same .ibd file?

In a file-per-table tablespace, are the secondary indexes stored in the same .ibd file as the clustered index?
user avatar
0 votes
0 answers
29 views

Object necessary for warmstarting database cannot be altered ORACLE

I have this error when I want to rebuild system index : 00701. 00000 - "object necessary for warmstarting database cannot be altered" *Cause: Attempt to alter or drop a database object (...
  • 1
0 votes
1 answer
51 views

How to reduce internal fragmentation while deleting rows in bulk

Context I have a table in SQL Server with millions of rows and a primary key (by default, clustered key). I got the requirement to do housekeeping for that table, so I need to delete rows in bulk ...
0 votes
0 answers
15 views

Using product price as index

I'm working on my first ecommerce website.In my product table design, i've created a non clustered index on name, slug,manufacturer because these fields will be involved in most of users researches. ...
  • 103
1 vote
3 answers
57 views

Forcing InnoDB to build the secondary index

When INSERTing, InnoDB defers the building of secondary indexes. For example, CREATE TABLE item_creators ( item_id int(11) unsigned NOT NULL, creator_id int(11) unsigned NOT NULL, INDEX(creator_id), ...
  • 4,281
3 votes
1 answer
108 views

Reducing Table Size after Clearing TEXT Column Precautions

I have 3rd part app with a table that's 450GB which contains file attachments in a TEXT column (yeah, I know). With a document retention angle, I've exported about 35% of the older attachments to ...
  • 31
0 votes
1 answer
32 views

In general if you were to index all columns would that be the same as indexing no columns?

The title likely sums up my question, but to be clear, I'm specifically thinking RDBs. I have no intention of doing this, I've just been curious and haven't found a clear answer in my attempts at ...
  • 127
0 votes
1 answer
34 views

Query performance with integer field larger than value, partitioned by another column

Our postgres 9 Events table contains 1 Million records, comprising of 2 fields: Job and Task (job is varchar; task is integer) A single Job might have multiple sequential Task(s), for example: Job ...
  • 101
0 votes
1 answer
96 views

postgres how to modify composite primary key order

I want to modify the composite primary key order, for example, from (col_b, col_a) to (col_a, col_b) because I want to query with prefix column (i.e, col_a) and want to take advantage of the primary ...
  • 115
2 votes
2 answers
98 views

trigram indexing for LIKE ALL filters

I find myself looking for a way to dynamically restrict search results so that all searches must be present: ["A TE","SOME"] search array: "MS A TEXT B SOMETHING" --> ...
0 votes
0 answers
32 views

Select MIN on Primary Key Field Using Sub-Optimal Index / Plan

I have a stored procedure that takes in a list (table variable) of IDs, and then it performs some queries to analyze the contents of the list and take the appropriate actions. One of the queries ...
  • 1,094
0 votes
1 answer
30 views

Fill a column with continous index in Postgres

Imagine the followign table: tmp_migration.asset ╔══════════╦══════════════════════════╗ ║ id ║ ...many other columns... ║ ╠══════════╬══════════════════════════╣ ║ 15 ║ ...
  • 103

1
2 3 4 5
63