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

learn more… | top users | synonyms

0
votes
0answers
22 views

Is there some tricks to better index a URL column in postgres?

I'm working with a table that needs fast retrieve rows by its url column. But they all will be starting with http://www.: http://www.google.com http://www.amazon.com http://www.stackoverflow.com I ...
2
votes
2answers
147 views
+100

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
5
votes
1answer
131 views

Sudden poor SELECT performance on a large table with existing indexes

My company uses a software suite in which the only thing I can truly modify is the database. We've always had performance issues (mostly hardware problems that we hope to resolve soon), but lately ...
1
vote
1answer
25 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...
1
vote
1answer
74 views

Help on deciding how to pick Correct Index

I would like to ask for some help to create correct indexes for this query: SELECT DISTINCT D.TypeID FROM dbo.Sizes AS S WHERE EXISTS ( SELECT 1 FROM #PermissionsTable AS PT ...
0
votes
1answer
66 views

Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)

We have a huge query on an Informix database that is not performing well. The query is doing a join of 9 different tables. Maximum row count is just 40,000 rows. The query also has 9 sub-queries, and ...
0
votes
1answer
35 views

How to determine if an Index is uselss in Percona MySQL?

Using Percona Server 5.5 I've a table which stores user activty (lets call it user_activity). It was created quite a few years ago when I had only a little bit of knowledge about database ...
3
votes
2answers
62 views

Mysql Index for concat select

For Below Query SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE TRIM(LEADING 0 FROM card_no)='2948' I've index on date card_no date,last_entry ...
1
vote
1answer
80 views

Index to filter a view before joining the underlying tables [closed]

Using SQL Server 2008 R2 Suppose you have a query like the following select top 1 * from view_cms_user where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n' view_cms_user is basically: ...
0
votes
1answer
32 views

What is the risk of doing a rebuild to my indexes in sql server 2012?

What is the risk of doing a rebuild to my indexes in SQL Server 2012? I have to do a REBUILD, but I don't know what are the risks . The server is running and a lot of information is going in and out. ...
0
votes
1answer
38 views

Indexes in a big table. (About 400 mil of rows)

I have a little deal. I have this table in a SQL Server 2012 Standard edition database. The table contains about 400 mil of rows. CREATE TABLE [dbo].[prodeje] ( [id] [int] IDENTITY(1,1) NOT ...
0
votes
0answers
28 views

PostgreSQL: raise error if planner needs to do an sequence scan

I want to check if our code does use only indexed columns. I think it does, but I want to be sure. How can I configure/modify PostgreSQL to raise an exception if it needs to do an sequence scan? It ...
0
votes
0answers
55 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
0
votes
2answers
42 views

SQL Server 2005 query with multiple conditions, some indexed, some not

I have a table with 50 million rows. It is indexed on zip. A query Select * from table where zip = '12345' returns 20,000 rows in about 2 seconds When I add an additional condition it jumps to ...
3
votes
1answer
95 views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
1
vote
0answers
60 views

Multiple SQL Server Indexes, which ones to keep?

I am looking at my database that several people have worked on. A particular table is responding slowly when queried on a certain join. I notice some of the parameters of the join are not indexed. ...
0
votes
1answer
61 views

Clustered index on a column with low selectivity

I have a table with a lot of columns. Two lookup functions are performed: 1) lookup by using column name (this happens rarely, maybe twice a season), and 2) lookup by column date. The column name has ...
1
vote
1answer
52 views

Rebuild vs Reorganize [duplicate]

I currently have a maintenance plan that is reorganizing indexes everyday and I would like to know if you reorganize an index everyday would you ever need to rebuild the index? If so, why?
0
votes
0answers
44 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
2
votes
0answers
63 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 ...
1
vote
1answer
34 views

Postgres - Insert performance with indexes

We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow ...
0
votes
0answers
27 views

When I can DROP an index safely? [duplicate]

I would like to get rid of unused indexes in my database. I have identified many indexes that are never used (seeks, scans and lookups are 0), and are being updated during DML operations. I used ...
0
votes
1answer
40 views

One Non-Clustered Index that covers every possible combination of predicates?

Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates. So, for example, if we have a table with columns A, B, C, etc we ...
4
votes
1answer
63 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? ...
1
vote
1answer
74 views

Poor query performance due to recheck condition on index scan

We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server. I get a problem when ...
0
votes
0answers
16 views

Index creation issue with MySql and Linux

MySQL Server database is installed on RHEL5.5 and db version is 5.6.20 Community Server. In MySQL database we have a table and it has 11,144 records. I am altering the table and adding index to it. ...
2
votes
2answers
123 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 ...
2
votes
1answer
55 views

Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

Let's say I have a table with 1 000 000 records. Structure of table is: create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic ...
1
vote
1answer
39 views

Performance of Combination lookup

I've been looking into this most of the day and I'm still not getting far so I thought I'd ask on here. Basically its a lottery system and what I need to do is check whether 4 of the 6 balls I have ...
0
votes
1answer
60 views

Order of Columns in Index

I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as ...
-3
votes
1answer
168 views

Ola Hallengren Index Optimise Script not Rebuilding Indexes [closed]

I have downloaded Ola Hallengren's script and deployed to master database. I run it using the below... EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, ...
1
vote
2answers
96 views

Postgres Index a query with MAX and groupBy

Is there any way to index the following query? SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id; I've tried creating sorted (non-composite) indexes on frame and ...
0
votes
1answer
44 views

Delete index while query is running, is it possible?

I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still ...
1
vote
1answer
72 views

Duplicate non-clustered index on Primary Key, can I drop one of them?

Looking over on of our older databases I noticed that several large tables had duplicate indexes on the Primary Key. Both indexes are Non-clustered, Unique and only includes the PK-column. The ...
0
votes
1answer
84 views

Why the optimizer does not use an Index Unique Scan?

I am on Oracle 10.2.0.4.0. I have a big Index Organized Table (IOT) with 74 columns and over 345 million of rows. An external tool execute a batch where I cannot touch the source code. The critical ...
2
votes
1answer
102 views

Indexing nvarchar(128) in SQL Server

Have no idea how indexing works in SQL Server. Working with a SQL Server 2012 Intel Xeon 2.2 GHz dual core 8 GB RAM. Will indexing a nvarchar(128) in addition to int primary key cause any ...
0
votes
0answers
15 views

MongoDB Sparse Index Query

I have around 40 million records in my collections but some million documents doesn't contain a field "catId". Sample Document : { "_id": ObjectId("5458b1a24669c5c0d66ae658"), "host": ...
2
votes
1answer
127 views

Can PostgreSQL use multiple partial indexes per query?

I have read that PostgreSQL can use multiple indexes in general, but in the specific case of a query that ranges across two indexes, will it use both? If so, will they be loaded sequentially or ...
3
votes
1answer
138 views

Is a clustered index on a child table in a parent/child relationship the most optimal index?

A clustered index will sort row data in a table according to the ordering of the fields in the index. If so, why would you (or would you not) want to create a clustered index on the child table in a ...
1
vote
0answers
80 views

Rebuild/Re-organize index and Update statistics in SQL server

For many of the sql servers we have seen that the SQL agent Maintenance job first runs the re-indexing and later as a second step it update stats with full scan. I want to figure out if auto update ...
1
vote
1answer
43 views

Does the length of the index name have any performance impact?

This may seem silly, but in trying to stick to my standard nomenclature when naming indexes in our DB, the latest one was a little lengthy, and like all things DB I wondered if it in any way could ...
2
votes
0answers
46 views

Why would a default constraint be missing an index?

I have a query that looks at the sys.dm_db_missing_index_group_stats system view to determine what missing indexes there are for maintenance purposes. If I remove the filter from the query that ...
1
vote
1answer
126 views

Index file growing endlessly - how to fix it?

I need to do some maintenance on a database on our client's site. Both log and index files were huge (Don't know how they took their backups to end this way) anyway I fixed the log file (.ldf) issue, ...
3
votes
2answers
125 views

Is there a way I can speed up queries against this particular view?

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...
0
votes
1answer
30 views

Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed?

I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering ...
0
votes
1answer
31 views

Reusing covering index for fields in FROM, from another query's index

For the following query I have a covering index as so: SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent; ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id); Now I have ...
5
votes
1answer
151 views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
0
votes
0answers
38 views

Why creating the missing index still shows it in the missing index dmv?

I followed the steps in this blog post to create the missing index and to clear it from the missing index dmv after running a select statement which uses the indicated column in the where clause and ...
2
votes
0answers
89 views

Covering Index - Insert Performance

We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables. When I try ...
0
votes
1answer
35 views

How to order the where clause and join columns in a covering index?

Simple query example: Select t1.A, t1.B, t1.C, t1.CustomerID from table1 t1 join table2 t2 on t1.CustomerID = t2.Id where t1.A = 'x' and t1.B = 'xx' and t1.C = 'xxx' I created a non clustered with ...