In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

learn more… | top users | synonyms

0
votes
1answer
21 views

MySQL script writing slow

I'm using php and the exec() function to run 2 MySQL script files one after another The first file has approx 2k update statements (see below) in and is 114kb in size. UPDATE table1 SET is_active = ...
0
votes
1answer
47 views

Best query to exclude existing friends from query

I have a User table / id_user / username / .. friends table (pair AB and BA): / friend_of / friend_to and a friend_request table (used for pending friend requests) / id_req/ sent_from / sent_to ...
2
votes
1answer
58 views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it ...
2
votes
2answers
71 views

How can one optimize a query involving a sort and a LIKE?

Here is the query: EXPLAIN ANALYZE SELECT * FROM "Users" AS "User" WHERE "User"."name" LIKE 'garr%' AND "User"."id" NOT IN (2449214) AND "User"."hellbanned" IS NULL AND ...
0
votes
1answer
24 views

What is different between fixed-size disk pages and disk-based?

As per my knowledge, i understand fixed page size as size of page which used for read and write data but i am not able to understand what does mean of disk based ? I read these words from this paper ...
0
votes
2answers
38 views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
3
votes
0answers
78 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
1
vote
2answers
63 views

Are nonclustered indexes used when joining tables?

I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them. But I've read recommendations to noncluster index all FKs, I ...
1
vote
0answers
39 views

How to optimize inefficient query in PostGIS

I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow). This query takes about ...
3
votes
2answers
153 views

Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
1
vote
1answer
18 views

How to optimize search restricted by boolean column?

I want to optimize a query1 of the form SELECT yid, xid FROM x WHERE is_principal; Here, is_principal is a non-null boolean column of x. If I run EXPLAIN QUERY PLAN on this query, the output I get ...
1
vote
1answer
42 views

Incorrect unit allocation size. error comes when Tuning the Database

Here I am Using the SQL IDERA tool for checking the Index and performance of the Database, then i found below error, can any one tell what is it talking about. Incorrect unit allocation size. ...
0
votes
0answers
46 views

Can an index be added to a PRIMARY with a null key?

I have a query that takes a long time to return, roughly 12-15 seconds, which I'd like to optimize. The query below is the result of running the original query with SHOW WARNINGS. If I run EXPLAIN ...
0
votes
0answers
25 views

How can I optimize a query with multiple left joins?

The working example that I have been provided with takes about 20 minutes to fully process. http://paste2.org/G8BZvNHe What are some basic strategies for making this more efficient? Edit: I've ...
0
votes
0answers
35 views

MySQL Query Causing Massive Slowdown Over Time

I've been experiencing massive slowdown on my server after launching a new website. Using MONyog I can see that after an apache/mysql restart, the queries/second starts out nice and low for around 10 ...
0
votes
2answers
50 views

What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?

Disclaimer: I am relatively new to PostgreSQL. I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple: Select Posts with a photo (Posts.photo IS NOT NULL) and a ...
0
votes
0answers
25 views

Creating separate small table from big based on access frequency for “select” performance

I have table with several millions of rows which are selected a lot (many times in a second in average). It is indexed and everything is ok for now. But performance is always a question (sometimes ...
1
vote
2answers
53 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) ...
0
votes
1answer
58 views

mysql multiple column index Cardinality order

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y). If X has High-cardinality than Y i.e., X is less ...
1
vote
1answer
90 views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
6
votes
2answers
583 views

Can rebuilding indexes cause worse performance after the rebuild is finished?

We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is ...
0
votes
0answers
22 views

Best mysql database structure for this data

I need to create a MySQL database that can store the following data for many users: General settings (for each user) 10 url's. 10 words of 20 letters for. ...and more fields like these. Sentences ...
1
vote
1answer
49 views

MySQL column type to boost performance

I'll have a table where: userid (link to user's id in users table) foldername (a varchar, max. 260 char) entry (varchar, max. 1024) data (varchar, max. 20kb) Single user will have several ...
1
vote
1answer
106 views

optimize design for a table with 50 columns [closed]

I have a table with 50 column and one of my friend has told me to split the table in 4 different table to save 12 column in each table. He proposed a design to have Master table and refer the PK of ...
0
votes
0answers
33 views

Large text column slows down whole table

I have a mysql database table table1 with a dozen columns in it. One column is a mediumtext column with lots of data in it and all the other columns are small (ints, timestamps, etc.). The presence ...
0
votes
0answers
72 views

Database design for price comparison site - too complex?

I'm working on a regular price comparison site with two exceptions. I want to show price history and also allow users to subscribe to a product with a specific size and color. As a result, I've ended ...
1
vote
0answers
36 views

Delete Query throwing warning db2

I am trying to delete multiple records from view The view has Instead of Trigger defined .. The view has records consolidated from multiple tables. [db2inst1@testServer001 ~]$ db2 "delete from ...
0
votes
0answers
87 views

How to Insert records from remote database to local database using dblink in postgresql 9.3

I have a scenario like i have a two DB, one resides in remote server and other resides local. Now i have to fetch the records from remote DB when new records are inserted on tables (in remote DB) and ...
2
votes
0answers
75 views

Why is the CTE much worse than inline subqueries

Im trying to better understand how the query planner work in postgresql. I have this query: select id from users where id <> 2 and gender = (select gender from users where id = 2) ...
0
votes
0answers
32 views

Create Temporary Index for bulk operation and Delete after its completed

I am moving bulk data to archive tables. Some times query returns up to 4.5million records, which takes about 2:15 minutes for a table of 40 columns. I want too increase the performance for the ...
0
votes
1answer
36 views

What would be most elegant way to replicate data contents periodically into a MySQL table?

I need to parse contents from an API route and insert into a MySQL database. The aim of parsing contents is to replicate the data from the API to my database table. I need to check API response every ...
0
votes
1answer
40 views

“explain analyze” cannot get detailed analyzed results for queries in a plpgsql function

I am using postgresql 9.3 I have a function (Pl/PgSQL) created with serveral complex queries inside. For example, create function f1() return integer as $$ declare event tablename%ROWTYPE; .... .... ...
0
votes
1answer
87 views

MySQL: Optimize query with a range and distinct count

I have a query on a large table with millions of rows that looks like this: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE ...
3
votes
1answer
881 views

Sql server table insert performance optimization

Setting In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
1
vote
1answer
48 views

If a database is in memory does it still need indexing?

INFO I'm following lessons on databases. We're learning about indices now. Question If big parts of a table are in the memory, would using a index still have benefits? My side I would assume ...
3
votes
1answer
93 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
0
votes
2answers
62 views

How to use max function in such condition?

My query is select * from guestuser union select * from olduser ORDER BY userid DESC LIMIT 1; Is that correct query to find last userid from different table have different userid? can i change ...
0
votes
0answers
47 views

Slow MySQL Query when Ordering

This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and ...
0
votes
0answers
48 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
1
vote
2answers
85 views

Simple query is slow on 4M-rows table

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to ...
1
vote
1answer
48 views

How to manipulate join order of PostgreSQL optimizer?

I am working on table join order optimization. While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL? How can I update ...
0
votes
4answers
102 views

DB performance vs replication

I am working on a db optimization (planing for future project growth) and need some help. Currently, every table is using uniqueidentifier as PK (clustered index) and we have high index fragmentation ...
0
votes
1answer
45 views

MySQL optimize range duplicate query

I need to eliminate duplicates from a time sheet. I found this solution and have adapted it for my own needs: DROP TABLE IF EXISTS `activity`; CREATE TABLE IF NOT EXISTS `activity` ( `id` int(11) ...
0
votes
0answers
71 views

Configuration help my.cnf Mysql

Is the following my.cnf file OK for a dedicated server with 56GB of RAM (on ubuntu server and 8 processors)? I'm running just one website on the server and there is a high demand on my cpu from ...
1
vote
0answers
72 views

Optimizing SELECT queries

I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I ...
3
votes
1answer
130 views

When is mariadb creating tmp tables?

I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
0
votes
0answers
38 views

Oracle estimate cardinality on partitioned table

How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join? For example, if I have a query like this select * from ...
0
votes
0answers
44 views

is there any limit of MySQL status variable value?

As we know MySQL status variable is a counter variable since the service start. MySQL service in my DB server restart every week (on sunday). Below is my observation since last few week: Suddenly ...
0
votes
1answer
11 views

How to Search a Database of Package Deals for Specific Items with the Fewest Unwanted Extra Items

Short Description I have a database of special offers that bundle several items together. I want to be able to search for a list of specific items and get a combination of bundles that includes all ...
0
votes
1answer
50 views

Help with optimising a sql query on two partitioned tables

I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate ...