A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
1
vote
1answer
53 views
MySQL with slow queries
I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page.
I ...
0
votes
1answer
60 views
How index work in oracle? [duplicate]
I want to understand :
How index work in oracle.
How they are stored in the disk.
How they increase the efficiency of a query?
By third point I means how Index contribute to the efficiency of ...
3
votes
1answer
73 views
Most efficient queries on flags in TSQL: bit field or table join?
Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing ...
0
votes
2answers
109 views
What is the performance difference between IN (42) and id = 42 in MySQL?
Are there any performance differences between
SELECT *
FROM table
WHERE id IN (42);
and
SELECT *
FROM table
WHERE id = 42;
?
The question is about just supplying a single value. The ...
0
votes
1answer
26 views
MySQL: What points should we consider when create composite index
There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key.
Beside this, we create composite index on the ...
0
votes
2answers
48 views
How should I structure my time based index?
So I have this table in SQL Server 2008. Let's just say it's called TimeSheets.
Now, that table has two columns I am concerned with.
TimeSheetDate and UserCustomerKey.
TimeSheetDate is a ...
0
votes
1answer
18 views
MySQL: Different thread states in Query execution
My MySQL 5.1 bases Intranet system is facing very bad performance during high load. When observe through MonYog, I found below thread states:
We can simply understand the different thread state by ...
2
votes
1answer
128 views
Are there advantages to using temporary tables over derived tables in SQL Server?
I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why?
I must do queries with large data (millions records) and I ...
2
votes
2answers
296 views
Query hangs forever at first but works after one day
I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it
First I rename the original tables in schema B
Then, using SQL Developer I copy the table ...
1
vote
1answer
51 views
How to get query runtime in PL/pgSQL to be as fast as console query runtime?
Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ...
0
votes
1answer
108 views
What is the Difference between IN and Not IN performance wise?
I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise.
When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
0
votes
0answers
19 views
Request for Help Optimizing a Query in MySQL
I have a query that is being used to report on the number of times a trigger in Zabbix, was fired by an event. For example, an event (not enough disk space) fires a trigger that sends an email to ...
0
votes
0answers
63 views
Yet another slow query Postgresql 9.3
I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, ...
2
votes
2answers
81 views
Comparing Queries
In SQL Server, if you're comparing the statistics IO from two queries, how do you ensure that caching doesn't become a factor?
For example (I'm making this up as I type, not a real example):
SELECT ...
0
votes
1answer
85 views
T-SQL Optimizing a Join on TOP Value from another table
I've got a data warehouse that goes through a full refresh each night that can take about an hour to process 16 million rows/25gigs of data and we're looking for ways to reduce this time without going ...
0
votes
1answer
50 views
Database is slow once in a while
I am executing a web service for 100 times. The average response time of this service is 1.5 seconds. But when I look at the response time of each execution individually, there are a few executions ...
2
votes
1answer
58 views
Optimize table and query, aggregate by date or date-hour
I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows:
CREATE TABLE sensor_data (
...
2
votes
0answers
40 views
Does Oracle ADO.NET varchar parameter size influence query plan reuse?
I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
0
votes
0answers
24 views
What Index can I apply to a GROUP BY, MIN() and WHERE MySQL Query
I have the following table:
CREATE TABLE `wp_prize_draws_log_copy` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`entry_date` date NOT NULL,
`entry_time` datetime ...
0
votes
1answer
22 views
Does Oracle Partition by Reference optimizes join queries
Oracle 11g has a new feature in partitioning tables called partition by reference.
I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining ...
3
votes
1answer
97 views
How to correctly return a query result only if it isn't NULL?
I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something.
What I'm doing is this:
Run the query
Check whether it returns something.
If it ...
1
vote
0answers
35 views
MySQL aggregation count performance
I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year.
The table structure looks like:
id | year | amount
The ...
0
votes
0answers
27 views
Mysql query getting hung!
I am using mysql 5.5. I create following temporary table:
CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s (
`count` int(10) NOT NULL,
`m_class` enum('SMS','EMAIL') NOT NULL,
...
2
votes
0answers
25 views
Single table row “sharding/versioning” to customize values
Introduction
Hello everyone, I have done quite a bit of searching and I cannot find a solution for my specific issue. There are a few factors that complicate my situation, which I have outlined ...
1
vote
1answer
36 views
Poor query performance in MySQL
I have a 10 million record table in a MySQL 5.5 InnoDB. It's running in a 16GB RAM server with good CPU and fast HD. When I run the following query
SELECT DISTINCT knowledge_id,
...
0
votes
1answer
39 views
Sql Server Full Backup Duration Increased Dramatically
since last week My Full Backup Increased dramatically .before last week the time is about 3 hour and now the time is about 6 hour.in addition some query take long time to run.
(not all the query).I ...
0
votes
1answer
42 views
Slow MYSQL performance due to sub queries
Here is my query:
SELECT BAL,NAME,
(SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR ,
(SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < ...
0
votes
2answers
52 views
A simple database and Query System? [closed]
I am new to databases and trying to solve one problem in which I have to build a simple database having few entries and then querying that database and then recording that answers from the database. I ...
0
votes
1answer
23 views
Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]
I have a poorly performing query:
SELECT user_id, count(item_id) as count
FROM table items
WHERE category = 'magazine'
AND created_at > 1384754400
GROUP BY user_id
ORDER BY count(item_id) desc
...
0
votes
2answers
79 views
MYSQL - Why does this SELECT get slower the further down the table?
My table:
CREATE TABLE `wp_users` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_login` varchar(60) NOT NULL DEFAULT '',
`user_pass` varchar(64) NOT NULL DEFAULT '',
...
2
votes
1answer
27 views
General purpose view for last value in a group
I am trying to create a view to show the last value in the group.
Looking for a more general purpose query.
CREATE TABLE a (
id INTEGER,
seq INTEGER,
val VARCHAR2(16),
PRIMARY ...
2
votes
1answer
48 views
Getting each status change in a table
I have a table that holds status changes per item. The item can go in and out of a status multiple times.
ItemTable (ItemId int, Status varchar(50), DateOfChange date)
I need to pull the date of ...
1
vote
2answers
45 views
Indexing varchar column to search for first letter
I'm trying to optimize a legacy web-app where users can ask for a list of all records whose name starts with a given character.
The query to optimize is:
SELECT oggetto.id_oggetto, titolo, anno, ...
0
votes
1answer
30 views
How can I efficiently search for all records from a given month, or a given year?
We have a couple queries that involve datetime fields. In particular, we are checking for things like
SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1
SELECT field1, field2, ...
1
vote
1answer
84 views
Slow running query with inner SELECT in where clause
I have a query that looks like the following:
USE DATABASE3;
GO
SELECT t1.COL1 AS COLUMN1,
t2.COL2 AS COLUMN2,
t1.COL3,
t1.COL4 AS COLUMN4,
t1.COL5 AS COLUMN5,
...
1
vote
1answer
42 views
How to use LOW_PRIORITY for heavy queries?
I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources.
I do not want them to stop end users from ...
1
vote
1answer
60 views
UPDATE a column by COUNT of another table
It might be a naive question, but what is the difference of these two queries and which is preferred?
UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = ...
0
votes
0answers
55 views
Execution time vary for the same query
I have a query which looks like this:
select *
from ORDERDOC, ORDERPOSITION
where ORDERDOC.id = ORDERPOSITION.orderId --some other conditions;
Execution plan looks like this:
0 SELECT ...
0
votes
4answers
84 views
Can migration from MS Access to SQL Server or … make database faster? [closed]
A company asked me to migrate their database from MS Access to SQL Server or MySQL because they think that on those engines their database will run faster. But I wonder if their problem isn't because ...
0
votes
1answer
68 views
statistics State in MySQL Processlist
I have a busy MySQL Server having query
SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
0
votes
1answer
69 views
Is it possible to optimize this query? Or any recommendations to speed it up?
After looking at my mysql slow log file I see that almost every entry there is related to this query:
SELECT
n.nid AS nid, AVG(v.value) AS rating
FROM
node n
LEFT OUTER JOIN votingapi_vote ...
3
votes
2answers
107 views
Should I add multiple secondary indexes to search
I have a search screen on my app where the users can use up to 4 parameters to search.
I have written a stored procedure to facilitate the search.
Select
ID,
FirstName,
LastName,
...
1
vote
1answer
74 views
Query tuning - performance
Could you please help me tune this query. It takes over half an hour to run! I want to identify repeat bookings (can only be identified using an annoying varchar field - email)
UPDATE #Repeats --a ...
1
vote
1answer
55 views
MySQL: What is the most optimized way to use datetime type field in query
I am working on optimization of some slow queries in my application. I found a query in which datetime comparison has been used. Below is the subquery:
select * from jos_content c where c.visible = 1 ...
1
vote
1answer
120 views
Query State hangs on sending data
For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why.
This is the table creating script:
CREATE TABLE IF NOT EXISTS `esp_game` (
`gameID` ...
1
vote
1answer
53 views
Using OFFSET in a large table by a simple sub-query
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM ...
1
vote
0answers
34 views
join performance, index vs. index+foreignkey
I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine.
I recently read that foreign keys can enhance ...
0
votes
2answers
75 views
MySQL: why assign NULL as default value
When we define a table, multiple times we assign default value of a column as NULL. I have
How NULL is different than empty value('') as well as 0?
Is there any impact of NULL value in query ...
0
votes
0answers
69 views
MySQL: how to determine key_len and its role in performance improvement of a query
I have a query, below is the structure of the table:
CREATE TABLE `jos_tag_rules_fields` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`label` varchar(255) ...
-2
votes
1answer
65 views
Maximum dataset size for a well-performing SQL Server 2012? [closed]
We're planning a project using SQL Server 2012 for storing about a billion records over less than 50 fields. Assuming good hardware, is SQL Server up to that task? Roughly how many request/second can ...
