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

0
votes
2answers
767 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
1answer
146 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
79 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
1answer
99 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
402 views

Simple PostgreSQL lookup table is inexplicably slow

I'm trying PostgreSQL 8.4.14 for storing triples, pieces of data of the form (String, String, String). For speed, I'm not repeatedly storing strings but rather using two tables: main table triples ...
0
votes
1answer
130 views

Using an enum index to limit records

A common issue, just not sure on the keywords. I have a table of assessments (14k+ records). Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done'). Most ...
0
votes
1answer
362 views

Efficient way to write query for multiple table which depends on column value of a table in mysql?

In the given schema, there is a table A having column A1,A2,A3,A4 There are three more tables i.e. B,C,D If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup ...
0
votes
1answer
261 views

T-SQL Gurus, I need help optimising these queries for performance

I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids In a production environment the table ...
0
votes
1answer
49 views

Query Optimization for added value

I have a query that's behaving a bit oddly. In my database I have a table called "records". It tells me a bunch of information about what applications a user ran on my company's machines. I'm ...
0
votes
1answer
56 views

strange “using where” in simple query

I have a table with this structure: mysql> describe user_groups; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
0
votes
1answer
30 views

Condense Query Results with Wildcard

I have the following query: select programVersion, count(programVersion) from records R inner join Programs P on P.programID=R.usageProgramID where programName="word" ...
0
votes
2answers
1k views

How to use and optimize subquery on 100 million rows

What I'm trying to do is running a job on more than 100 million domains which haven't processed before. I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
0
votes
1answer
53 views

Optimizing Query

Can anyone help me tuning this query..... explain extended SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a LEFT OUTER JOIN (SELECT msisdn FROM as_treat_pre_usage_30days GROUP BY ...
0
votes
1answer
128 views

What is the correct model for related tables with millions of rows?

I need to create a question and answer tables that will have millions (maybe billions) of rows. The current model is: Question Table id_question (PK, auto increment) id_user question_content ...
0
votes
1answer
491 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
0
votes
1answer
30 views

Simple query not using indexes

My query is not using indexes. It is a simple query. Can someone explain why that's the case? The query: select TIME_FORMAT(start_time,'%H:%i') start_time, TIME_FORMAT(end_time,'%H:%i') ...
0
votes
1answer
100 views

speeding up a query on MySql [closed]

I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query. create table t2 select field_1, sum(ifnull(field_2,0)) ...
0
votes
1answer
92 views

Speed efficient query for membership first joined, latest category from membership table (min, max)

I have the following table representing membership information: CREATE TABLE IF NOT EXISTS `membership` ( `id` int(11) NOT NULL AUTO_INCREMENT, `organisation_id` int(11) NOT NULL, ...
0
votes
1answer
481 views

slow queries - set weight to token type in tsquery - postgresql

Postgresql version 9.2.3! I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm ...
0
votes
1answer
72 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 ...
0
votes
1answer
116 views

Query taking long time

My query is taking around 540 seconds to get result. Please help me to rewrite it. Query: SELECT pd.domain, fd.passkeyid FROM portfolio.domains AS pd, fabulousdomains.domains AS fd WHERE ...
0
votes
1answer
287 views

MySQL Partition based on month-year

Is there a way that I can create a month-year partition on the below table? Without having to define all the permuations of month/year combos ? CREATE TABLE visitor_visits ( id int(10) UNSIGNED ...
0
votes
1answer
2k views

Is there an easy way to debug and find out why a query runs slow?

I'm running onto a weird issue. We've got two db servers, one is development and the other one is production. Both have same specs. VMs (Hyper-V) Windows Server 2008 R2 64 Bit SQL Server 2008 R2 64 ...
0
votes
2answers
271 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): ...
0
votes
1answer
135 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 ...
0
votes
1answer
365 views

Returning Multiple Result-Sets or Single XML Blods

I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with ...
0
votes
0answers
6 views

Convert date in date list condition to list of date ranges

I want to look for all the records that occur on specific dates. SELECT * FROM table1 WHERE date(column) in ($date1, $date2, ...); But as many of you know this kind of comparisons doesn't get ...
0
votes
0answers
5 views

Create table with select Query on DB link

I'm trying to execute the following query, it is taking more than usual time. I'm retrieving data from db link. without the create table statement the query is giving results. for Create table also if ...
0
votes
0answers
52 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
0
votes
1answer
18 views

MySQL 2 Million Rows, longtext and varchar indexes

I'm trying to find a solution for this, with no success. I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR. name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE ...
0
votes
1answer
30 views

Optimizing a query on Mysql 5.5.24

I am trying to optimize the query SELECT CONCAT(lastname, ', ', title, ' ', firstname) AS fullName, e.lastname, e.firstname, e.email, e.userType, e.userID FROM employee e, ...
0
votes
0answers
41 views

Which of sys.dm_exec_query_stats columns should I pay most attention?

I'm trying to tune my database and currently staring into sys.dm_exec_query_stats (with actual queries): SELECT SqlText.text, * from sys.dm_exec_query_stats CROSS APPLY ...
0
votes
0answers
7 views

indexing table column on the go of mysql query commit

It is said that to optimize the query cost of execution, then it would be by indexing the field of tables in database. Since I don't have a permission to enter the phpmyadmin in my web server as I am ...
0
votes
0answers
20 views

Best way to run a formula (segment) a large number of users with custom attributes

Here is my question. I have a table that stores a large number of people (80 mil). Each person has a GUID id. Then I have another table (or set of tables) that stores information about people. e.g. ...
0
votes
1answer
74 views

Improve Query Performance

I have the following sqlite3-Tables: db.execute("""CREATE TABLE IF NOT EXISTS STATIONS (StationUID INTEGER PRIMARY KEY AUTOINCREMENT, StationNumber TEXT NOT NULL, ...
0
votes
0answers
48 views

Slow running Oracle query caused by unnecessary full table scan

I'm by no means an expert with Oracle, but I know enough in terms of how to analyze explain plans and perform basic optimizations. However, this time, Oracle has stumped me with it's explain plan. ...
0
votes
1answer
32 views

InnoDB Table has backed up INSERT and UPDATE queries, high CPU

I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST; Basically, I keep a table of every logged in customer and update ...
0
votes
0answers
50 views

Optimising an SP - Left Join against several big tables

I have stumbled upon performance bottleneck due to LEFT JOIN against several big tables (approx. 6 million rows of data, consisting of 10 columns). Any suggestions on how to optimize this SP is ...
0
votes
0answers
31 views

How to divide a big SQL Query into small query

I am trying to traverse the whole file system. I am picking each and every file and then trying to insert it into the SQLite Database. Before inserting I am also calculating the checksum of each file. ...
0
votes
0answers
30 views

Mysql cursor alternative / Optimization - each row updates are too slow

I'm looking to optimize my cursor based update or actually replacing it... Situation We're carrying out promotional campaigns and I'd like to track user activity per campaign. Logic Each campaign ...
0
votes
0answers
32 views

delete query performance issue on mysql

I have two tables table1, table2 where table2 is a subset of table1 (basically created with the query create table table2 as select * from table1 where .....) Now, I am trying to run the below query. ...
0
votes
1answer
20 views

Optimize Query in MySQL

I've following tables with respective fields 1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data 2) wi_individual_p(ind_id,prg_id,...) Around 250K data 3) ...
0
votes
0answers
19 views

DB space vs query complexity tradeoff

I have a dilemma - Right now I have a query that I do constantly - joining 5 tables on different columns, calculate stuff per row, distinct on that, and do that with offset and limit (pagination). I ...
0
votes
0answers
32 views

trying to a lag a column in SQLite

Sorry if this is too basic for this forum, but I have had no luck in StackOverflow. I am a newbie to SQL. I am using SQLite. I have a table where rows represent individuals in a point in time. I am ...
0
votes
1answer
23 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
0
votes
0answers
42 views

Auto update stats turned off for a user database on SQL Server

While trying to improve the performance of few queries, we found via execution plan that there were lot of index / clustered index seeks. Therefore: First thing we did, was to check our Re-indexing ...
0
votes
0answers
39 views

SQL query running slow compared to its earlier execution time

One of our application using an SQL query is reported running slow. Current execution time:- 15 minutes and so Earlier execution time:- 30 seconds. SQL-Sentry Plan for the slow query I've ...
0
votes
1answer
41 views

set global variable parameter?

My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time. I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need ...
0
votes
1answer
38 views

Query results that not are in previous query result

I want to retrieve from my table the id_product of the products which haven't got some attribute and it's value is not the ones i want For example i want the id_product of the products in which ...
0
votes
0answers
22 views

Sampling and matching samples with spatial conditions

The Problem I have two tables with start points of a route and possible end points of a route which need to be matched against each other. I also have a given distribution for the distance between ...