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.
470
questions with no upvoted or accepted answers
7
votes
1
answer
9k
views
How to estimate reindexing time?
How do you estimate the total time reindexing an entire table will take in PostgreSQL?
I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 ...
- 1,314
5
votes
0
answers
606
views
When/how does removing an index cause a table to lock in MySQL?
Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html.
As I understand it, INPLACE means that there is potentially some ...
- 171
5
votes
0
answers
2k
views
Postgres index on timestamp column not utilized
I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column.
I have a query as below
SELECT
*
FROM
employee
WHERE
timestamp >=...
5
votes
2
answers
574
views
PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?
I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability )
CREATE TABLE scale_data (
section NUMERIC NOT NULL,
...
- 161
5
votes
1
answer
317
views
Guidelines on best indexing strategy for varying searches on 20+ columns
I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation.
Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search.
The problem:
...
- 951
4
votes
1
answer
607
views
AWS Aurora Mysql seemingly not picking PRIMARY or secondary index
Relevant System Info:
Aurora Mysql 8.0.mysql_aurora.3.03
rg6.xl instances (1 writer, 2 read replicas)
Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot)
I have been ...
- 135
4
votes
1
answer
7k
views
Creating index on a big text column in postgres
We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE.
The column is of type text and it is used ...
- 93
4
votes
1
answer
2k
views
Why does postgres choose to sort instead of scanning the index?
I have the following table:
# \d service
Table "public.service"
Column | Type | Modifiers
-------------+----------+-----------
customer_id | integer | not null
date | date ...
user109810
4
votes
0
answers
675
views
Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database?
I have a large production PostgreSQL (9.1) database running on Ubuntu Linux. It is currently being queried and used. However there is another use I need later, and I will need to create a large (...
- 897
4
votes
0
answers
635
views
SQL Server 2012 duplicate key error with Spatial index that shouldn't be unique
We have a table that has a geometry column that is filled by a stored procedure from two x and y values whenever they're changed
( SW_GEOMETRY = GEOMETRY::STGeomFromText('POINT('+ CONVERT(VARCHAR(50)...
- 41
4
votes
0
answers
712
views
SQL Server 2012 Error when creating Selective XML Index on XML Query Plan
I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database.
Part of the optimisation requires persisting ...
- 10.5k
4
votes
5
answers
4k
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 | ...
-------------------------------------------------------------------
...
- 563
3
votes
3
answers
92
views
Do you need to explicitly include the primary key in a composite index?
I recently read that when creating a composite index in MariaDB that the primary key is appended to the end of the index "under the covers". I access my data many times in order by these ...
- 131
3
votes
0
answers
322
views
Full-text search index across columns and tables
I am building a document database and I would like to utilize the full-text search capabilities of PostgreSQL. The documents do not all follow a clean schema, so I decided to store some values in ...
- 81
3
votes
1
answer
317
views
How to optimize simple table with groups and timestamps (SUM over 15M rows with GROUP BY)
I think I have quite simple question, but I couldn't find the answer anywhere.
I'm using MySQL and I have a simple table:
id
timestamp
groupId
costA
costB
costC
...
1
2022-02-01 19:45
1
5,13
3,20
30,...
- 31
3
votes
3
answers
293
views
Execution plan changed for existing query - high CPU usage
So few weeks ago we had an alert on our production MySQL database due to a CPU usage spike. We identified the issue as a query that had been there for several months that suddenly was misbehaving. The ...
- 111
3
votes
0
answers
830
views
Why is PostgreSQL not using composite Index on JSONB Fields?
Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example:
SELECT id from table where (j1->>'f1')::int = 1 AND (...
- 131
3
votes
1
answer
39
views
Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?
I have a big table (30 millions rows) defined like this:
CREATE TABLE `drawing` (
`id` int unsigned NOT NULL,
`artist` int unsigned NOT NULL,
`imageUrl` text NOT NULL,
`uploadDate` timestamp NOT ...
- 131
3
votes
0
answers
4k
views
MSSQL - Unable to find index entry in index ID 1
Currently keep getting this severity 21 error through for a database that is hosted on SQL 2014 Enterprise with SP3 CU1 applied:
DESCRIPTION: Unable to find index entry in index ID 1, of table ...
- 61
3
votes
0
answers
3k
views
ERROR: invalid input syntax for type oid: "english"
While creating an index in Postgres I am getting an error:
invalid input syntax for type oid: "english"
LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',...
The query ...
- 131
3
votes
0
answers
247
views
TokuDB/Percona - Insert Performance worse as table grows
We have a bunch of existing servers which run just fine, inserting our thousand rows/second, but the new batch just will not do it. As the day goes on and the table grows, it gets slower and slower, ...
- 31
3
votes
0
answers
729
views
Searching between two BIGINT
I have a table defined as:
CREATE TABLE [dbo].[IPS_INTEGERS](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_start_integer] [bigint] NULL,
[network_last_integer] [bigint] NULL,
[...
- 39
3
votes
1
answer
3k
views
How to optimize a Full Text Search
So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.
Doing this search slows the query down by about 300ms and since the query is run a few ...
- 294
3
votes
0
answers
259
views
Fast index creation issue
One thing that I can't explain is creating secondary index that doesn't trigger fast index creation as it should. I noticed that while creating secondary index on larger table, it gets actually ...
- 31
3
votes
0
answers
125
views
What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?
According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
- 31
3
votes
0
answers
729
views
Unique Index much bigger than the table itself
Pg 9.3 on debian 7.
I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
3
votes
0
answers
2k
views
How do I recover from MySQL InnoDB table/index corruption?
While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible.
...
- 273
3
votes
0
answers
1k
views
Best practice for storing image order number in a database
I am designing a social networking website (mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:
PicId (BIGINT, AUTO INCREMENT, PK)
...
3
votes
1
answer
695
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)....
- 157
3
votes
0
answers
297
views
Disabled index still incurs overhead when inserting
I have a 38 million row table that I periodically truncate and refill. I disable the indexes (non-clustered) when I do this. But when I included the Execution Plan I noticed SQL Server 2005 is still ...
- 109
3
votes
0
answers
273
views
custom population of full text index
Is there any way to populate a full text search index without storing the backing information in the database?
I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
- 343
2
votes
0
answers
81
views
Query not using foreign key indexes
I have the following schema for my database:
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE employments (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
...
2
votes
1
answer
67
views
Why does creating a compound unique index with an fk and text column fail for mariadb?
I encounter an error when trying to create a compound unique index using a foreign key and a TEXT column.
The error can avoided by explicitly adding an index on the foreign key or changing the type of ...
- 121
2
votes
0
answers
109
views
Why is it so fast to create an index on a table?
While optimizing my query in Postgres, I noted that creating an index is always very fast, less than 1 second on a table with millions of rows.
But if there's no index, a query would take a few ...
- 305
2
votes
1
answer
576
views
How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?
I am trying to debug a slow query, and when I EXPLAIN the SQL, I get a warning:
Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]' ...
- 173
2
votes
0
answers
300
views
Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?
I'm currently using Postgres 12.
A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
- 21
2
votes
1
answer
164
views
Skip (or speedup) postgres unique index creation if I know beforehand that it's unique
I have a large Postgres table (dbeaver says it has about 3e9 rows and almost 4Tb of disk space)
I need an UPSERT, but ON CONFLICT section fields don't contain PK and I can't know it without performing ...
2
votes
1
answer
90
views
Performance, RAM improvement with partitions
I have a table like:
create table table_a
(
id_a mediumint unsigned not null,
id_b tinyint unsigned not null,
id_c char(10) not null,
value tinyint ...
- 45
2
votes
0
answers
70
views
Clustered Index being used over non-clustered with same key
I have a table that has a clustered index (CI) on the field filename.
It also has a non-clustered index (NCI) - With the key column of filename and 5 other columns in the includes. (There are ...
- 29
2
votes
0
answers
199
views
Indexing 250M URLs for infix search in PostgreSQL
I have a very large Postgres table with about 250M rows, and a text column url. I want to be able to search against this in a performant way, eg. SELECT url_id FROM urls WHERE url ILIKE '%somewebsite....
- 183
2
votes
0
answers
741
views
Postgresql 14 Primary Key not working, allows duplicates
I discovered that my 3-column (integers, not null) primary key is allowing duplicates!
Then I created new unique index and replace the current one, and the problem still exists, how is that even ...
- 129
2
votes
0
answers
54
views
Separating tables and indexes in Postgresql (possibly in general as well)
Popularly separation of tables and indexes and putting them on different disks is supposed to improve performance.
Does anybody has some evidence on performance, pros and cons of either solution?
Can ...
- 503
2
votes
0
answers
343
views
Slave index corruption mariadb 10.5
We have a simple mariadb replication with a master and a bunch of slaves. without any prior alert or warning, randomly in our slaves some indexes ( some of them are not present in master) become ...
- 21
2
votes
0
answers
628
views
How to estimate the nonclustered index size before its creation?
I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong.
Scenario 1
Create table mytable1 with clustered index and ...
- 1,253
2
votes
2
answers
354
views
MYSQL Query Index Check
I have a table with the following structure;
CREATE TABLE `operations_copy` (
`id` bigint(22) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`zoneId` int(11) unsigned DEFAULT NULL,
`...
- 121
2
votes
0
answers
85
views
Querying a High Cardinality Field
I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
- 121
2
votes
1
answer
55
views
Is searching faster if indexed values are the same?
Currently I have a collection events with an attribute createdAt of type date (for example 2019/09/09 10:32:12.123).
Because my users search a lot by date, I added an index on this attribute. There ...
- 121
2
votes
1
answer
342
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 ...
- 21
2
votes
0
answers
58
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,
...
- 153
2
votes
0
answers
195
views
Why does indexing take forever on an Aria table?
I have a table using the Aria engine on MariaDB 10.3, it stands at 123.5 GiB in size on an SSD server, with plenty of space available (the disk could fit another copy of the unindexed database + more)....
- 21