Tagged Questions
2
votes
1answer
87 views
Postgres: count(*) vs count(id)
I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).
My question is ...
0
votes
2answers
65 views
Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?
Imagine you have inherited some project with extensive usage of SQL queries.
Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point.
A question is: could ...
0
votes
2answers
151 views
optimize big sql query
My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
0
votes
1answer
40 views
What index to add in case of join by two optional fields
I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.
...
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 ...
4
votes
1answer
191 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
4
votes
1answer
84 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
4
votes
1answer
95 views
How to get the information of the optimizer of sql server?
The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql ...
2
votes
0answers
118 views
Best book on query optimization, rewriting, refactoring [closed]
I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient.
But in a more general sense, I need a book that explains ...
0
votes
1answer
123 views
Optimising MySQL query with lots of self-joins
I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved.
In some cases it can be said that one entity is a constituent of a ...
1
vote
2answers
134 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
0
votes
0answers
39 views
mysql select query optimization [duplicate]
Possible Duplicate:
mysql select query optimization
I have two tables, testa and testb.
CREATE TABLE `testa` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
...
2
votes
2answers
75 views
How to optimize a log process in MySQL?
In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns ...
4
votes
1answer
128 views
250 billion rows - computing interconnectedness on a really huge scale
I have a table of approximately 700k rows, each identified by a unique itemnumber.
Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
1
vote
2answers
259 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
0
votes
1answer
102 views
MySQL query optimization
I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be ...
4
votes
1answer
149 views
Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
2
votes
1answer
40 views
Convert Log table days in a state
This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following...
Input Table
Date Item Moved to Box
Oct-1 ...
1
vote
1answer
113 views
query optimization delete nested query [closed]
Can the following query be optimized ?
DELETE FROM COLOR_TAGS
WHERE COLOR_PROJECTID = $id
AND NoteID IN(
SELECT ID FROM note_note
WHERE BibID=$bid AND isdel=0);
9
votes
4answers
772 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
3
votes
3answers
193 views
optimizing a compare query
Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
4
votes
2answers
120 views
Optimizing my update sequence
Whenever a user updates his profile, we conduct a set of queries like so:
DELETE FROM `user_likes` WHERE `ID` = $id
INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name)
Is ...
4
votes
1answer
297 views
MongoDB optimization on high data
I have a mongoDB collection with 42M registers with 1M new registers every day.
We found a low performance on mongoDB querying.
showing in mongostat the fields: qr|qw ar|aw with values of 300-500.
...
1
vote
0answers
501 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
4answers
381 views
This query is choking the server. How can I improve it?
Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof!
The MySQL Processes list shows that as the number of requests ...
3
votes
1answer
255 views
Understanding optimisation of complex SELECT queries
I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table.
I want records that match ((criterion 1 or criterion ...
0
votes
3answers
171 views
Dog slow query, needs optimizing, please help
I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is ...
2
votes
2answers
77 views
Performing a “Spread Negitive” over a dataset
I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
4
votes
1answer
169 views
MySQL: A better way to write this query?
We are using the following query in our system:
SELECT f.type AS type,
COUNT(i.id) AS submissions,
SUM((CASE (e.oiPost + e.oiPhone + e.oiEmail)
WHEN 0 THEN 0
WHEN ...
2
votes
2answers
480 views
Trying to optimize query for performance (MySQL)
So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
1
vote
0answers
54 views
Link-table(s) vs Redundant Columns? [duplicate]
Possible Duplicate:
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
I have two tables, Users and People, both of which share a common ...
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 ...
3
votes
3answers
598 views
How to restructure this slow query containing subquery?
This query takes about 2-6 seconds to run depending which tag id(s) is/are selected:
SELECT t2.id, t2.name, count(*) as kpl FROM
(SELECT pt1.post_id
FROM posts_tags pt1
WHERE pt1.tag_id ...
3
votes
2answers
602 views
How can I optimize performance on a MySQL ndbcluster?
So, this is what I have:
A simple table classes__to_students:
|class |student|
---------------
Math Alice
Math Bob
Math Peter
Math Anne
Music Bob
Music Chis
Music ...
9
votes
2answers
223 views
How many Sproc Parameters is too many?
I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
4
votes
1answer
782 views
How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:
FOR COLUMNS SIZE 5 some_column
and
FOR COLUMNS some_column SIZE 5
both will create a ...
3
votes
2answers
1k views
How to optimize this dynamic stored procedure
I have a stored procedure which is retrieving data using 20 tables.
Sample of the procedure:
CREATE PROCEDURE GetEnquiries
(
@EnquiryDate DATETIME = NULL
)
AS
...
10
votes
3answers
2k views
Does “WHERE 1=1” usually have an impact on query performance?
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).
...
1
vote
1answer
1k views
Help optimizing MySQL slow query
i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort"
query:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
8
votes
2answers
493 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
1
vote
1answer
129 views
Optimising a sql correlated subquery statement
I am updating a column on tableA with data from tableB like this:
update tableA
set bKey = select bKey from tableB B where B.colB = tableA.colB
bKey has a unique index on tableB.
Which is the ...
3
votes
3answers
504 views
Improving slow queries. Slow query log attached
Below is the Slow Query Log when my .NET application was running. Please let me know how to improve the performance of the queries:
TCP Port: 3306, Named Pipe: (null)
Time Id Command ...
3
votes
3answers
853 views
MySQL Query Optimization : Indexing and Pagination
I'm adding pagination to my 'latest news' php script and running in to an issue.
SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit
I ...
1
vote
3answers
164 views
Query optimization
In another question they told me that the speed of a DBMS largely depends on the optimizations of your queries. Does anybody know good documents about this? At school I only learned the basics.
For ...
3
votes
2answers
255 views
How do I calculate working business minutes?
I'm working on business inteligence reports and one of my task is to create reports for time tracking.
I have to report on business minutes (10 hours per day) within business week (monday to ...
4
votes
1answer
533 views
Speeding SQL queries for a large(?) WordPress database?
I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here).
The plugin is great ...
8
votes
4answers
919 views
How can I optimize this MySQL query further?
I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
4
votes
4answers
255 views
Do cross-joins get optimised by the client library?
Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ.
I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
0
votes
2answers
505 views
How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?
SQL Server DELETE only marks as DELETED though leaves as data in the data pages.
Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record ...
9
votes
5answers
1k views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
