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
20 views

MySQL 8 not using Spatial Index?

I have an issue where whatever I do I cannot get MySQL to use a spatial index. A query like the following then takes 5 seconds! Any way to force this to use the index at all? WITH ranked_reports ...
3
votes
1answer
41 views

Rebuild and Reorganize index while altering index

Can someone please explain for example if i have a unique non-clustered index on a table with 3 columns. due to some reason if i need to add one more column in the existing index . Do i need to ...
0
votes
2answers
37 views

What should I index when joining on a table with conditions?

I have a query like this: SELECT "main_table".* FROM "main_table" INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL AND "other_table"."id" = "main_table"."appointment_id" WHERE "...
1
vote
1answer
53 views

Best way to handle this type of constraint

Guidance please. We have a small configuration table where we need to ensure only 1 record has bit checked. In the past, we have done check constraint with function that checks count, which we are ...
0
votes
0answers
15 views

mechanism of use Index in a query that has two conditions [closed]

If we have a query like this select * from employee where name = 'test' and salary = 500 and we had two indexes, one of them was on name The other one on salary Instead of a multi-attribute index ...
0
votes
0answers
10 views

Partitioned tables not using appropriate index

I have a query in postgres on partitioned tables: select * from journey.journey_part where ( journey.journey_part.start_time >= timestamp '2018-08-29 16:56:21.0' and journey.journey_part....
0
votes
1answer
24 views

I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?

The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-...
1
vote
2answers
36 views

How do I know if an index has been successfully created in MySQL?

I use Capistrano to deploy a Rails app. Capistrano handles running database migrations via essentially a shell script that runs from my local computer. About 24 hours ago I started a migration that ...
1
vote
1answer
40 views

Composite index performance with varchar columns

I have large fact tables with composite non-clustered indexes, something like this: NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey) The columns ending with "Key" are varchar(...
0
votes
1answer
55 views

Understanding about Execution plan and Index in SQL Server [closed]

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
34 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
20 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 ...
9
votes
2answers
390 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
26 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
22 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
21 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
21 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
266 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
124 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
25 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
41 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
43 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
36 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
16 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
37 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-...

1 2 3 4 5 52