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
0answers
17 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
23 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 ...
4
votes
1answer
337 views

MySQL won't use an index on an int, if I provide a string

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...
0
votes
1answer
12 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
707 views

How to find index details on Temporary tables in SYBASE

I am a layman in SYBASE world. In order to improve performance, I am creating indexes after data load of temporary tables in many stored procedures. Say, one proc A has an insert into temporary ...
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 ...
1
vote
1answer
61 views

MongoDB: Creating index very slow

I'm currently creating an index on a mongo database and so far it has taken about 52 hours. I've worked with large(r) Mongo databases before but creating an index has never taken this long. My ...
1
vote
2answers
96 views

DB2 Load Utility Primary Key vs. Unique Index

In short, does the Primary Key double as a Unique Index? Some background: I am trying to load a bunch of data into two tables using the DB2 Load utility loading in IXF file types. The data is ...
0
votes
1answer
43 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
16 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
24 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
1k views

Query to see Index Fragmentation on all databases without using SP or temp tables

I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as ...
0
votes
1answer
59 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
3answers
29 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
0answers
28 views

Will a composite index containing an enum with few choices or a boolean increase or slow down query speed? [closed]

I have the following query: SELECT new DTOs.EntityDTOs.cropvariety.SeedCalendarResultDTO(u.id, u.crop, i.previewImageUrl, u.plantType, u.growthTemperature, u.hardiness, u.growthTime) FROM SeedRecord ...
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 ...
1
vote
2answers
40 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 ...
0
votes
1answer
34 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
vote
1answer
1k views

duplicate key error index MongoDB

I have rocket.chat server connected to LDAP. When one of users try logging to their account he get logged to someone other account, it's probably because indexes. My logs look like this: [...
1
vote
1answer
33 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....
11
votes
4answers
5k views

Is there a way to show the creation statement for an index in PostgreSQL

I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new ...
0
votes
0answers
11 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 ...
5
votes
2answers
2k 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 ...
3
votes
1answer
548 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not all)....
0
votes
1answer
35 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
34 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
17 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
97 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
55 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
1
vote
2answers
2k views

Query to retrieve index details for a particular column in all tables

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to ...
8
votes
2answers
4k views

How to index dynamic attributes in MongoDB

I have following kind of data (simplified a bit from my real case) in MongoDB: { "name":"some name", "attrs":[ {"n":"subject","v":"Some subject"}, {"n":"description","v":"Some ...
5
votes
3answers
115 views

Optimizing table/indices for getting the latest row (filtering by one additional column)

I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this: ...
1
vote
0answers
31 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
5answers
541 views

Slow queries on indexed columns (large datasets)

I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete. product_base (1.5GB, 26 million rows) CREATE TABLE `product_base` ( `EAN13` varchar(13) NOT NULL DEFAULT '', ...
0
votes
1answer
102 views

How to optimize a SQL Server query to fetch the results faster

Currently I am having a situation where I have to import around 0.5 Million rows into a SQL Server table named BARCODE_MANAGEMENT, which has the following columns: ID int (identity column), SETUP_ID ...
0
votes
0answers
26 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
43 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. ...
0
votes
1answer
92 views

What are the pros and cons of adding secondary columns to a table's primary key?

In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table. I have a photos table. Queries on it come in many different ...
0
votes
1answer
436 views

Full text indexes on big tables

I have a news website where I store my articles in a table that looks like that: Id title data Where data contains the content of the article and is the biggest column in the database by far. ...
10
votes
2answers
2k views

Does Postgres offer a feature like “NEWSEQUENTIALID” in MS SQL Server to make UUID as primary key more efficient

Microsoft SQL Server offers the NEWID command to generate a new GUID (the Microsoft version of UUID) value that can be used as a primary key value (in their uniqueidentifier data type). These are not ...
0
votes
1answer
63 views

Very slow query despite index being used

I have this table, created to hold tons of modbus measurements that comes from my devices: CREATE TABLE public.tv_smartdevicemeasurement_modbus ( measurement_id serial NOT NULL, insert_time ...
0
votes
1answer
565 views

How to pass url value to index? [closed]

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
0
votes
1answer
48 views

Index / Check DB Script question

I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.
4
votes
1answer
1k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
0
votes
1answer
509 views

sqlite database table with 1000's of columns

I am using sqlite3 in an application to conduct an alalysis of distances between sets of points. Distances between these points have been precomputed using dijkstra's algorithim and left me with a ...
6
votes
1answer
1k views

How can I determine why a concurrent Postgres index was marked as INVALID?

I'm trying to create an index concurrently. CREATE INDEX CONCURRENTLY blocks_expr_idx6 ON blocks USING btree ('blah') WHERE "type" = 'user root'; 12 hours after I ran the query, if I run \d blocks ...