A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

3
votes
2answers
202 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
78 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
164 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
1k 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
517 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
54 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
413 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
135 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
101 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 ...
0
votes
0answers
65 views

MySQL, query optimization with join on huge table

I'm trying to execute a query on MySQL (Database created from a GTFS schema) I have two tables like these: trips trip_id | route_id --------------------- 1 | 409 2 | 409 3 ...
9
votes
2answers
2k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
0
votes
1answer
92 views

MySQL: fetch multiple global status and system variable in a single query

When we execute SHOW global status; or SHOW variables; we get list of 291 and 278 records in the resultset. For performance perspective, only few of them are much important. I have to fetch these ...
4
votes
1answer
674 views

Optimising a query against sys.fn_dblog

Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc.. Here ...
3
votes
2answers
170 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
0
votes
0answers
128 views

My SQLs are taking too long to execute for first time everyday in Oracle 11g [duplicate]

whenever I make a new request in my application it takes to long to respond for the first time and all the subsequent requests are faster. If I do it again after some hours or tomorrow again it takes ...
0
votes
0answers
148 views

MySQl: slow query is not using index in joining

Below is the configuration of my DB server: Apache version : Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8 PHP version : 5.3.13 MySQL version : mysqlnd 5.1.11 Windows Server 2008 Standard ...
2
votes
1answer
250 views

T-SQL Query runs quickly on local PC, but slow on SQL Server?

I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters. It's been running without issue for months - but has suddenly started running very slowly on the server ...
0
votes
0answers
74 views

Query with large number of or'ed predicates on the same column?

At one place we need to query a table to determine whether any records exists that are in a subset of the same table. The columns that are queried are varchar columns containing file-paths, and we ...
0
votes
1answer
81 views

Sequences Vs Count for auto-increment

Can some one please tell me the exact difference between the following statements , INSERT INTO table1 VALUES ((select count(column-name) from table-name2)+1); here i am using the count function to ...
0
votes
1answer
68 views

Query a name in large database, optimizations? [closed]

if you would like to query a name in a very large database, are there some ways to optimize this query? If 1 million names are in the database, and the name has to stay as it was registered (and not ...
0
votes
0answers
97 views

Postgres chooses a bad query plan

The following query finishes in 1.5s (which is ok, the table contains about 500M rows): explain (analyze, buffers) select sales.* from sales join product on (product.id = sales.productid) join date ...
0
votes
1answer
262 views

Insert but ignore if duplicate AND set a value different if exist in another table

I'm trying my make an invitation system together with the Facebook PHP APK. What I basically want is when you have invited your friends, Facebook redirects back to your website with an array ...
0
votes
2answers
286 views

How to get rid of using temporary when using DISTINCT in MySQL?

When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary? http://sqlfiddle.com/#!8/f61a2/3/0 CREATE TABLE `test` ( `a` varchar(45) NOT NULL, ...
1
vote
1answer
164 views

Identical query, tables, but different EXPLAIN and performance

I have two tables levels and users_favorites +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
79 views

Index being ignored by Oracle

I have a table for sensor data, simplified it looks like this: DEVICE_ID NUMBER SENSOR_ID NUMBER DATA_TIME NUMBER DATA_VALUE1 NUMBER The table constains billions of rows and is partitioned by week ...
0
votes
1answer
66 views

Is it any good for performance to have master and slave on the same server

There are similar questions on this site, but they mostly cover server failure and data backup aspects. I'm concerned only as far as query performance. If master and slave share the same resources, ...
1
vote
1answer
460 views

SQL Server performance for tables having large number of columns [closed]

I have a requirement where I need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int, varchar, ...
1
vote
1answer
143 views

Precautions when designing database schema if tables are going to have billons of rows

I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
1
vote
1answer
611 views

Large INSERT INTO SELECT [..] FROM gradually gets slower

I've written a program that does a large INSERT in batches of 100,000 and shows its progress. The source table contains 2.5GB of data: CREATE TABLE wikt.text ( old_id INT(10) UNSIGNED NOT NULL ...
15
votes
3answers
813 views

difference in execution plans on UAT and PROD server

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs). Both UAT and PROD are having exactly data and indexes. ...
3
votes
1answer
1k views

Comparing two queries in SQL Server 2012

I am comparing two queries in SQL Server 2012. The goal is to use all of the pertinent information available from the query optimizer when choosing the best query. Both queries produce the same ...
0
votes
2answers
92 views

Can I improve this query?

SELECT * FROM SameLogTable WHERE ID_Table IN (SELECT ID_Table-1 FROM SameLogTable WHERE <SameCondition>) OR ID_Table IN (SELECT ID_Table ...
0
votes
1answer
1k views

Different execution plan for the same query if I change a value in the predicate

I am getting two different execution plans for the same query only by changing the value in the predicate, the first one uses nested loops and the other one hash join which takes a lot longer. ...
0
votes
0answers
174 views

Alternatives for UPDATE statement Oracle 11g

I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less) Table1 ID varchar(64) Status int(1) Transaction_date date tons of other columns And this ...
0
votes
0answers
727 views

Database design to track inventory?

Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general? mysql structure ...
1
vote
2answers
139 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
0
votes
0answers
82 views

My transaction's max time is going beyond a minute

I have a transaction called "updatePolicyStatus" whose response time is usually under a 3 seconds for many instances during a peak load. This transaction processes bunch of business rules and fires ...
1
vote
1answer
1k views

Some queries are very slow after index rebuild

After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80. After rebuilding all indexes, some ...
1
vote
1answer
76 views

How is the number of records on a select determined as high or low by optimizer?

What is the threshold between using the index and doing an direct access to the database? I assume this decision is related to the number of index access (and subsequent access to the tables). If this ...
2
votes
2answers
156 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
0
votes
0answers
80 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
1
vote
2answers
913 views

INSERT INTO with SELECT subquery db2 9.5

I have the following query that takes 4 seconds to execute: SELECT TX.* FROM TABLE_X TX INNER JOIN TABLE_Y TY ON TY.VALUE_ID = VALUE_ID_IN AND TY.PROD_ID = TX.PROD_ID WHERE (TX.PLACE_ID1 ...
1
vote
1answer
235 views

“copy to tmp table” for 24 hours, then timeout; when ALTERing to add PK & AI

I downloaded the Wiktionary XML dump, and ran mwdumper.jar to insert the data into MySQL. Following the advice in the manual, I commented out the PRIMARY KEY AUTO_INCREMENT part of the CREATE TABLE ...
1
vote
4answers
213 views

Which of these queries is best for performance?

Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i ...
1
vote
0answers
72 views

MySQL: What criteria should I base my table column order/arrangement/grouping on?

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...
0
votes
2answers
258 views

Why would a long running query remain sleeping and with null wait_info

The following query was run on SQL Server 2008 R2 and took over 1 hour to run: select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id It produced 0 rows ...
0
votes
1answer
354 views

Calculating time spent on query cache lock

How can I calculate the total time my MySQL database is spending Waiting for query cache lock ? I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the ...
4
votes
2answers
1k views

GROUP BY with MAX versus just MAX

I am a programmer, dealing with a big table which the following scheme: UpdateTime, PK, datetime, notnull Name, PK, char(14), notnull TheData, float There is a clustered index on Name, UpdateTime ...
0
votes
0answers
54 views

Compare elapsed time for 2 oracle Sql files. (2300 pairs of sql files)

Version ORA 11G I have some timing comparisons that I want to do on about 2300 Pairs of Oracle SQL statements. I'm trying to convince management that tuning of these 2300 would give them a ...
0
votes
1answer
382 views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...