The tag refers to how to make SQL queries run more efficiently. Some of these questions may belong on DBA.stackexchange.com, especially if they involve reindexing, query plans, etc. SQL Queries can take a long time to run if written badly, or on huge amount of data. Performance can be crucial ...
-1
votes
1answer
19 views
postgresql query optimization with btree on multiple columns
I have a large table with a btree index on multiple keys. If a make a query by fixing the first two columns of the index and putting a unilateral bound on the third column, it results in a very very ...
0
votes
0answers
17 views
Tips for mysql tuning
My mysql server was running too slow. After doing preliminary investigation using top command I found that MySQL was using 200% of cpu which led me to following answer .
After running SHOW FULL ...
3
votes
0answers
33 views
How to design indexes for columns with NULL values in MySQL? [migrated]
I have a database with 40 million entries and want to run queries with the following WHERE clause
...
WHERE
`POP1` IS NOT NULL
&& `VT`='ABC'
&& (`SOURCE`='HOME')
&& ...
1
vote
1answer
17 views
Why cardinality value in mysql indexes don't equal distinct count for column values
Not a long ago I started to optimize queries for mysql db. I created some indexes and decided to see their params, using show index from syntax. And saw that cardinality doesn't equal distinct count ...
1
vote
3answers
43 views
What kind of indexes will help me in this MERGE query?
I have a table like this:
Name | TimeA | TimeB | ValueA | ValueB
And, I am performing some MERGE operations as follows:
CREATE TABLE #TEMP1...
INSERT INTO #TEMP1
SELECT Name, Value
FROM ...
1
vote
1answer
37 views
Need Help to Optimize Query
Can someone please help me optimize following query? Its to search for a search term and result data which will be used in an auto complete form. Data will be sent as JSON (probably) or HTML (). But ...
0
votes
0answers
48 views
How to optimize CTE in SQL server
Below is my recursive query to get children and child of each children but it takes too long to execute. How can I optimize this query so that it can execute faster. If I remove distinct, it brings ...
1
vote
1answer
38 views
Does Database Indexing play any important role in Parent Child table?
Look at this 1st scenario, you got a table that has 2 columns- Parent (P) & Child (C).
P-C
1-3
2-8
3-6
6-4
8-7
When users search for all descendants of "1" then it will show:
P-C
1-3
3-6
6-4
...
1
vote
0answers
38 views
Why does SQL ignore an index hint and opt for a different index?
Given a table that has two indexes on it, one sorted in the reverse from the other and given these two queries.
Select value From SomeTable wITH (INDEX(IV_Sort_Asc))
Select value From SomeTable wITH ...
0
votes
1answer
25 views
How do you modify an SQL query to iteratate over every 100 results and then perform an outter query?
The following query:
DELETE FROM attachmentdata
WHERE attachmentid IN
( SELECT attachmentid
FROM attachments
WHERE pageid IN
( SELECT contentid FROM content_delete )
);
works ...
0
votes
2answers
36 views
Optimizing mysql Join query with date sorting
Simplifying the database/table structure i have a situation with two tables where we store 'items' and item properties (the relation between the two is 1-N)
I'm trying to optimize the following ...
3
votes
5answers
71 views
best practice to count record in MySQL + PHP [duplicate]
I want to check weather number of record > 15 for a condition.
What is best practice to speed up the query?
1)
$query="SELECT `id` FROM `table` WHERE `name`='$name' AND `usage` > '$limit'";
...
0
votes
1answer
25 views
use of Index over function SUM in DB2
I had a Query as Below,
SELECT SUM(SOLD_QTY_CNT) AS QUANTITY, DENOM_AM, SUM(LIN_AM) AS LINE_AMT,
LIN_AM AS O_LINE_AMT, ORD_DTL_ID
FROM MyTable GROUP BY ORD_DTL_ID, LIN_AM, DENOM_AM
...
0
votes
2answers
45 views
How to improve the query so as to reduce the time taken?
Consider i have 15 categories and each category has 6 sub-categories and now i have items table where i have to find 3 items from each sub-category based on the latest purchased date.
category 1 ...
4
votes
1answer
72 views
top-N query doing too much work in spite of STOPKEY optimization
This is going to be long, so here's a quick summary to draw you in: my
top-N query with COUNT STOPKEY and ORDER BY STOPKEY in its plan is
still slow for no good reason.
Now, the details. It starts ...
