A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
2
votes
1answer
147 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()) ...
2
votes
3answers
102 views
mariadb ignoring force index
I'm trying to tune a relatively simple query:
select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
...
2
votes
2answers
1k views
Will altering order of columns in GROUP BY affect performance?
I have a SQL query like this:
SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D)
FROM SomeTable
GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END)
On a huge dataset the actual execution plan ...
2
votes
1answer
130 views
Need some help with this query get all status and their associated comments of me and all my friends
I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
2
votes
2answers
398 views
How does compound index help speed up query?
Say we have a bunch of column in mysql table.
Latitude Longitude
Say we make an index (Latitude and Longitude)
Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
2
votes
5answers
264 views
Query optimization [Oracle]
The Query:
select JOIN_RESULT.batch_id as BATCH_ID,
JOIN_RESULT.unique_doc_id as DOCUMENT_ID,
JOIN_RESULT.brand as BRAND,
JOIN_RESULT.message_date_time as UPLOAD_DATE,
...
2
votes
2answers
97 views
Different queries result in big difference in performance
I have 2 similar functions, different way result in big diff in performance.
The PostgreSQL version: 9.2.1.
Function 1
create or replace function func_1() returns text as
$$
declare
v_i ...
2
votes
2answers
123 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
108 views
how to improve query performance
## Only 1 query which returns 10 rows.
## It took about 66 secs
mysql> explain
SELECT al.log_id , al.user_id , al.page_name ,
al.section_name , al.submit_time , al.url ,
...
2
votes
1answer
163 views
Why are my queries getting really slow?
I own a WordPress installation, and Updated to 3.4.1.
After the upgrade, I experienced a very strange slow-down.
After all, I used the
define('SAVEQUERIES', true);
in my wp-config.php with ...
2
votes
1answer
100 views
Where to place non clustered index
Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
2
votes
3answers
139 views
How to get all non deleted messages from one user
I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:
CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
1k views
I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation
I want to get a dedicated server for my Database (MySQL) which runs InnoDB Engine.
There is this option in InnoDB capacity that you can define the IOPS in it. Let's say
that I have a SATA 72K-RPM HDD ...
2
votes
2answers
212 views
How expensive are SELECT FROM LIKE statements from a design perspective?
I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files ...
2
votes
1answer
142 views
Oracle ash report
Recently, i have been handed over with an ash report from DBA.
To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
2
votes
2answers
115 views
Is there a difference in performance between @date and getdate()?
Usually I use the getdate() function in my where clauses to go back in time.
Something like:
DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())
Will SQL Server 2008R2 perform faster ...
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
...
2
votes
2answers
111 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
...
2
votes
1answer
193 views
Splitting a column to normalized table
I have a de-normalized table as
Original De-Normalized Table
id text
23 first,second|third,fourth,fifth|sixth
I want to normalize the database to create the following tables
Table 1: (Split by ...
2
votes
1answer
94 views
How to know what to investigate and how for a VERY slow stored procedure
I have inherited a large and slow stored procedure and it's giving me a nightmare:
I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck.
I have ...
2
votes
3answers
339 views
Speed up search across multiple columns
We have a table:
user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.)
Now, we have a user search that search across three columns first_name,last_name and work_place.
...
2
votes
1answer
57 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
1answer
252 views
inner join on PK with extra criteria slow despite indices?
Given the two tables below I am struggling to understand:
why is the third query slow even though first two queries are fast
what exactly is EXPLAIN saying
can I do anything to significantly speed ...
2
votes
1answer
306 views
Should I use left join to do my job in this scenario?
What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field ...
2
votes
1answer
656 views
How to speed up mysql query?
I have query :
SELECT
`school`.`ARCO_name`,
`student-`.`ClassSize_7`,
`student-`.`ClassSize_8`,
`degree_o`.`degree_code`,
`accredit`.`full_faculty_3`,
...
2
votes
1answer
646 views
How to optimise read/write performance a MySQL table with many rows?
I am developing a django application where I use a MySQL database. In my application there is one table for domains information which has only 2 coloumns
id
domain_name
but I have a problem when I ...
2
votes
1answer
216 views
Query tuning - SQL Server
One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was ...
2
votes
1answer
127 views
MySQL optimization question — how to speed up a simple query
I have a very simple database, one table with five columns (one INTEGER key, two VARCHAR, a DATE, and a DOUBLE). The table contains 909,000 records.
A simple query like
SELECT t.price
FROM ...
2
votes
1answer
297 views
How to speed up queries on a large 220 million rows table (9 gig data)?
The issue:
We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in ...
2
votes
1answer
39 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
2
votes
1answer
210 views
Oracle 10g using collection with sql
I have a stored procedure that takes collection of objects. (TABLE OF MyCustomType). Inside the procedure I'm trying to join this parameter with real tables.
For example, something like
create or ...
2
votes
1answer
156 views
oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation
I have a Merge query (open to better suggestions) that seems to cause the server to hang. (I'm not sure what it's doing, but it pegs the CPU and never completes) When I run it through the query ...
2
votes
1answer
378 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; ...
2
votes
1answer
94 views
Get the id_user_app if not exists in id_user_rq
I'm building a application and have this table for users relationships. I need to build some queries like for example:
Who invited me as a friend?
Who I invited to be my friend?
Who I accept as ...
2
votes
1answer
309 views
How to calculate query that access system view cost in Oracle?
For a query that returns if there are any active transactions within current session ,
SELECT COUNT(*)
FROM
v$transaction t
INNER JOIN v$session s ON (t.ses_addr = s.saddr )
INNER ...
2
votes
1answer
180 views
datetime index skipped for same query different params
I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575)
Any idea why it skips ...
2
votes
1answer
125 views
Should I use an index or a hash function on a primary key if I have 100000 rows of data?
I have a table named PRODUCT that has 100,000 rows. I was wondering if either indexing, or a hash function on the primary key would result in better performance. I was also hoping someone could ...
2
votes
1answer
157 views
Rewriting a query
I have a database with the following structure (star schema):
facttable:
pros integer
mena integer
me integer
vahet double precision
slutttp timestamp without time zone
Structure of each ...
2
votes
1answer
102 views
Oracle: How to gather stats in a logical standby database?
I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
2
votes
3answers
191 views
Why is SQL running the same query longer from another connection?
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?
Dont jump to any conclusion just yet, let me explain what I mean...
Here is the setup:
A Windows ...
2
votes
0answers
34 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 ...
2
votes
0answers
158 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 ...
2
votes
0answers
160 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 ...
2
votes
1answer
233 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 ...
2
votes
2answers
73 views
MySQL index on column comparison
How do you go about setting an index to be used for a column comparison like WHERE col1 > col2?
col1 and col2 are of DATETIME type.
2
votes
2answers
117 views
Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?
I have a table that has a large number of rows in it.
The primary key (an auto-incrementing integer) is, by default, indexed.
While waiting for a row count to be returned I did an EXPLAIN in another ...
2
votes
0answers
80 views
How To Analyse Index Scans
I am using merge replication with SQL Server 2012.
During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
2
votes
3answers
499 views
MySQL optimization - year column grouping - using temporary table, filesort
I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
2
votes
0answers
122 views
end user complaining poor performance of a query or application in SQL server [closed]
Any suggestions on what set of questions I should be asking an end user or an application user to drill down to the real issue of his complaining about slow responses to his queries?
For example:
...
2
votes
0answers
238 views
What does duration measurement in sql server actually measure?
What precisely does the query duration measure when conducting a profiler trace?
I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
