A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
3
votes
2answers
319 views
PostgreSQL Sequential Scan instead of Index Scan Why?
Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
4
votes
1answer
64 views
Covering Index Question
I have the following query
Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED,
PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND,
CASE Left(PT.INVENTREFID, 3)
WHEN ...
0
votes
2answers
153 views
Large time difference between two almost identical simple queries…why?
I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:
SELECT ...
2
votes
0answers
30 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
46 views
Faster joining on 'most recent' related rows
I have a bug tracking application. The bug table stores very little,
because every state change to a bug happens by adding a comment. For
example, the current state of a bug is determined by the state ...
2
votes
3answers
172 views
Is it better to seperate a big query into multiple smaller queries?
There are situations which require to have really big query joining several tables together with sub select statements in them to produce the desired results.
My question is, should we consider using ...
3
votes
1answer
132 views
How to search for an ID and its descendants in this query?
I have the following tables:
Project Unit
------------- ---------------
ID | IDUnit ID | IdParent
I have to select all projects that have the IDUnit ...
2
votes
1answer
55 views
Are there differences in performance of getting data between FullText Search and indexed columns
In SQL Server 2008 R2 I have a query which looks like
create stored procedure give_me_art @filter varchar(15),@skl_id int
begin
select
id,naziv,sifra,isnull(lager.kolicina,0) as lager
from art
...
2
votes
0answers
134 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
1
vote
1answer
102 views
Query is not using indexes on third table in left join
My query is not using indexes on third table(pci promotion_coupon_images) .
i was tried with using index and force index in query but there is no change in result.
any body please suggest me how ...
6
votes
3answers
523 views
SQL Database Structure for RESTful API
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.
Initially, I though a table per resource would be a good way to go, but I'm ...
0
votes
1answer
77 views
Create a VIEW that spans 3 tables
Sorry if the title is not clear - feel free to improve.
Here are 3 tables (not all data is relevant to the question):
CREATE TABLE `content` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
2
votes
1answer
359 views
MySQL 5.5.8 InnoDB Foreign Key, JOIN performance
We are using aws/rds, MySQL 5.5.8. all InnoDB.
For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
5
votes
1answer
110 views
Logical reads amount difference with almost identical indexes
I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server.
The mentioned query is quite simple, ...
1
vote
1answer
33 views
simple query is not using indexes
My query is not using indexes.
it was taking around 5 seconds.
output is 500 rows only. it is a simple query
Please find the explain plan and table structure here.
Explain Plan
mysql> explain ...
0
votes
0answers
73 views
Performance Optimising MySQL query
I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5)
I was wondering if there is any way to enhance this query so ...
0
votes
1answer
75 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
1
vote
2answers
61 views
Efficient way to query set of rows from MySQL
I am using MySQL database engine. My Java application uses simple JDBC to retrieve data from database.
I have following database schema: main table with two columns: (INTEGER id, BLOB data). id is ...
0
votes
1answer
59 views
Query taking long time
My query is taking around 540 seconds to get result.
Please help me to rewrite it.
Query:
SELECT pd.domain, fd.passkeyid
FROM portfolio.domains AS pd,
fabulousdomains.domains AS fd
WHERE ...
3
votes
3answers
200 views
PostgreSQL 8.3: Slow GROUP BY on large table
I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow...
select run_id, count(*) from result group by run_id;
I have an index ...
9
votes
1answer
376 views
query optimization: time intervals
In the main, I've got two kinds of time intervals:
presence time and absence time
absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
4
votes
1answer
100 views
In SQL Server, what are the typical symptoms of parameter sniffing?
There is a wealth of information on the DBA.SE about what parameter sniffing is and how it relates to individual query performance circumstances. I was hoping that someone could provide some sort of ...
2
votes
1answer
142 views
Optimize simple query in SQL Server
This should be a quite easy query, but I honestly think its execution time can be improved.
select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) ...
1
vote
1answer
69 views
Dealing with large log files
I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers. The two largest tables are of ~50 million and ...
3
votes
1answer
197 views
Slow SQL Server query using a DB2 linked server in virtual environment
I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue:
Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 ...
2
votes
0answers
155 views
Simple Select * on SQL Server 2008 R2 table of 1812 rows is taking 3 minutes to load
A query on a table of 1812 rows is taking over 3 minutes to execute. Is there a reason this should be happening?
If I execute a
SELECT *
statement on any other table the results pop back ...
4
votes
1answer
118 views
Same Query takes 0 seconds on Server A, 7.5 minutes on Server B (same db/hardware/config)
I have a query (sql below) that is nearly instantaneous on 1 server, but takes 7.5 minutes on another virtually (key point) identical server. The estimated query execution plans have one obvious ...
1
vote
2answers
165 views
Query Performance Tuning on a huge table
I have the following table Transactions. The table looks like this:
CREATE TABLE [dbo].[Transactions](
[TransactionID] [bigint] IDENTITY(1,10) NOT NULL,
[PlayerID] [bigint] NOT NULL,
...
1
vote
0answers
72 views
How can I use mongoDB efficiently in this scenario?
I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned:
...
2
votes
2answers
110 views
Slow MySQL query with Order and Group by
I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query:
SELECT `user_articles`.*
FROM `user_articles`
...
2
votes
1answer
224 views
Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields
I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).
Furthermore I'd like to show ...
3
votes
2answers
1k views
How does paging work with ROW_NUMBER in SQL Server?
I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee ...
3
votes
1answer
236 views
Query Performance Issue
I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this.
Version : Sql Server 2008 R2 Enterprise
No fragmentation . ...
12
votes
2answers
2k views
Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
2
votes
1answer
71 views
Slow Query Gets Even Slower After Indexing
I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number:
select * from my_table
where
...
3
votes
2answers
61 views
Performance of different precedence pseudocolumn solutions with LIMIT
I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests.
The OP wanted to get the first matching row given ...
4
votes
2answers
176 views
Performance on SQL Join
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.
The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for ...
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, ...
1
vote
0answers
502 views
SQL query optimization in Prestashop e-Commerce solution
I'm working on a layered navigation module within Prestashop e-Commerce solution.
I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
3
votes
1answer
93 views
Why does SqlSentry Plan Explorer not give the duration of my queries?
I have a large nasty stored procedure in a 13gig database that takes 45 minutes to run when I include the actual execution plan. I save the query plan from SSMS and open in up in SQL Sentry Plan ...
1
vote
1answer
61 views
Performance impact of including result column in indexed columns
I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of:
texelid ...
4
votes
1answer
126 views
help required with indexing and query optimzation
After browsing most of the posts and after running out for options I am posting here.
I have a table with 76,222,954 (76M) rows. It collects web site traffic based on country.
Here is my table ...
3
votes
2answers
62 views
Looking for a possible multi-column index
I have a table highscore containing the columns:
game (text)
date (timestamp)
score (integer)
more irrelevant ones...
The query most often run on it is:
SELECT *
FROM highscore
WHERE game = ...
4
votes
2answers
558 views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
2
votes
2answers
106 views
Query taking too much time
My query is taking too much time to execute. Please help me here.
mysql> explain
select pcm.catalog_id
from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c
...
1
vote
2answers
106 views
How to optimize this query?
Query:
Select *
from `t_event`
where `create_user_id`=7
and (`event_create_date`)=('2012-12-18 00:00:00')
and `event_type_cd`=11
and `domain_id` =602
and `job_id` =1
limit 1
Table structure:
...
0
votes
1answer
57 views
Long running query
This is my long running query:
select *
from t_keyword
where id in
(
select rel.element_id
from t_event event
join t_event_element_rel rel on
event.id = rel.event_id
where
...
11
votes
3answers
454 views
Improve performance of query using IN()
I have the following SQL query:
SELECT
Event.ID,
Event.IATA,
Device.Name,
EventType.Description,
Event.Data1,
Event.Data2
Event.PLCTimeStamp,
Event.EventTypeID
FROM
Event
INNER JOIN ...
0
votes
0answers
44 views
Query to fetch missing siblings [closed]
DECLARE @AName VARCHAR(100)
DECLARE @BName VARCHAR(100)
DECLARE @CName VARCHAR(100)
SET @AName = ''
SET @BName = ''
...
1
vote
1answer
84 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 ...
