Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
2 views

How to create an Index on a raster column?

In a PostGIS enabled DB, I have a 1.7GB table with a raster column rast. I need to create an index on that column in order to speed up my raster queries. I have tried the following: CREATE INDEX ...
7
votes
2answers
270 views

Covering index used despite missing column

I have the following query, using MariaDB 10 / InnoDB: SELECT id, sender_id, receiver_id, thread_id, date_created, content FROM user_message WHERE thread_id = 12345 AND placeholder = FALSE ORDER ...
1
vote
0answers
23 views

Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”

I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on ...
0
votes
0answers
4 views

InnoDB clustered index performance when using random values as primary key

By default, my primary keys of InnoDB storage engines are auto increment integers. In effort, to hide the number of rows in the database, application code implements some random generator for the ...
1
vote
1answer
14 views

Multi-dimensional range queries with single column indexes

I am reading a book that covers many database topics on a very high level, but doesn't dive deeper. It introduces multi-column indexes as a better way to query for 2 dimensional ranges. For example, ...
0
votes
0answers
40 views

How to calculate count of rows of Memory-Optimized Table?

Performance is important for me so I don't use select count(*) from MyTable. I use the following query to calculate rows count: SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [...
0
votes
2answers
25 views

Speeding up Postgresql manual index reset?

I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records). To solve this, I ...
0
votes
1answer
17 views

Force index using the unused index columns having effect as order by for the result set?

if I have a index for (col1, col2, col3, col4), my query put col1 and col2 in the query where clause and force MySQL to use the above index. Would my query result be sorted by col3 and col4 ...
1
vote
1answer
37 views

Optimize MySQL query and index for multiple column range query

I have a table like this: CREATE TABLE IF NOT EXISTS `jobs` ( `job_id` varchar(36) NOT NULL, `job_status` varchar(30) NOT NULL, `created_at` datetime NOT NULL, `lease_date` datetime, ...
1
vote
1answer
33 views

best index type for uuid in postgres 11

I realize a similar question has been asked multiple times but all the ones I can find are from 2014/2015 and many versions of postgres ago. I'm on postgres 11. I'm introducing a new primary key for ...
1
vote
0answers
23 views

Database for dictionary storage and querying

I'm looking for a database for the following data storage and query pattern: Data consists of dictionaries - mappings string to string. Queries are also dictionaries. The result of the query is a ...
0
votes
1answer
16 views

ERROR: “…” is not a table or materialized view, when creating index in PostgreSQL

I got an error from PostgreSQL (11) when my code tries to CREATE INDEX on a view: ERROR: "_blk_l" is not a table or materialized view To avoid the error, I wanted to check if a regclass object can ...
0
votes
0answers
18 views

Composite index to cover sum of columns

Is it possible to create an index in MySQL that will help with a query like this: SELECT a, b, c FROM `table` WHERE a + b + c > 0 I've tried adding a composite index on all a, b, and c to no ...
0
votes
0answers
32 views

SQL Server inconsistently reporting missing indexes

I have a stored procedure that is not performing well and I am trying to tune it. I am confused by what I see in SSMS and the Database Tuning Engine Advisor. In SSMS I have 'Include Actual ...
3
votes
0answers
26 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
1
vote
0answers
57 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
0
votes
1answer
17 views

Is mongodb upsert the right option for write intensive use case?

I'm designing a data model for a transactional use case which is write intensive. The write load would be 900 - 1,600 Transactions Per Second (TPS) which would account for 30 million Inserts/Updates ...
1
vote
1answer
35 views

Can the LOG file fill up from index rebuilds where the indexes are not fragmented?

Working on SQL Server 2014. I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been ...
3
votes
1answer
78 views

How to find dropped or ghosted columns in Azure SQL DB?

In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt. On prem, I use system_internals_partition_columns to determine what indexes have ...
1
vote
1answer
26 views

Filtering a MySQL table on multiple columns, including one with spatial data

I have a MySQL table with linestrings: CREATE TABLE edges ( id int(11) unsigned NOT NULL AUTO_INCREMENT, geometry linestring NOT NULL, type_key varchar(255) NOT NULL, created_at datetime NOT ...
1
vote
2answers
56 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 ...
4
votes
1answer
93 views

Why does MSSQL choose scan in execution plan?

Here is my query (It is a Microsoft Axapta Query): (@P1 bigint) SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT, T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP, T1....
0
votes
0answers
24 views

PostgreSQL GIN pg_trgm default operator class - Does Not accept data Type uuid

I was trying to create GIN Indexes for an Array that stores uuid data type. CREATE INDEX IX_Gin_Column1 ON table_name USING gin (column1 gin_trgm_ops); PostgreSQL raised an error: ERROR: ...
0
votes
2answers
37 views

Why MySQL is ignoring my indexes in a query?

I have the following query in a production database server(master): SELECT * FROM Enlace_llamadas where id_empresa IN (1,5,33,36,40,44,48,53,55,111,115,125,127,151,167,175,181,185,187,189,195,199,...
0
votes
1answer
19 views

Why does a query on a sparse index perform much faster than a query on a partial index in MongoDB?

I created a sparse index in my MongoDB Atlas like so: db.detection_events.createIndex({"emp_id": 1}, {sparse: true}) When I performed this operation: db.detection_events.count({"emp_id": {$exists: ...
2
votes
1answer
68 views

Did adding JSONB indexes bloat the database?

We are running: user@host:~$ psql -d database -c "SELECT version();" version --------------------------------------------------------...
0
votes
0answers
27 views

sys.dm_db_index_usage_stats showing incorrect figures for last_user_update

As the title suggests, I seem to be getting unexpected readings from the last_user_update column in the index_usage_stats DMV. It seems to update the datetime value everytime I run a script inserting ...
0
votes
0answers
14 views

After upgrading from PG 9.3 to 10 indices became corrupted

So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier ...
1
vote
1answer
22 views

Are idx_scan statistics reset automatically (default)?

I was looking at the tables (pg_stat_user_indexes and pg_stat_user_tables) and discovered many indices that are not being used. But before I think about doing any operations to remove these indices, ...
0
votes
0answers
36 views

Slow insert (trigger/indexes) processing

Whenever an insert is done in a table these tasks are executed: One Trigger (see down). Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have ...
0
votes
1answer
18 views

mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

I do not understand why MySQL is not using the index for the below query. I have a table named users and its create table is this table contains data. Now I am running SQL select statements and it'...
2
votes
2answers
52 views

Define JSON Index for Schemaless using MySQL 8.0

We are using MySQL 8.0 and need to implement Schemaless Feature where we allow Vendors to create a Module(ObjectName) and add columns(int, string, currency) to it. Vendor can add objects after ...
0
votes
0answers
44 views

Is there a way to make postgres “COUNT WHERE” query log(N)?

Afaik, now this request SELECT COUNT(*) FROM user WHERE rating > 50 looks like this: find an item with the value of rating 50(or greater) in our b-tree index, pretty fast. Iterate though all items ...
0
votes
1answer
64 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 ...
2
votes
0answers
37 views

the cost decreased but the time was practically the same, why?

Table definition: CREATE TABLE schema.mylogoperation ( id_mylogoperation serial, data DATE, myschema VARCHAR(255), column_var_2 VARCHAR(255), user VARCHAR(255), action TEXT, ...
1
vote
1answer
58 views

Creating index on TIMESTAMP column to use with range operators

TLDR: Can I create an index that's used by the following WHERE clause: WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01') Let's say I have a table like this: CREATE TABLE foo ( foo_id ...
0
votes
1answer
62 views

Simple insert statements are taking so long

I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long I ...
0
votes
0answers
31 views

Does having unused columns effect the indexing of the table

I have a table where there are some columns which are empty and they are not used. That table has to be indexed multiple times based on the application. Does having these empty columns in my table ...
0
votes
0answers
43 views

How is data accessed with unique index in MariaDB? (NULL r_rows on ANALYZE)

I'm using MariaDB 13.3.14 I have a very simple test table with an email column. And I am using ANALYZE to see the behavior of the indexes. If I understand correctly the output of ANALYZE, rows ...
0
votes
2answers
32 views

[Solved]MySQL index work in one table and not work in another table when using IN

I was asked if the index is working or not when we use IN keyword in MySQL like below: //suppose col has index and the value is almost unique SELECT * FROM yourtable WHERE col IN('xxx','yyy','zzz');...
0
votes
1answer
27 views

Should I use Indexes on mySql table?

I have a table in mysql, in which searching, insertion, deletion and updation is done quite frequently and I have read that indexes affection insertion and updation performance. so should I use ...
1
vote
1answer
46 views

passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb

I am often in need to run the sp_executesql in the context of different databases I would like to add the same parameters as the sp_executesql into the sp_ineachdb But when I look at this: -- Syntax ...
2
votes
1answer
205 views

Indexes with the same object_id but different names

When I run SELECT count(object_id) AS count, object_id, min(name) as name1, max(name) AS name2 FROM sys.indexes GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count I will get a list ...
16
votes
5answers
1k views

What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

Currently, we have an existing database and application that is fully functional. I do not have the ability to change the architecture at this point. Today, each table in the database has an "...
1
vote
1answer
28 views

Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server

I am working on a report which will give information for different segments like blocking process slow running queries disk space etc. I also want to add one more segment where I can get ...
0
votes
1answer
18 views

How to add a constraint to a table using a partial index?

I have a simple table: create table item ( lan text not null, disabled date ); My aim is to ensure uniqueness for the lan column over all rows, where disabled is null. I created a partial ...
5
votes
1answer
100 views

Okay to merge included columns on otherwise identical indexes?

I have two indexes that index the same field on the same table. The only difference between them is the included columns. There's no reason I shouldn't combine the included columns and just have one ...
1
vote
0answers
23 views

Spatial index unused on nested set

I'm using the nested set architecture to store URL hierarchies in the following InnoDB table on MySQL 5.7. From my research, I've learnt that using a SPATIAL index will significantly speed up any ...
1
vote
1answer
32 views

PostgreSQL Index Only Scan can NOT return ctid

I have this table in PostgreSQL 11: CREATE TABLE A (id bigint PRIMARY KEY, text text) Now I want to fetch the ctid of rows meeting some condition like id = 123. However, even using pg_hint_plan to ...
0
votes
2answers
39 views

Does adding a postgres index concurrently take up disk space gradually or all at once?

I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I ...