0
votes
1answer
37 views

MySQL: How index speed up query execution

I have read multiple articles about MySQL indexes. Some of the important pages are below: http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html ...
1
vote
1answer
104 views

Why is this second query more expensive than the first?

While reading Graph Databases book, I came across this excerpt: I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person. May anyone ...
0
votes
0answers
31 views

Mysql table with only two columns, nothing unique: Will an index increase search performance?

If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search ...
2
votes
1answer
82 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 ...
1
vote
0answers
38 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: ...
2
votes
0answers
95 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
1
vote
1answer
57 views

MySQL from table to table data transfer

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create ...
0
votes
1answer
154 views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
0
votes
1answer
129 views

MongoDB insert performance rapidly dropping

I'm currently experiencing a rapid decrease of the insert Performance. I have a rather simple data model { "Id": "ObjectId", "SomeProp": "xy", "SomeTags": [ { "key": ...
2
votes
1answer
82 views

Will adding more indexes speed up using RedGate SQL DATA compare?

We want to use RedGate's SQL Data Compare to migrate user data from one DB to a DB on a completely different provider. This will be a time-sensitive operation as we do not want our site offline for ...
-2
votes
2answers
65 views

Index is not used with same Query on two Servers [closed]

I have two notebooks with same specs (same model, only ram is 24GB/32GB, same ssd) and exact same database and same sql server version. SQLIO is 3300 and 3600. Now I run a query on one notebook and ...
0
votes
1answer
45 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
6
votes
2answers
299 views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
1
vote
0answers
37 views

Database tuning and possibly unexpected results

(This is cross-posted from stackoverflow; I initially put it in the wrong place.) So I have a database that's working a bit slower than I would expect and I'd like to describe what I'm doing so ...
0
votes
0answers
139 views

index usages on table with recursive relationship

we are working on a project that uses jpa and oracle. In this project we have a table with a recursive relation: Create table alarm ( id varchar2(200), ... alarmcode varchar2(50), parent_id ...
2
votes
2answers
102 views

SQL Server 2008 R2 Missing Index

I am performing index maintenance on a database. I have run SQL script that uses DMVs to provides a list of indexes SQL Server believes should be created. I am not creating indexes just because the ...
0
votes
1answer
134 views

PostgreSQL - Return unique combinations of columns based on where clause

This is quite a long question, please bear with me. So I'd like to first explain I have a database of firewall logs created using the following command: CREATE TABLE firewall_logs_mapped ( ...
2
votes
1answer
290 views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
0
votes
1answer
36 views

Should the value of a select max be indexed when the where clause is already indexed

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company ...
0
votes
2answers
143 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
1
vote
1answer
76 views

How is the number of records on a select determined as high or low by optimizer?

What is the threshold between using the index and doing an direct access to the database? I assume this decision is related to the number of index access (and subsequent access to the tables). If this ...
1
vote
1answer
183 views

Using coalesce on an index column

Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way? I think that it ...
0
votes
1answer
134 views

Best read performance when every query is known, and the data doesn't change

I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or ...
2
votes
2answers
577 views

MySQL - Add index to low cardinality text column?

I'm working on project where an enum column is being converted into a text column (I cannot change this). The cardinality of the column is low (7 unique values). Would I gain a performance increase by ...
5
votes
2answers
2k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
0
votes
3answers
404 views

Performance of primary key IDs with gaps (but in sequential order)

I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e: 154300000 283700000 351300000 464200000 ...will ...
0
votes
0answers
320 views

Is it possible to optimize a range query so an explain statement would state “using index” instead of “using index condition”?

I have a very large table where specific queries made by my application are timing out. The table looks something like this: LargeTable ------------ Id [BIGINT(20)] UtcTimestamp [BIGINT(20)] Pivot ...
2
votes
2answers
2k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...
1
vote
1answer
115 views

Benefit to keeping a record in the database, rather than deleting it, for performance issues?

So I have a client that I am building a Rails app for....I am using PostgreSQL. He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I ...
1
vote
2answers
276 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
0
votes
2answers
81 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
0
votes
2answers
276 views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
1
vote
1answer
466 views

How to partition an Oracle table with 65 million rows?

The table that needs partitioning contains a list of Components and their Values. So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the ...
1
vote
2answers
3k views

Index for date column for range query in Oracle?

We have a lot: select count(*) from TBL where XDATE between ? and ? and FLD = ?; We expect to boost performance by tuning index on XDATE column (which have DATE type). What type of indexes more ...
1
vote
1answer
70 views

What indexes to add on MySQL table

I've been trying to optimize my table, but am yet unable to get the desired results. CREATE TABLE IF NOT EXISTS `puzzles` ( `puzzleID` int(7) NOT NULL, `puzzleMask` varchar(30) CHARACTER SET utf8 ...
2
votes
4answers
348 views

When do I use another type index than integer? (eg string) [integer vs string index]

I was reading about relational databases like sqlite and I always see the schema for user storage like: UserID (index) | Username | Password but wouldn't it be actually much faster to use Username ...
0
votes
1answer
70 views

Indexes and Optimizations for Logging System

I'm designing tables for a new log system and am having trouble figuring out what indexes I'll need (I'm a developer, not a DBA), and am also open to any suggestions/criticisms/etc. for making the ...
0
votes
1answer
287 views

Postgresql table with one integer column, sorted index, with duplicate primary key

I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be ...
4
votes
2answers
209 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
1
vote
4answers
473 views

MySql Order by isnull() Performance Problem

my sql below is use for listing stock added from 10 days ago. Order by isnull(Price) is use so that stock without any price yet will still being listed. AddDate and Price has an index. SELECT Id, ...
0
votes
1answer
93 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
10
votes
4answers
5k views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...
3
votes
1answer
84 views

How can I get this query to use it's index?

Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime). As I don't really know what I'm doing I tried adding indexes to all columns in ...
0
votes
2answers
276 views

Is there slowdown inserting into an InnoDB table that has no index set?

I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such. Those tables only contain a few thousand rows. Would it be faster to INSERT data ...
3
votes
1answer
2k views

Why is a hash match operator in this very basic query

I'm beginning to learn some about looking at execution plans and making queries more efficient Consider these two basic queries select distinct pat_id, drug_class, drug_name from rx select pat_id, ...
6
votes
1answer
294 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
5
votes
1answer
216 views

RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits? The last two weeks one of our applications has had downtime due to ...
4
votes
1answer
156 views

After Rackspace server creation, PostgreSQL query planner doesn't work as expected

We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
1
vote
1answer
135 views

Consolidating indexes

I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
2
votes
1answer
355 views

How to improve INSERT speed in large table that has unique constraint

I have a simple MyISAM table: explain entities; +------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra ...