Questions tagged [index-tuning]

The process of determining which indexes are useful and which are not.

Filter by
Sorted by
Tagged with
2
votes
1answer
635 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
0
votes
1answer
15 views

Create partial index with single value

I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-...
0
votes
0answers
35 views

MySql Group By Query Taking More 5 seconds On An Indexed Table

I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table CREATE TABLE IF NOT EXISTS `affiliate_analytics` ( `...
0
votes
1answer
219 views

MySQL MyIsam Compressing tables

Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows) Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt ...
1
vote
1answer
217 views

Indexes involved in intervals collision detection

Let's assume an hotel booking application wants to display all customers being present between two dates. Starting with a venue table : CREATE TABLE `venue` ( `id` INT NOT NULL AUTO_INCREMENT ...
1
vote
1answer
26 views

Optimising query: not sure why LOWER() index isn't used

I've got comments that have 1..N comment_notifications. Each notification basically contains a username of the user notified. Each notification can be resolved_at when the user is done with it. This ...
0
votes
1answer
29 views

Use both b-tree and BRIN indexes in PostgreSQL

I have rather large tables (> 1000 M rows) where I have to do quick lookups. They are typically indexed with composite indexes to enable index-only scans like this CREATE INDEX table (study, ...
1
vote
2answers
30 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, `...
0
votes
1answer
346 views

MySQL Query causing high CPU and taking forever to execute

this below query takes a long time to execute in excess of 120 seconds, upon checking the explain plan it seems to be using using where, using temporary; using filesort. Since the application is a ...
0
votes
2answers
33 views

Postgres 10.6: Why is a particular partition's index not being used?

I'm truly stumped as to what's causing this issue and hoping for some guidance. Firstly, this is in our STAGE environment and we partition this table based on the createdTime every month. I have ...
3
votes
1answer
126 views

How to optimize the execution of query that takes too long

I am working with a query that is taking around 53 minutes to execute, which I feel is excessive. Maria DB version: Server version: 10.1.38-MariaDB-1~stretch mariadb.org The declaration of the ...
0
votes
3answers
65 views

Why does SQL Server not use the Index it recommends?

The query I had a specific query I wanted to optimize: SELECT * /*12 columns*/ FROM [dbo].[EnterpriseGroup] WHERE (EnterpriseGroup.ChildId = 123 OR EnterpriseGroup.FatherId = 234) AND StatusCd &...
1
vote
2answers
60 views

Very large table makes the application performance too slow

We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention. I found one table referenced in this app with 1,332,730,...
3
votes
1answer
42 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 ...
5
votes
1answer
286 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: ...
1
vote
2answers
163 views

How to improve query performance?

I have a query that executes very often on our system and there is no option to change the query text. Are there any suggestions on what could be done? Any help would be appreciated. The query: ...
0
votes
1answer
95 views

Optimise primary key and index settings for multiple link tables

I have this tables stated as below. orders is the main table that will store all the details such as the customerID, merchantID, which riderID it was assign. The order details like total, subTotal, ...
3
votes
2answers
57 views

Why is creating and dropping a random index fixing this performance problem?

I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...
2
votes
1answer
12k views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
2
votes
1answer
436 views

Oracle using sub-optimal index on bind variables

Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields). And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third ...
0
votes
0answers
56 views

SQL Server Full-Text Search Order by another Column Performance

I have two tables, Entity and EntityDetails. Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime). EntityDetails table has columns EntityId, Details1, Details2. Where ...
2
votes
2answers
390 views

Updating indexes and stats script

I am having the following errors with the script below Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '?'. Msg 319, Level 15, State 1, Line 47 Incorrect syntax near the keyword '...
1
vote
2answers
33 views

postgres includes listed column in sort key and thus fails to use index

This is a query that occurs reasonably frequently: SELECT DISTINCT id, published_year, first_author FROM publication ORDER BY published_year DESC NULLS LAST LIMIT 10; There is an index that could ...
15
votes
3answers
2k views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT o....
0
votes
1answer
47 views

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how ...
11
votes
1answer
428 views

Which index will be used in this scenario?

SQL Server 2014 Standard Edition I need to find the number of flights that are to and from specific cities for certain months. E.g. select count(*) from flights where flightTo_AirportCode = 'aaaa' ...
1
vote
1answer
3k views

Which stored procedures are using a specifc index

I've been reviewing the indexes in our databases (adding missing indexes and removing bad ones). I stumbled on an index I created months ago which looks way to good to be true. Since the last server ...
30
votes
1answer
25k 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 ...
7
votes
1answer
3k views

Use GIN to index bit strings

I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
7
votes
2answers
4k views

Filter on array text[] and sort on timestamp

Description PostgreSQL 9.6 on Linux, size of tags_tmp table ~ 30 GB (10 million rows), tags is a text[] and has only 6 values. tags_tmp(id int, tags text[], maker_date timestamp, value text) id ...
0
votes
1answer
45 views

Indexing a GROUP BY Select with Multiple Inequalities

I have a pair of linked issues I need help with: Issue #1: How to Index a Table to Optimize for a "Group By" SELECT I have a table of stock price information with the following Create statement: ...
0
votes
1answer
15 views

When using blitzindex should are the reads the number of times the index was used to read or is that the op stats?

Im using blitz index to identify some indexing issues. Im concerned that I am reading the output incorrectly. In my output, im seeing reads, compared to writes in the usage column, and then im ...
0
votes
1answer
23 views

query doesnt use column dedicated index after a certain amount of rows

I have the following table: create schema test; CREATE TABLE test.foo ( info_datetime timestamp NOT NULL, asset_id int4 NOT NULL, price float8 NULL, CONSTRAINT minute_prices_pkey ...
0
votes
3answers
1k views

How to make MySQL use indexes in an integer range select query

I'm doing a select on a table looking for rows that match between an upper and lower bound SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ; the ( simplified ) create ...
0
votes
3answers
130 views

Why is this datetime index not being used?

I have a table like this: CREATE TABLE TestTable ( [TestTableID] [int] IDENTITY(1,1) NOT NULL, [IntField1] [int] NOT NULL, [IntField2] [int] NOT NULL, [IntField3] [int] NOT NULL, [...
0
votes
1answer
20 views

Performance of query from this schema

Essentially all comments are stored in this schema (dbml) table comments { commentID BINARY(16) [not null] //UUID post_date timestamp postID BINARY(16) [not null] //UUID has_reply bool [...
0
votes
1answer
69 views

Speeding up “duplicate” checks

We have an events table that is reasonably large (~35 million rows) The original table is like this, CREATE TABLE [dbo].[Events]( [EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [...
1
vote
0answers
31 views

Which order should the columns in a composite key as a foreign key be specified?

Looking at the code from this answer which uses a composite key to control which child tables can contain the parent key, I noticed he specifies the primary key of the foreign table first, then the ...
2
votes
1answer
76 views

best way for creating Index in mysql (multi-column/table)?

i have a big problem with my database.. I need a very fast query over 2 tables. 1. table: (node) ~42.500.000 entrys 2. table: (waynode ~51.200.000 entrys The following query is much to slow: ...
1
vote
1answer
425 views

What index can I create to help a query using multiple datetime columns and datetime functions in its where clause?

SQL Server 2016 I have the following query: SELECT [Id], [Descriptor] FROM [Networks] WHERE ( [ValidFrom] <= SYSDATETIMEOFFSET() AND SYSDATETIMEOFFSET() < COALESCE ...
3
votes
1answer
134 views

How can I improve the performance of this query on a table with millions of trigrams?

In an effort to make searching across many string fields in certain tables faster, I've been attempting to use trigrams. I've created a separate table to hold them, and a query to search against them ...
1
vote
2answers
58 views

Retrieving data from PostgresQL database with millions of rows takes very long

I've been working on a system where users can register as an user, create a book club and invite other people (members) to join. The user and member can all add books to the club, and also vote for ...
0
votes
0answers
18 views

Would a set of non-clustered index work better than a clustered index for my large table?

I'm trying to create an index over a table with potentially 100 million rows. Right now I have the following columns: name="date", type ="DATE" name="account_id", type ="INT64" name="group_id", type =...
0
votes
1answer
29 views

Does the order for clustered index have the same constraints as non-clustered index on search?

I have a table filled with a bunch of data, and I want to use the following clustered index: [account_id, group_id, run_id, page_id, date] For a non-clustered index, if I were to to use WHERE ...
0
votes
1answer
67 views

How does an index speed up insertion in a table

How does index speed up insertion in a table? I have an idea how it happens for retrieving the rows and selection of data, but how does it helps in insertion? CREATE TABLE persons( id NUMBER , ...
0
votes
2answers
133 views

Worse query performance even after improving the query by making it to run in parallel mode

I have a SQL Query which gets generated from application using EFF. It uses a view and this view has joins to multiple tables in there. The query runs slow and when i looked at the execution plan,i ...
26
votes
2answers
19k 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 ...
0
votes
1answer
136 views

What index(es) and queries for multiple column ranges search?

Based on a simple innodb mysql 5.6 database schema like this: What is the optimal indexing strategy for querying the "info" table where most of the search criteria will be IN and BETWEEN conditions? ...
0
votes
1answer
50 views

Optimise XOR search on int list

I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int). Another large table (TableB) contains the same ID column with some other ...
0
votes
1answer
189 views

Performance recommendations stuck on 'Validating'

I have been tasked with migrating a 'database from hell' to Azure SQL. It has some complicated and long-running queries (20 seconds on the S6 tier, >600 seconds on S4 tier) and I'd like to optimise ...

1
2 3 4 5
14