Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

1
vote
1answer
116 views

What are the side-effects of disabling nested loops in postgres?

A query of multiple joins spends a lot of time on nested loops. I disabled the nested loops and the query ran much faster. However, I have been told that nested loops might give me wrong results by ...
1
vote
1answer
48 views

stored procedures location influence their performance?

I have a procedure SP_MYPRO that updates data in MYDB1. 1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1? 2) what if the procedure SP_MYPRO only read data, ...
1
vote
1answer
287 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
1
vote
2answers
121 views

MySQL query performance question

I have a MySQL database where I would like to return results based on multiple rows. id | key | value | selector --------------------------------------------------------- 1 | username ...
1
vote
1answer
4k views

Have Postgresql query planner use nested loop w/ indices over hash join

I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
1
vote
2answers
252 views

Why does “SELECT *” take longer than “SELECT <primary_key>”?

I'm having a problem with a query running on a little PHP program. When I use * to select all the columns, it takes way more to return the result than when I use just one column. The query that is ...
1
vote
3answers
6k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
1
vote
2answers
201 views

Oracle result cache behaviour

Oracle by default caching the query results and function results(Buffer cache). I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next ...
1
vote
2answers
3k 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 | ...
1
vote
1answer
411 views

MySQL looking up more rows than needed (indexing issue)

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows: +----------------+-------------+-----------------+--------+ | participant_id | question_id | given_answer_id | status | ...
1
vote
2answers
258 views

Need some support on MySQL Query

I have a query SELECT leads.id, sfav.id is_favorite FROM leads INNER JOIN teams tst ON tst.team_set_id = leads.team_set_id INNER JOIN members members ON tst.team_id = ...
1
vote
2answers
1k views

Mysql. Block/disconnect slow queries

I am experiencing issue. Someone 'attacked' my server: simply by searching the same phrase with multiple requests. As it is text search request and database indices are not used, the engine searches ...
1
vote
1answer
920 views

Query is not using indexes on third table in left join

My query is not using indexes on third table(pci promotion_coupon_images) . i was tried with using index and force index in query but there is no change in result. any body please suggest me how ...
1
vote
1answer
50 views

simple query is not using indexes

My query is not using indexes. it was taking around 5 seconds. output is 500 rows only. it is a simple query Please find the explain plan and table structure here. Explain Plan mysql> explain ...
1
vote
1answer
94 views

Dealing with large log files

I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers. The two largest tables are of ~50 million and ...
1
vote
1answer
205 views

EXISTS and sub-query cost

i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns? 1) SELECT id FROM Parent p ...
1
vote
1answer
444 views

SQL Server 2012 Performance Test

I've been given a task to test where an SQL Server 2012 can achieve better performances - we have two options: Use the SQL Server as an On-Premise machine (Standalone server that we have) Use the ...
1
vote
2answers
181 views

Can a poor database layout cause performance issues for simple queries?

I have a database layout that is realy a mess and needs to be optimized. Does the layout of the database and some queries that are executed cause other queries to be slow too? For example, an insert ...
1
vote
2answers
1k views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
1
vote
1answer
74 views

Why using where in indexed field is slow?

we have a big table. the table structure is : CREATE TABLE `visit_logs` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, `ref_id` int(10) NOT NULL DEFAULT '0', `link_id` ...
1
vote
2answers
81 views

How can I improve performance on a 3 table query?

I'm attempting to analyze a resource drawdown (the percentage of time individual units are being utilized during a reporting period, e.g. 2-weeks or 1-year). I need the aggregation down to the ...
1
vote
1answer
48 views

Extra fields in user_role / roles tables or a new table

I have a user_roles table which I use to manage permissions and user's access to certain things. For example, a team manager is a role. Since a team manager belongs to a specific team, the way I ...
1
vote
1answer
59 views

get query execution time in command prompt [duplicate]

I'm doing this query select * from rentals. This table has more than 10,000 rows and it takes more than 4 minutes to retrieve the results. But I get this time after I get the results: 10000 rows in ...
1
vote
1answer
77 views

MySQL difficulty with optimizing slow query with index but without improvement. Explains tells otherwise

I've a slow query (~11 seconds) that I'm trying to optimize. But the use of indexes seems to not make any difference even thou Explain indicates otherwise. Let me walk you through my problem. So this ...
1
vote
1answer
45 views

How can I 'further' improve my table and queries

This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query. I also changed my application so that unused filters weren't ...
1
vote
1answer
93 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
1
vote
1answer
26 views

What are the consequences of enabling slow_query_log and flushing the cache on mysql?

I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing ...
1
vote
1answer
58 views

Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes? If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate ...
1
vote
2answers
293 views

Slow SQL query with LEFT JOIN

What my query is doing: I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current ...
1
vote
1answer
101 views

Speed up COUNT(*) when using an index

I have a table similar to this (simplified): CREATE TABLE books ( id INT AUTO_INCREMENT, category INT NOT NULL, PRIMARY KEY (id), KEY (category) ); This table has over 10,000,000 ...
1
vote
1answer
120 views

Calling a View from Procedure

I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters. Ex: CASE pSearchType WHEN 1 THEN SELECT Col1, ...
1
vote
1answer
61 views

How can I improve this query in PostgreSQL?

I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have counts in them, such as: SELECT COUNT(*) AS count_all, listings.id AS listings_id FROM "listings" ...
1
vote
1answer
43 views

Statistics needs to be captured for query analysis

What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for: Query elapsed time Logical read Logical write Physical read ...
1
vote
2answers
83 views

Is there's another function to optimize this query in MySQL?

I want to use a function that returns for me the same result in one line is that's possible?? MIN( CASE WHEN (data.pro LIKE '1 %' ) THEN 1 WHEN (data.pro LIKE '2 %' ) THEN 2 ...
1
vote
1answer
171 views

Why isn't Postgres doing an index-only scan?

I am using Postgres 9.4. This is my table: id | integer | not null default nextval('frontend_prescription_id_seq'::regclass) presentation_code | character varying(15) ...
1
vote
2answers
38 views

Two (almost) identical queries, but only one of them uses the index

I have a table with two columns, batch_id and study_id. There are indexes on both columns. I want to get a random row back from MySQL. I originally wrote: SELECT * FROM alt_join_batch_study ORDER BY ...
1
vote
2answers
231 views

Viewing execution plans for queries with parameters

I have a sample query generated by an application (Microsoft Dynamics AX 2012 in this case) which is ineffective performance-wise (cross joins, order by etc.) I would like to display its execution ...
1
vote
1answer
92 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 ...
1
vote
1answer
49 views

Can I use SPARSE somehow on a non-nullable bit column with mostly false values?

I have a table that stores the results of queries that are run at least once a day. There's a bit column that represents whether the row is from the most recent run of a particular query with a ...
1
vote
1answer
87 views

Query performance issues

I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the ...
1
vote
2answers
157 views

Fourth inner join to the same table increases query duration significantly

As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec. The table in question is deployments d4. The SQL Query: select a.id as ...
1
vote
3answers
77 views

Performance first low, then high after adding index, then keep high after drop index

I am testing performance a complicated select query. It is very slow. using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. result comes out fast. However, when I DROP the INDEX I ...
1
vote
1answer
183 views

SQL : get rows from two tables relatively

I've two tables named categories and products. Simple structure is given below. categories cat_id cat_name 1 c1 2 c2 3 c3 products prod_id ...
1
vote
1answer
38 views

How can I speed up this simple query

I am trying to speed a query that grabs data to be used to display a graph for a research project. It's currently VERY slow. It has 18295603 items only and takes about 20s per query. Here is an ...
1
vote
1answer
821 views

MongoDB performance problem

I have a MongoDB for storing result like this {_id, type, imei, lat, lng, spd, dir, time} {_id, type, imei, lac, cid, time} If type is f first one is used and if it is l second one is used. Here ...
1
vote
2answers
112 views

Is there a more efficient way to run this query for first/max transaction?

For each account, I need the first transaction date, the largest transaction amount, and the time taken to get from first to largest. Data is truncated and reloaded frequently, and I have read-only ...
1
vote
1answer
71 views

A table that consists of multiple FK or creating separate tables for each FK

I have tables called 'books', 'category', 'subcategory', and 'booksCategory'. Each book can have its own category and subcategories. For example, a Sci-Fi book can belong in the 'category' table under ...
1
vote
1answer
497 views

Tuning Mongo (TokuMX) for fastest bulk reads

What options do I have for tuning MongoDB / TokuMX for reading the results of queries with large result sets? I'm currently on a single server (no sharding) with the data on an SSD drive. My queries ...
1
vote
1answer
135 views

Performance issues when extracting scatter plot data

First off: Sorry for the title, couldn't think of anything better. Feel free to change it though. Ok, assume I have the following table: ...
1
vote
1answer
88 views

Any idea why this seemingly-simple query is really slow?

Here's the query SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL WHERE r.event_id = "102577"; It seems pretty ...