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
0
votes
1answer
48 views

Understanding about Execution plan and Index in SQL Server

I’m Christine and I have been studying for Data Analytics, I would like to know if you could help me clarify what I'm drilling down this execution plan as below. About detail of execution plan, you ...
0
votes
2answers
27 views

Is it possible to make an index for an OR query?

I have a query like this: select * from things where person_1_id=123 OR person_2_id=123 Is it possible to make some sort of index that covers this? Assume that the id (123) matched against will ...
0
votes
1answer
15 views

Single Column index or 3 column index in MS Access

I am creating a table with a single memo field and either 3 text fields (tablename, fieldname, caseID) or a single text field with the 3 text strings concatenated. There will be a single NO ...
0
votes
0answers
18 views

Optimal query using indexes

I have an sqlite table Task(type INT ,due_date LONG) where due_date is stored in epoch TYPE DUE_DATE ---- ---------- 1 1582038293 1 1485034292 2 1082048291 3 ...
1
vote
0answers
17 views

Why is PostgreSQL prefix/range search finicky?

Configuration PostgreSQL 11.4 SET default_statistics_target=1000; Data The scenario is that there are "computers" each with "files", which each have path (ordered text list) unique to that ...
8
votes
2answers
329 views

Creating an Index that is not used by a (SELECT) query reduces performance of that query

I have just watched this video by Pinal Dave. He has a SELECT query that produces ~370k reads in tempdb and ~1200 reads of the table the query is SELECTing from. He then creates an index (Let's call ...
-1
votes
2answers
25 views

UPDATE with too much values at IN state takes too long

I have the next query: UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN (23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,...
0
votes
1answer
20 views

Does a unique clustered index give array-like lookup performance?

Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row. id | PlanetName ---+----------- 0 | Mercury 1 | Venus 2 | Neptune Suppose that I am primarily ...
-1
votes
1answer
20 views

Is there a usecase to create separate indices on the foreign keys of junction/associative table?

Imagine an associative/junction table junction_table_ with (pseudocode) columns like: id_of_table_x REFERENCES TABLE X id_of_table_y REFERENCES TABLE Y PRIMARY KEY (id_of_table_x, id_of_table_y) So ...
1
vote
2answers
74 views

Viability of a large database without using indexes and some other things

I'll explain my situation as briefly as possible. My group is working for another company and I'm mostly creating stored procedures for making reports. The twist is that, last month, they decided to ...
0
votes
1answer
27 views

Index Rebuild Operation

I'm planning to do an index rebuild operation for over 30+ indexes. Some index rebuild require almost >30min because the Table Record is around 60,000,000. Command used for this table is ALTER ...
0
votes
0answers
27 views

DB scheme for the home exchange site: store date ranges or individual days for availability

I am creating a db schema for a home exchange site. Host can set his home as exposed for exchange on certain dates. Guest must be able to view available dates. If exposed date is free (not reserved ...
1
vote
0answers
46 views

Online Index Rebuild shows self blocking

I've been doing some testing on rebuilding some larger clustered indexes (with online + sort_in_tempdb on) and I'm observing something that I can't immediately explain. I have a hunch that this is ...
0
votes
0answers
20 views

Database primary key Guid as clustered index alternative

I've red a couple of blogs/stackoverflow posts about not using a Guid as primary clustered key. Unfortunately we have at the moment and due to this we're experiencing performance degradation (with 900....
0
votes
2answers
54 views

Postgres: SELECT with ORDER BY is 4x slower when using index

I have a table with 4 columns: id SERIAL PRIMARY KEY dt DATE val bigint -- Already has a btree index txt VARCHAR(64) I am running a query: EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;...
1
vote
1answer
25 views

MariaDB handling composite unique indexes that are foreign keys at the same time [closed]

I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that. I ...
16
votes
3answers
8k views

SQL Server - Select most recent record from each group when performance is critical

I run a SQL Server 2016 database where I have the following table with 100+ millions rows: StationId | ParameterId | DateTime | Value 1 | 2 | 2020-02-04 15:00:000 | 5....
0
votes
2answers
31 views

Query on large database much slower on HDD

I have the following table: CREATE TABLE `Geo1` ( `id` int(8) UNSIGNED NOT NULL, `nameRef` int(8) DEFAULT NULL, `placeRef` mediumint(7) UNSIGNED DEFAULT NULL, `category` enum(...) COLLATE ...
5
votes
1answer
265 views

Is there any difference with specifying the primary key as an include column in a nonclustered index?

Don't nonclustered indexes inherently store a reference to the primary key on a table so that it can do a key lookup as needed?...if so, is it any less or more performant to specify the primary key as ...
2
votes
1answer
149 views

How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on?

In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions: Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation. What magnitude of number of rows ...
-1
votes
0answers
43 views

Is there an easy / quick way to create some index fragmentation on a large table?

For testing purposes I'd like to create index fragmentation on a large table (~500 million rows). I'm looking to create at least 5% fragmentation on at least one of the indexes. Is there a quick ...
0
votes
1answer
63 views

How can I check index fragmentation in the quickest way possible? [duplicate]

Checking index fragmentation in my database seems unruly slow. Regardless if I use the DMV sys.dm_db_index_physical_stats (for a specific database, table, or even index) or if I use the SSMS Index ...
0
votes
2answers
30 views

Can multi indexing speed up “AND” of comparisons?

I am using PostgreSQL and have query of type: SELECT * from t WHERE a > 30 AND b < 20 where a and b are columns of table t. I have indexed columns a and b. Would that speed up this query or do ...
3
votes
1answer
123 views

Running sys.dm_db_index_physical_stats in parallel

I am trying to get fragmentation information on a few tables which are over 500 GB and I am using the ...DETAILED option of sys.dm_db_index_physical_stats. I am doing this on a restore copy of the ...
1
vote
0answers
23 views

MySQL SELECT .. WHERE .. ORDER BY … LIMIT 1000000,10 performance

I have following Innodb table with ~2.500.000 rows: ID | param1 | param2 | param3 | param4 | sort_field1 | sort_field2 | sort_field3 | sort_field4 ID - int, PK param1-4 - tinyint(1), INDEX (it has ...
0
votes
1answer
24 views

How should i index this? [closed]

i always had this question and i hope you can help me with this, how should i index created_at if i need to order results based on date. Example: COMMENTS TABLE: ID (UUID) ENTRY_ID (UUID) USER_ID (...
0
votes
0answers
18 views

When should we index a key K1 when a composite index already exists with K1 placed first?

If a table already has an INDEX (K1, K2, ...), when is it a good idea to create an INDEX(K1)? More generally, when INDEX (K1, K2, K3, ...) is present, under what circumstances is it a good idead to ...
0
votes
2answers
22 views

How does MySQL skip ranges (nodes) in a loose index scan?

I've read the MySQL loose index scan, and it makes sense as optimization, however, when using an example B+tree, I don't understand how nodes can be efficiently skipped, due to potential matches ...
0
votes
1answer
20 views

Share single index for multiple linearly correlated columns

I've got a table with two columns whose values have a perfect linear correlation, for example CREATE TABLE measurements ( sensor int PRIMARY KEY, num serial PRIMARY KEY, time timestamptz ...
0
votes
1answer
46 views

Where does this index come from?

Looking for some index in one schema I get to create, I found one index with prefix sys_ such as below: SQL> select index_name from dba_indexes where owner = 'DANILO'; INDEX_NAME -----------------...
0
votes
0answers
19 views

Improve PostgresSQL aggregation query performance

I am aggregating data from a Postgres table, the query is taking approx 2 seconds which I want to reduce to less than a second. Please find below the execution details: Query select a....
0
votes
0answers
26 views

Should I use two indexes? [duplicate]

I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables: Private Index (uses int), for relational table purpose only ...
2
votes
1answer
51 views

Are Clustered Index Usage Stats skewed since they are referenced in Non-Clustered Keys?

When using tools like SP_BlitzIndex and looking at usage stats for indexes. Would clustered index usage be skewed in any way since they are secret keys in all non-clustered indexes and would be read ...
1
vote
1answer
32 views

Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?

I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index CREATE INDEX tab_x_y ON tab(x) ...
1
vote
3answers
35 views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
0
votes
1answer
32 views

Clustered index and Non clustered index [duplicate]

This might be a silly question but i really want to know the internals.This is one of small table just to test as below I have create a table as above and 'name' column is primary key and clustered ...
0
votes
1answer
61 views

Non-clustered index covering a clustered index

I have a table that has the following indexes: IXC clustered index that includes a single column : A asc. IX1 that includes two columns : A asc, B asc. In this case ICX seems like a duplicate index ...
1
vote
2answers
42 views

Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

I have the following table and indices defined: CREATE TABLE IF NOT EXISTS data ( id serial, job_id bigint NOT NULL, payload jsonb NOT NULL, tags jsonb NULL ); CREATE UNIQUE INDEX IF ...
1
vote
1answer
35 views

Rebuild materialized view from massive base tables

We had a materialized view over two bae tables, which was set to refresh on demand, and we had an hourly job which fast refreshed the mview after a linux script inserted new records to the base tables....
0
votes
0answers
14 views

How to create index for XML query in PostgreSQL

I am using PostgreSQL 12. below is my table schema create table soa_rawdata_lp ( date_created timestamp with time zone default now(), filename varchar(128), xmldata xml ); Each ...
0
votes
1answer
36 views

Most efficient way to query a date range in Postgresql

I have a table with a timestamp with tz column. I want to perform a count / group by query for rows within a certain date range: select count(1), url from app_pageview where viewed_at between '2019-...
0
votes
1answer
39 views

PostgreSQL not using varchar index when ordering by primary key

My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it ...
0
votes
1answer
23 views

How to avoid index_merge in a query?

I have multiple queries using index_merge but for put an example, I show you the following query EXPLAIN select count(*) From Clientes Where id_campana='1849' and id_empresa='55' and (...
0
votes
1answer
37 views

Can I modify a column's data type against a live table in Postgres?

I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields. I want to change these columns back to varchar. Can I do this against a live ...
0
votes
1answer
18 views

Multiple query to columns indexed in MySql

I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure: +----------------+ | products | |----------------| | ...
1
vote
2answers
98 views

How to compare write speed before and after adding index?

I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on ...
0
votes
3answers
56 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
1
vote
0answers
32 views

Update is not taking index for query

In the logs I found slow queries, but when running them manually it is fast or at least taking indexes into account. The logs tell me the query part is not using an index, which is available. Running ...
0
votes
0answers
31 views

Will MariaDB treat ut8/utf8mb3 and utf8mb4 collations as compatible?

Background on relevant character sets MySQL and MariaDB support the deprecated utf8mb3 character set and collations. (utf8 is currently an alias for utf8mb3.) This supports all Unicode code points on ...
0
votes
1answer
44 views

Offline index rebuild - does it also create a whole new copy of the index?

As I know, while online index is rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index. ...

1 2 3 4 5 51