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.
390
questions
123
votes
3
answers
59k
views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
- 1,661
144
votes
8
answers
231k
views
Pattern matching with LIKE, SIMILAR TO or regular expressions
I had to write a simple query where I go looking for people's name that start with a B or a D:
SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1
I was wondering if ...
- 1,785
44
votes
1
answer
16k
views
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is:
922475bb-ad93-43ee-9487-d2671b886479
There ...
- 683
145
votes
4
answers
161k
views
Optimizing queries on a range of timestamps (two columns)
I use PostgreSQL 9.1 on Ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
- 8,501
41
votes
2
answers
42k
views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about one ...
- 711
29
votes
4
answers
6k
views
Can spatial index help a "range - order by - limit" query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
- 97.4k
93
votes
5
answers
41k
views
Working of indexes in PostgreSQL
I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:
Friends ( user_id1 ,user_id2)
user_id1 and user_id2 are foreign keys to ...
- 1,963
16
votes
3
answers
19k
views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
- 1,271
16
votes
2
answers
9k
views
Reasons for occasionally slow queries?
We are running MySQL 5.1 on Windows Server 2008 R2.
We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
- 600
56
votes
3
answers
48k
views
What is the optimal data type for an MD5 field?
We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute).
There is a table names that serves as a sort of central registry. Each row has a ...
- 755
70
votes
3
answers
106k
views
Optimizing a Postgres query with a large IN
This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.
With this style of query, the obvious optimization ...
- 1,033
108
votes
3
answers
166k
views
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t (
k int,
v int,
index k(k)
) engine=memory;
insert into t (k, v)
values (10, 1),
(10, 2),
(10, 3);
When ...
- 1,298
4
votes
2
answers
5k
views
Can Postgres use an index-only scan for this query with joined tables?
This is a follow-up to: Do covering indexes in PostgreSQL help JOIN columns?
Consider the inverse of the schema in the other question where you filter in the joined-on table:
CREATE TABLE ...
- 669
16
votes
2
answers
6k
views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
- 331
9
votes
2
answers
1k
views
Index that is not used, yet influences query
I've got a PostgreSQL 9.3 table with some numbers and some additional data:
CREATE TABLE mytable (
myid BIGINT,
somedata BYTEA
)
This table currently has about 10M records and takes 1GB of ...
- 289
129
votes
7
answers
150k
views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
- 1,393
6
votes
2
answers
1k
views
Optimize a query with small LIMIT, predicate on one column and order by another
I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times:
Query #1
EXPLAIN ANALYZE SELECT posts.* FROM posts
WHERE posts.source_id IN (...
- 239
44
votes
4
answers
56k
views
Why is my database still fragmented after I rebuilt and reindexed everything?
I have a database which I tried to defragment all the tables at once by running this T-SQL:
SELECT
'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
'ALTER INDEX all ON ' + ...
- 2,677
28
votes
2
answers
16k
views
"Recheck Cond:" line in query plans with a bitmap index scan
This is a spin-off from comments to the previous question:
Postgres 9.4.4 query takes forever
Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
- 172k
18
votes
2
answers
41k
views
Do covering indexes in PostgreSQL help JOIN columns?
I have a whole lot of tables that look vaguely like this:
CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES ...
- 669
14
votes
2
answers
8k
views
space usage on sys.allocation_units and sp_spaceused
It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows.
However, when you have the stats updated, I can't see why they wouldn't.
I am working on a ...
- 15.8k
11
votes
1
answer
21k
views
MySQL Index creation failing on table is full
UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space.
Original Q:
I'm trying to add an index to an InnoDB table, and ...
- 969
7
votes
1
answer
1k
views
PostgreSQL partial index unused when created on a table with existing data
In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/...
- 607
274
votes
7
answers
147k
views
When should I use a unique constraint instead of a unique index?
When I want a column to have distinct values, I can either use a constraint
create table t1(
id int primary key,
code varchar(10) unique NULL
);
go
or I can use a unique index
create table t2(
id ...
- 12.2k
53
votes
1
answer
52k
views
Indexes: integer vs string performance if the number of nodes is the same
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
- 633
33
votes
3
answers
41k
views
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
- 5,333
8
votes
1
answer
1k
views
Does MySQL still handle indexes in this way?
Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index.
If a ...
- 869
7
votes
1
answer
6k
views
Unused index in range of dates query
I have a query that is not using existing indices and I do not understand why.
The table:
mustang=# \d+ bss.amplifier_saturation
Table "bss....
- 173
38
votes
1
answer
76k
views
Index optimization with dates
I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field.
I want to divide the query by millions, for scalability & concurrency purposes, and I ...
- 561
22
votes
7
answers
2k
views
Where can I find some guidance on index strategies?
Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded.
As a general rule, which fields should be indexed?
Which fields should ...
19
votes
3
answers
30k
views
Index max row size error
Is there a upper bound for an array column?
I am getting this error when inserting into the array field -
PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data"
Here's my ...
user310525
7
votes
4
answers
17k
views
Benefits of BTREE in MySQL
What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage?
Does BTREE provide easier iteration in increasing order ?
What kind of queries would ...
- 11.5k
6
votes
1
answer
10k
views
Character varying index overhead & length limit
I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million.
I have read about B-Trees, but I can't determine how much disk ...
user32234
33
votes
2
answers
16k
views
Custom unique constraint, only enforced if one column has a specific value
Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is 'true', then I want the ...
- 1,019
26
votes
2
answers
38k
views
Slow index scans in large table
Update 2020-08-04:
Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are ...
- 429
26
votes
1
answer
7k
views
Composite Primary Key in multi-tenant SQL Server database
I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will ...
- 361
16
votes
3
answers
36k
views
Why does MySQL ignore the index even on force for this order by?
I run an EXPLAIN:
mysql> explain select last_name from employees order by last_name;
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+
| ...
- 1,003
9
votes
2
answers
9k
views
How to create an index on an integer json property in postgres
I can't figure out for the life of me how to create an index on a property of my json column which is an integer.
I tried it this way (and also dozens of others)
CREATE INDEX user_reputation_idx ON ...
- 1,533
8
votes
3
answers
6k
views
Speed up creation of Postgres partial index
I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4.
My data is completely static, so I am able to insert all data, then create all indexes.
In this 1.2TB table, ...
- 275
3
votes
2
answers
1k
views
MySql - find redundant indexes
is there a tool that spots redundant indexes in MySql?
e.g. if I have the following indexes:
index1(col1)
index2(col1,col2)
then index1 should be flagged as redundant.
any ideas?
- 1,513
2
votes
1
answer
364
views
Combining columns in index
With reference to RolandoMySQLDBA's answer on
MySQL: Index when joining to tables not being used (Performance optimizing question)
He mentioned
Make sure you build an index that involves the ...
40
votes
4
answers
17k
views
Why does MySQL not have hash indices on MyISAM or InnoDB?
I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
Alex
35
votes
7
answers
171k
views
Copy complete structure of a table
Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:
select * into dbo.table2 from dbo.table1;
This is just a simple copy of the ...
- 1,999
29
votes
2
answers
25k
views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
- 887
22
votes
2
answers
14k
views
LIKE uses index, CHARINDEX does not?
This question is related to my old question. The below query was taking 10 to 15 seconds to execute:
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
...
- 3,131
19
votes
3
answers
4k
views
Is there a reason to update statistics manually?
In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?
- 5,333
16
votes
1
answer
14k
views
Best index for similarity function
So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column.
The queries can be:
SELECT "lca_test".* FROM "lca_test"
WHERE (...
- 343
11
votes
1
answer
5k
views
Unexpected Seq Scan when doing query against boolean with value NULL
I have a database column called auto_review where column type is boolean.
There is an index for that field, created using the ActiveRecord ORM.
CREATE INDEX index_table_on_auto_renew ON table USING ...
- 341
11
votes
2
answers
36k
views
Find rows containing a key in a JSONB array of records
I'm trying to query for a key present in an array of objects. This structure:
column jdata
{"name": "Somedata",
"array": [ {"name":"bla1", "attr&...
- 141
9
votes
2
answers
8k
views
Why is MySQL not using the index with the higher cardinality?
I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
- 197