A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
1
vote
2answers
140 views
Can a poor database layout cause performance issues for simple queries?
I have a database layout that is realy a mess and needs to be optimized. Does the layout of the database and some queries that are executed cause other queries to be slow too?
For example, an insert ...
2
votes
1answer
597 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 ...
7
votes
3answers
457 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
6
votes
1answer
180 views
Performance of query with a range condition and order by
We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:
lexikon
-------
_id integer PRIMARY KEY
word text
frequency integer
...
6
votes
1answer
146 views
Is it recommended to clear the query plan cache
Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation.
After making the changes to the code, ...
0
votes
0answers
123 views
Is like '%text%' faster than like ('%text' or 'text%') [closed]
A lot of programming language when you use an OR, he will check the first condition to be true and won't check the rest of the condition.
for example
true OR (false) OR (1) OR (false) OR ....... OR ...
1
vote
2answers
143 views
Does this cause overhead ? Select name from User where user_name LIKE '%alex%'
I'm wondering if this statement can cause real overhead if many concurrent users
send the same request to mySQL InnoDB storage engine. Let's say I have a table that
has the user_name, and someone want ...
2
votes
1answer
185 views
Why is the following query so bad performance-wise
After asking this question, I got to thinking about why a certain query was doing all the problems.
In short, there was a query which took 500ms, I ran some execution plans and applied the ...
2
votes
3answers
135 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 ...
0
votes
1answer
119 views
Normalization and Breaking down tables for InnoDB
I'm designing a web application, something small but deals with lots of read and inserts.
Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table
called User which has ...
0
votes
3answers
1k views
Will more indexes on a table affect performance? [duplicate]
Possible Duplicate:
How do indices impact query performance?
How to know when/if I have too many indexes?
Say ‘X’ report using some "where" condition in the statement so we are creating ...
6
votes
3answers
786 views
Is it better to populate variables using SET or SELECT?
What is the better way (with regards to performance) to set a value to variable?
By SET command:
DECLARE @VarString nvarchar(max);
SET @VarString = 'john doe';
SELECT @VarString;
By SELECT ...
7
votes
3answers
1k views
TSQL performance - JOIN on value BETWEEN min and max
I have two tables in which I store:
an IP range - country lookup table
a list of requests coming from different IPs
The IPs were stored as bigints to improve lookup performance.
This is the table ...
1
vote
2answers
109 views
Why are my results returned twice?
I've this query:
SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id,
`default_wall_posts_comments`.`comments` as comments,
UNIX_TIMESTAMP() - default_wall_posts_comments.date_created ...
1
vote
4answers
341 views
Performance degradation while updating tables having 10s of millions of records
I want to update tables ( my be 20-30 ) having 10s of millions of records each.
The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
2
votes
1answer
987 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 ...
1
vote
1answer
451 views
Index Strategies on Text or NVARCHAR(MAX) Fields
I have the following query (simplified for question) I'm trying to speed up for a read only DB ...
SELECT
[sysid]
,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)
,[From]=[from_addr] ...
1
vote
1answer
151 views
MySql - How can I improve this query?
I have the following table: http://pastebin.com/XH6FvhZ8
and the following query:
SELECT *
FROM (SELECT *
FROM `job_logs`
ORDER BY id DESC) AS job_logs_tmp
WHERE (job_logs_tmp.user_id = ...
0
votes
1answer
95 views
Creating Indexes to optimize query
I have this query and I want to increase its performance by adding appropriate Indexes.
DELETE
FROM MYTAB1
WHERE MYID1 IN
( SELECT MYID2 FROM MYTAB2 );
I am not familiar with the ...
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 ...
3
votes
2answers
137 views
How do I count rows with two properties in one index scan in SQL?
I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it.
I need to output the following: for each day I need to ...
6
votes
3answers
413 views
Single query sending and retreiving duplicate data on MS SQL Server?
Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
3
votes
2answers
246 views
Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?
In this query:
select count(*) from largetable;
a secondary index is chosen:
mysql> explain select count(*) from largetable;
...
2
votes
2answers
198 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 ...
1
vote
3answers
1k views
Is it normal to use many Triggers?
I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
3
votes
1answer
213 views
Measuring the relative performance of queries given the assertion that “cost” isn't reliable
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.
Source
Does ...
1
vote
3answers
193 views
Checking if the query is made async or sync on SQL Server 2008 R2
I have tried to make async calls to my SQL Server instance as you may see on my SO post:
ASP.NET MVC 4 asyncronous database call: never returns the HTTP response
I did a benchmarking and the sync ...
6
votes
3answers
246 views
How can I identify when to create a new table to hold data that can be obtained from a query?
We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when ...
3
votes
1answer
2k views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
4
votes
1answer
218 views
Does the order of columns has a significant effect on INSERT?
Do we experience a noticeable difference in performance for these two queries?
INSERT INTO table (col1, col2, col3, col4, col5) ...
and
INSERT INTO table (col5, col3, col1, col2, col4) ...
Do we ...
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 ...
4
votes
2answers
563 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
...
10
votes
3answers
1k views
Are Views optimized when I add a WHERE clause to them?
Does it make a difference if you filter a View inside or outside the View?
For example, is there any difference between these two queries?
SELECT Id
FROM MyTable
WHERE SomeColumn = 1
Or
SELECT Id
...
3
votes
2answers
1k views
Copy huge amount from 1 table to other
I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail.
So I ...
9
votes
3answers
2k views
Why is an aggregate query significantly faster with a GROUP BY clause than without one?
I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.
For example, this query takes almost 10 seconds to run
SELECT MIN(CreatedDate)
FROM MyTable
WHERE ...
8
votes
1answer
665 views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
4
votes
1answer
2k views
Why does a query run slower in a Stored Procedure than in the Query window?
I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?
Here's what my query looks ...
2
votes
3answers
331 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
4answers
505 views
Remove sub-string from WHERE clause
I have a stored procedure that is using the SUBSTRING function in the WHERE clause.
SELECT DISTINCT
ColumnBB,
ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;
...
4
votes
3answers
236 views
Which is the most efficient way to run a particular select query?
If I run the following code:
select PolicyNumber, MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber, Risk
I get the following ...
5
votes
4answers
980 views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
2
votes
1answer
135 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 ...
1
vote
1answer
174 views
Oracle Benchmarking (SQL_NO_CACHE …)
I want to benchmark some queries on an oracle System from a JDBC application.
However, the database seems to cache at some point in time. However, this is not intended in this case.
Is there ...
3
votes
4answers
329 views
Help with Query. Finding records that have the same relationships (MySQL)
I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this:
listings
----------------
id (int)
name (varchar)
listings_taxons
...
3
votes
2answers
351 views
Separate Address Table, Latitude, Longitude
We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each ...
0
votes
2answers
104 views
Attempting to avoid SORT in query planner
I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically):
...
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 ...
3
votes
4answers
568 views
Optimize Query with Derived Table
I have the following query
select
ps.id, ps.title
, IFNULL(s.likes,0) as num_likes
, IFNULL(s.comments,0) as num_comments
, IFNULL(s.ratings,0) as num_ratings
, IFNULL(s.views,0) as num_views
, ...
0
votes
1answer
123 views
Help me to better design my query so I can gain more performance
I need to find so called first entry in database for items regarding their partners.
I wrote query and it is giving me correct results but it sucks with performances.
I suspect that I have missed ...
2
votes
1answer
126 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 ...
