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
0answers
15 views

Join on Jsonb Path Query with filter condition

I have a jsonb column with rows like this. "regions" array contains up to 60 elements. All rows contain the same list of ids but 'result' differs. { "a": [ { &...
0
votes
1answer
40 views

How to create an index on CASE expression in Oracle

I am trying to create an index on an oracle table as it follows DECLARE MGMT_ID NUMBER; SSHKEY_MGMT_ID NUMBER; BEGIN SELECT ID INTO MGMT_ID FROM POLICY_SETS WHERE POLICY_SET_NAME = 'MGMT';...
2
votes
3answers
57 views

Slow query when multiple users connected to MariaDB

I have a query that runs very fast on test environment (0.4 seg) but in production it is taking about 5-7 seconds. The database for test environment is very similar to production, the difference is ...
0
votes
0answers
29 views

MySQL query is slower after index create [migrated]

At first i will write some information about my test table. This is books table with 665647 rows of data. Below you can see how it looks. I made 10 same queries for books with price equal select * ...
-2
votes
1answer
43 views

Will index on a partition that doesn't include partition column work fast?

Let's say I have a huge table with columns [CloseOfBusinessDate], [DataSourceID], etc. [CloseOfBusinessDate] is a partition column and I have a clustered index on that column (on ...
0
votes
1answer
33 views

Create Postgres Index for OR query

This is the query I want to optimize: SELECT * FROM invited_member_events WHERE (user_id = '422820341791064085' OR inviter_user_id = '422820341791064085') AND guild_id = '...
0
votes
0answers
61 views

Why does Index Seek Used When Order By Is Present [duplicate]

I have this query: SELECT t.some_id AS id, t.* FROM some_table t WHERE (t.first_col = '123' OR t.second_cold = '321') AND t.third_col != 'aaa' AND t.some_date >= :someDate AND t.some_date <= :...
1
vote
1answer
38 views

Can PostgreSQL build an index without storing the source data?

In Elasticsearch we can configure an index to not store the source data. Is it possible to recreate this in PostgreSQL? Today, we are building an application that encrypts all (sensitive) data before ...
1
vote
1answer
46 views

Table is full even with innodb_file_per_table

I am trying to create an index to my table using alter query. My my.cnf file innodb_data_home_dir = /usr/local/mysql5/data innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
1
vote
0answers
19 views

SQLite, Sort by Index, faster without index

I'm just reading this excellent guide to relational database query planners and indexes on SQLite site. In section 2.2. Sort By Index it says the following about sorting by index: But does this ...
0
votes
0answers
30 views

Searching text using LIKE, and Included Column

SELECT * FROM Table WHERE (Column1 = 'ED69K') SELECT * FROM Table WHERE (Column1 LIKE '% ED69K%') I have a wide table with 25 columns on the table. The above queries do need to do key lookup ...
0
votes
1answer
40 views

Index not used in simple select query mysql

I have a simple query on a table with 66k rows and the index is not used. Query : select * from products p where p.upc = '771313251785' MySQL version : 8 Here is the create table : CREATE TABLE `...
0
votes
0answers
28 views

Optimal index for storing crypto addresses using bytea

I have a simple table that stores Ethereum addresses: CREATE TABLE address ( id SERIAL PRIMARY KEY, address bytea NOT NULL, attributes text[] NOT NULL DEFAULT '{}'::text[] ); CREATE ...
0
votes
0answers
34 views

Rebuild indexes makes switch partitions useless (SQL Server)

I have a big table containing multiple indicators per ticker. The table is partitioned by indicator. Each indicator has nearly 50 million rows and currently there are about 30 indicators and they will ...
2
votes
1answer
71 views

If a DB table is 3TB in size and read speed is 10mbyte/sec. Does it mean that 83hours are necessary to create new index?

One of our custom log tables is 3TB in size. We need to create a new index. Read speed on the disk itself is 10mbyte/sec. Does this mean that the index creation process will be 3.000.000/10 = 86 hours?...
0
votes
2answers
32 views

Is Postgres insert performance the same in the replica

We've got a Postgres database with a table that takes heavy select, update and inserts that needs some tuning (it has too many indexes). We have a read replica, and I'm wondering if moving all of our ...
0
votes
1answer
70 views

How to optimizate COUNT query with multiple LEFT JOIN

I have query that select filters for current category and show how many products in each filter are Atm i have 20k products and 10k filters for test that take around 1sec. to execute that is not to ...
0
votes
0answers
9 views

Will rand string as custom _id make B tree index split higher frequency in MongoDB?

In the answer to the question Are there any advantages to using a custom _id using MongoDB? you can make them more human-friendly, using random strings: t3oSKd9q For the B tree, random key inserts ...
1
vote
1answer
56 views

MySQL indexing on 11 million rows

I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G. I'm importing a large table from development to production, but ...
-1
votes
1answer
62 views

What does the “wi” stand for in SQL Azure Automatic Index Naming? [closed]

I have a SQL Database on Azure that is giving me a performance recommendation for an index. What does the "wi" part of the name? I'm guessing something to do with the include part of the ...
1
vote
2answers
38 views

Improving sql query execution time for 3 billion rows table

I have a 2+ billion row mysql table. I am also writing 1 million rows each day. The table contains some large columns like varchar(255) (to hold long urls) In order to perform analytics on this table ...
0
votes
1answer
37 views

Error 1041 “Out Of Memory” after pointing tmpdir to new disk space

I want to index a very large column in a very large table. The default /tmp does not have enough disk space and is throwing no space left on device error. I have a much larger filesystem partitioned ...
2
votes
1answer
34 views

Is there a difference between text_pattern_ops and COLLATE “C”?

If I have a text column name with a collation, for example tr-TR, and I run a query SELECT * FROM t WHERE name LIKE 'a%' then this will do a seq scan. If I now create an index CREATE INDEX ON t(name ...
0
votes
1answer
34 views

Joining two tables partitioned tables having index with the partition key and joining columns does not help much

I have two partitioned tables that have an index created on the joining key and the partition/primary key (because on partitioned tables on Postgres the partition key must belong always to the index). ...
1
vote
1answer
34 views

Improving indexing of jsonb data when the json structure is well defined

I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
3
votes
2answers
24 views

Conflict resolution for hash-backed unique constraint of large text bodies

So we've just run into ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un" Hint: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index ...
0
votes
1answer
11 views

How can I make an index covering a ts_vector expression and also an integer?

I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this. SELECT "foods".* FROM "foods" INNER JOIN (SELECT "foods&...
0
votes
0answers
37 views

How to speed up the mongo query for getting the min and max values in range?

I have a simple collection that contains two fields: timestamp and value (float). I need to get min and max values in the timestamp range. The collection contains approx 10000000 documents. I've add ...
1
vote
1answer
33 views

Best index for case insensitive match on one field and ordering by another field

I want to run a query like this: SELECT * FROM "core_user" WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com') ORDER BY "core_user"."...
0
votes
1answer
26 views

Good idea to have an index on datetime column?

Assuming something like so: select * from table where DatePart(YEAR, dateColumn) = 2012 Reasonable to have an index on dateColumn? Edit* For SQLServer
1
vote
2answers
73 views

Why does MySQL Workbench let me add unique indexes to primary keys?

I'm using MySQL Workbench 8.0.12 Reproduce: Create a table Create the first column id, select it as a primary key (PK) Under indexes, PRIMARY appears Back under columns, also select id as unique (UQ) ...
0
votes
1answer
32 views

Skip events on sp_BlitzIndex

Is there any way to skip some results on sp_BlitzIndex? Like has done on sp_Blitz, using the @SkipChecksTable. For example, i have some index thats are not in use, but, those indexes are UNIQUE and i ...
0
votes
1answer
23 views

What is the word that means a query predicate can use an index [duplicate]

I remember reading in a paper a word that means “query predicate can be evaluated using an index” but I can’t recall the word. For example if you had a table with a column called “name” and you had an ...
3
votes
1answer
37 views

Indexing options for Postgres for a table with billion records when you want to index only recent records

We have a table which has close to 1 billion records taking up to ~315 Gigs. This is just the size of the raw table without the size taken by any indexes. This table has a updated_at column which gets ...
0
votes
1answer
21 views

Get ALL information about spatial index using SQL, including CELLS_PER_OBJECT and USING GEOGRAPHY_AUTO_GRID

When I create a spatial index I can specify some extra parameters, e.g. CREATE TABLE dbo.Test( Id bigint not null, Polygon geography not null ) GO CREATE SPATIAL INDEX IX_Test_Polygon ON dbo....
1
vote
1answer
33 views

Is it necessary to re-insert all the data in specific order, when I'm going to use the BRIN index?

I'm going to use a BRIN index to replace the B-tree index on a date/timestamp column in Postgresql-11. I never used it before. Since that a BRIN index can be more effective if the data is stored ...
0
votes
0answers
30 views

Oracle and stopkey of an index range scan?

I am struggling to resolve a performance issue with an Oracle query that is opting to perform a full table scan of a table with several million records. The table is partitioned by month of a ...
0
votes
1answer
52 views

Is there a way to tell optimizer not to use index for reads?

Of course I can just drop and recreate or disable and rebuild it. But for large tables it can take time, and for critical tables it can cause issues. Is there any "more sophisticated" way to ...
0
votes
1answer
47 views

Index Reorganize fails

I am using the default SQL Server Maintenance task to reorganize index only which was running until I got the below error. Executing the query "ALTER INDEX [PK_abc] ON " failed with the ...
1
vote
1answer
37 views

Different (much worse) query plan when using one view

I am running the following query: SELECT m.entityid, m.alias::text AS alias, m.measurement_ts, m.volume_m3::double precision AS volume_m3, m.pressure_mca, m.battery_volt, (m....
0
votes
0answers
25 views

Non cluster indexing with combination or on single entity for multiple columns

I am new to db design I also read concepts of non cluster indexing and know about the combined non cluster indexing but in my scenario I am having user table with multiple search on column table ...
0
votes
1answer
26 views

What is the most performant way to group by and join high cardinality columns in column based table?

I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
0
votes
0answers
22 views

Can MongoDB support TTL per document, not per index?

I am trying to do a Redis-like collection in MongoDB. I need a collection of documents that expire and are automatically deleted after a certain time. I can use TTL Indexes but with this, every ...
0
votes
1answer
52 views

Disk space requirement for REINDEX in Postgresql

There is a server with postres 13 running out of disk space. It contains a table metrics that occupies 90% of the entire database. I have deleted old records in this table. Due to this table is used ...
0
votes
0answers
25 views

Index on json list

I have an attribute (ids) defined as jsonb in Postgres that contains a list of values: CREATE TABLE test.id_data2 ( key bigint NOT NULL, ids jsonb, CONSTRAINT tag_data2_pk PRIMARY KEY (...
0
votes
2answers
22 views

Finding “one of” entries to a composite key/index

Problem: Assuming I have a table like this: CREATE TABLE `test` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `a_id` bigint unsigned NOT NULL, `b_id` bigint unsigned NOT NULL, `c_id` bigint ...
0
votes
1answer
38 views

Optimize BETWEEN Query in SQL Server Azure

I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:- Timestamp | ComponenentID | Parameter1 | ...
0
votes
1answer
39 views

Understanding Postgres query planner behaviour on GIN index

Need your expert opinion on index usage and query planner behaviour. \d orders Partitioned table "public.orders" Column | ...
0
votes
1answer
41 views

INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?

When you are in the position of inserting data into a table if the data does not already exist ...what is the best way to go with indexes? I'm specifically thinking of SQL Server here but I assume the ...
1
vote
3answers
112 views

Is it worth adding or rebuilding an index on a table already involved in an active query

I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due ...

1
2 3 4 5
58