Tagged Questions
7
votes
4answers
306 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
57 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
0answers
44 views
one vs two column index difference when doing JOIN query?
Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :
SELECT * FROM alfa
JOIN beta on beta.id = ...
0
votes
2answers
39 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 ...
0
votes
0answers
31 views
The best approach to index dynamic query in MongoDB
I am working on log managment system where user will be able to upload logs from file. I have 'event' collection where I store all events from all sources (each source can have different log format ...
3
votes
1answer
103 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
180 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
69 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
78 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
74 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
80 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 ...
2
votes
0answers
32 views
How can one improve the performance of a query that selects on a low cardinality column in MySQL?
I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
0
votes
1answer
69 views
Mysql. Block/disconnect slow queries
I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches ...
2
votes
1answer
239 views
SQL Server manage indexes
Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
3
votes
3answers
315 views
Is a surrogate key better than a natural key in this case
I copied this code from here:
CREATE TABLE records(
email TEXT REFERENCES users(email),
lat DECIMAL,
lon DECIMAL,
depth TEXT,
upload_date TIMESTAMP,
comment TEXT,
PRIMARY ...
3
votes
1answer
615 views
Should I rebuild index after truncate/large insert?
I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)
Basic outline is very simple:
...
2
votes
3answers
428 views
Slow query on large table with GROUP BY and ORDER BY
I have a table with 7.2 million tuples which looks like this:
table public.methods
column | type | attributes
...
3
votes
1answer
290 views
Is a clustered index locked while a (clustered index) scan is in progress?
The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this.
The problem leading me to this question is that I have a view joining several ...
5
votes
3answers
287 views
Query performance degrades with time and use
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
7
votes
1answer
744 views
PostgreSQL Index optimization with dates
I have a large table of objects (15M rows +), for which I want to query for outdated field. PostgreSQL (9.0.8)
I want to divide the query by millions, for scalability & concurrency purposes, and ...
3
votes
1answer
70 views
query discrepancies on very large postgres table
I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.
these two queries although similar are taking massively ...
7
votes
2answers
549 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?
...
2
votes
1answer
66 views
Does updating a partially indexed field add or remove it from an index in Postgres
We can create a partial index for a field in Postgres.
I am looking at using this to stop rows which are archived from being in an index on a created_at field:
First we create the index:
test=# ...
1
vote
2answers
808 views
Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?
I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). ...
1
vote
1answer
96 views
What would be a optimal index for this query in mysql
I'm trying to optimize this query.
SELECT * FROM sales_reps
WHERE account_id = 1
AND (state = 'enabled')
AND (clients >= 5 OR revenue >= 10000)
ORDER BY name, platform, client_group, ...
3
votes
2answers
250 views
Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?
I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it.
I have a very big table with something like ...
3
votes
2answers
272 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 ...
6
votes
1answer
72 views
Minimizing Indexed Reads with Complex Criteria
I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such:
CREATE TABLE TICKETS (
TICKET_ID id PRIMARY KEY,
JOB_ID id,
ACTION_ID id,
STATUS str256 ...
-2
votes
1answer
756 views
What are performance benefits when using number type instead of using char/varchar?
From various posts, I have summarized about using number type instead of char/varchar for PK:
If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
1
vote
3answers
385 views
Improve performance of a Fact Table
I have a Fact table CardTransactionFact
Table Structure
TABLE [dbo].[CardTransactionFact]
[CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
[TransactionTerminalID] [int] NOT NULL,
...
3
votes
2answers
627 views
Rebuilding index has impact on mdf/ldf files SQL Server 2008
While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files.
What does it exactly means?
Is complete process done on TempDB and does ...
4
votes
4answers
187 views
Can I reorganise index on a sql server 2005 database without performance hit on production server
I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. ...
1
vote
1answer
89 views
Should indexes automatically be placed into their own bufferpool?
We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX.
Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG ...
3
votes
2answers
290 views
SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help
I have a SQL Server table with about 2 million rows in, this table stores short textual documents. Here's my schema:
CREATE TABLE Documents (
documentId bigint IDENTITY NOT NULL,
content ...
3
votes
1answer
183 views
Adding new key(s) to enable partitioning, INSERT performance?
I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 diferent devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
3
votes
2answers
300 views
Slow ORDER BY with LIMIT
I have this query:
SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
@@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
I'm happy with it:
"Sort ...
3
votes
2answers
256 views
What exactly is “pinning” in relation to indexes?
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.
It is part of a test question, where ...
1
vote
5answers
561 views
How should I save apache logs into a mysql table?
This is a php script to view apache logs.
I want to save apache logs into a mysql database.
Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
3
votes
1answer
213 views
Will an index that is a partial key of the begining of another index perform any better than the longer index?
If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches ...
4
votes
5answers
1k views
Improve sort performance in PostgreSQL?
I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
1
vote
2answers
608 views
Improving performance of nasty nested-view joins
I have a moderate-sized database spread out over a few tables, the rough architecture is:
Input Data (Data ID, Session ID and a few fields of statistical importance)
Input File (Data ID and a blob)
...
3
votes
2answers
575 views
Index on primary key not used in simple join
I have the following table and index definitions:
CREATE TABLE munkalap (
munkalap_id serial PRIMARY KEY,
...
);
CREATE TABLE munkalap_lepes (
munkalap_lepes_id serial PRIMARY KEY,
...
0
votes
1answer
545 views
slow index scan on postgresql 9.1
I have a view which is amazingly slow :) It joins two tables only, having a few simple WHERE conditions and one in a form of a subquery (see SubPlan1 below). The simplest condition is checked as an ...
2
votes
1answer
296 views
Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame
System:
I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total).
Issue:
During the Index Rebuild (~20 minutes), we fail to insert data into the DB
...
5
votes
2answers
105 views
Are existing entries immediately included into a newly created index?
Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index:
CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ...
3
votes
2answers
401 views
Performance degradation after rebuilding indexes
I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
2
votes
2answers
156 views
Does the number of indexes effect the modification of table?
First of all Good morning !
Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !!
Now come to the point,
...
7
votes
2answers
696 views
Is there a way to force an index to stay in memory with SQL Server 2008?
I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
1
vote
2answers
265 views
All columns of table should be indexed or not mysql database?
I am having one table with 4 columns. I that one is primary key and other three columns are references to columns of other tables. I am using join of these four tables for search filter. These joins ...
9
votes
1answer
2k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
