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 (3)

2
votes
1answer
91 views

Would a cron job to automatically optimize tables on a monthly basis be ideal?

I'm wondering if running the OPTIMIZE command via cronjob would be an intelligent move? Sometimes I forget to run optimize for months at a time. My database is frequently using select, insert, update....
3
votes
1answer
54 views

Timestamp Difference - Syntax Error - Condtitional Selects

Here is what I have right now SELECT name, admin, reason, unban, FROM_UNIXTIME(date) AS datetime, HOUR(difference) AS HRemain, MINUTE(difference) AS MRemain FROM (SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE,...
0
votes
0answers
60 views

Optimize MySQL JOIN Query

I've been using following Query to fetch data from MySQL. SELECT SQL_CALC_FOUND_ROWS DISTINCT (wi_individual_g.ind_id), wi_individual_g.ind_id, wi_individual_g.ind_name, ...
0
votes
2answers
63 views

How to set query_cache_type=2 on mac?

I want to enable query_cache_type and change its value to DEMAND. When i am doing it via terminal its giving this error:- ERROR 1651 (HY000): Query cache is disabled; restart the server with ...
2
votes
3answers
380 views

Count is slow Oracle

this is my query : SELECT Count(last_upd) FROM S_CONTACT WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd + 1/24 > SYSDATE - (1 / (24 * 60)); It takes 84 seconds to return ...
0
votes
1answer
93 views

How to optimize database for specific queries?

Hello I have built this queries thanks answers to my questions: How to get data from three different tables and How to show rows with 0 without any records in database But now I have performance ...
3
votes
1answer
124 views

Improve performance of query with multiple group by column and count function in select

Brief Background of issue We have a aggregate table to store activities of competitors and this table have currently 3 Million activities and we need to retrieve data from this table group by ...
1
vote
1answer
147 views

Maximum possible memory usage over 100%

During these days I experienced several problems (delay and failed restart) on my database MySQL, so I tried with some help here around (and using Percona service too) to optimize my /etc/mysql/my.cnf ...
1
vote
0answers
50 views

Disable/Remove Table Index for INSERT Performance

I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
1
vote
1answer
100 views

Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?

Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space. Have option to either optimize the table, or dump the table and restore ...
9
votes
5answers
297 views

Getting a scan though I expect a seek

I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure: CREATE PROCEDURE dbo.something @...
1
vote
2answers
358 views

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]

I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT: SELECT customs.id AS custom_id, customs.custom_name AS ...
1
vote
2answers
56 views

Optimising a query to get rows where x is between pairs of values from another table

PostgreSQL 9.4.5 In my database there are 2 tables, vcfentries and exons. I want to query for rows in vcfentries where pos is between a list of pairs of positions, exonstart and exonend in the ...
4
votes
2answers
157 views

Will the Query Optimizer Ignore A Fragmented Index?

Scenario: I have a heavy OLTP table with an index. I see many inserts, updates and deletes and the index fragments heavily within a day or less. While on day one of the index build, the optimizer ...
2
votes
2answers
107 views

Retrieving changed rows only from a table with 10^7 rows

I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well. I've ...
1
vote
3answers
281 views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
0
votes
0answers
18 views

The most-recent-record problem with partitioned tables

Today's variant on the most-recent-record problem involves a table partitioned on the timestamp field relevant to the most-recent-record selection, and efficiently joining the result of the most-...
1
vote
0answers
59 views

What are the things that you will look at execution plan [closed]

I can write some good queries and I am trying to understand reading execution plans and see if there can be a better plan for this query. Below is the checklist I derived over time from different ...
1
vote
2answers
919 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
1
vote
3answers
83 views

Improving query time for auto complete box with millions of record

I have a very basic requirement. I have a table that store city id (primary key) and city name (nvarchar value). The remaining information is stored in a separate table. In my UI, I have an auto ...
7
votes
1answer
2k views

How does SQL Server generate a query execution plan that adds up to 6,000%? [duplicate]

Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still ...
0
votes
1answer
66 views

Join on with modified ON

I am trying to create table with summary of few other tables. All solutions i came up with are very slow and clearly not optimal. Initially I tried with multitude of subqueries but that was very slow. ...
2
votes
1answer
288 views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
0
votes
1answer
52 views

MySQL optimizer difference between 5.6.27 and 5.5

We've just updated the MySQL version of the database of one of our clients from the 5.5 to the 5.6, everything is working fine except one simple query which is causing us troubles. Having the ...
1
vote
0answers
44 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
1
vote
1answer
128 views

Database Design and structure

I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, and ...
2
votes
1answer
39 views

How to optimize this query on Postgres

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It sometimes takes up to ...
2
votes
1answer
41 views

OFFSET and LIMIT on complex query

I am doing a pretty complex query in Postgres 9.3.4: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) s(source_id)...
0
votes
0answers
60 views

In which order does a mongodump --query executes

In which order does a mongodump --query executes, is from last insert to first insert or the other way around, if it is other way around is there any option that I can pass to mongodump --query, so ...
0
votes
0answers
25 views

Slow Query - Optimize - PostgreSQL 9.2

I've got a slow query.. I'd like to make it faster.. Make add an index? Query: SELECT j.clientid AS client_id, ni.segment_index AS note_id, f.inode_id AS file_id, f....
3
votes
2answers
71 views

Should I normalise this database?

I have two database designs thought of for storing messages(instant messages) between users. It will be a group chat system. I have two table designs in mind. One is this: CREATE TABLE `...
6
votes
2answers
214 views

SQL Server: Accurate Row Estimates without Histogram?

I'm exploring the optimizer in SQL Server, particularly histograms and I'm seeing some strange, results. If I execute the query below, where I know there is no histogram on the field, SQL Server ...
8
votes
1answer
274 views

Index not used with `=any()` but used with `in`

Table t has two indexes: create table t (a int, b int); create type int_pair as (a int, b int); create index t_row_idx on t (((a,b)::int_pair)); create index t_a_b_idx on t (a,b); insert into t (a,b)...
8
votes
1answer
867 views

The use of NOT logic in relation to indexes

According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development: Neither leading wildcard characters not NOT logic allow the query optimizer to ...
1
vote
1answer
39 views

MySQL Hardware Bottleneck

I have a web application that makes simple queries to a MySQL database. The database is quite large (8.4GB) and the time to lookup an entry needs to be as fast as possible. The database represents a ...
0
votes
0answers
112 views

MySQL Optimization (my.cnf)

My site has been extremely slow lately at peak times (3 hour window each week). My website is an auction site, Ajax-heavy and uses Pusher.com to update bid status and bid amounts in real-time. I tried ...
0
votes
1answer
33 views

New column to a base table

I want to add a new column to a base table, and add that new column to an existing view... So all selects/updates/deletes translate back to the original table How can I do that? Thanks ------------...
4
votes
1answer
421 views

Why did adding a secondary index not increase select performance?

I have a table in MySQL called messages, which looks like this : id (primary key) | description | created_at(timestamp) This table is supposed to hold chat messages between users of my ...
2
votes
1answer
90 views

Optimized MySQL “optional” FULLTEXT search on multiple tables

I need to select data with optional columns in where clause based on client input. my query is like this: SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt FROM a LEFT JOIN b ON a.b_id=b.id ...
0
votes
1answer
70 views

Having four different tables or having one table with one more field?

I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables. Which one of these ways are faster and ...
1
vote
2answers
106 views

simple SELECT EXISTS takes about two hours to execute

I have mysql server hosted. Now, If I issue simple SQL statement SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND PRODUCT_ID='2bc7277f-c7c3-...
1
vote
0answers
66 views

How does logical and physical operators of query plans works in low level? [closed]

As a practice a few weeks ago I decided to build a small database system. I wrote relational tables as object arrays. Also I wrote basic relational algebra operations that operate on my data ...
4
votes
2answers
111 views

Optimize a query with small LIMIT, predicate on one column and order by another

I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times: Query #1 EXPLAIN ANALYZE SELECT posts.* FROM posts WHERE posts.source_id IN (...
3
votes
1answer
105 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
1
vote
0answers
38 views

Most Similar Records Query Performance

I have a mysql database table with 20 million records. It grows at roughly 25,000 records per day. Each record has a number of numeric fields. I have a similarity query that selects the 10 most ...
3
votes
1answer
57 views

Indices for a join query with datetime range

Given the following query: SELECT DISTINCT COUNT(DISTINCT "patients"."id") FROM "patients" INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id" WHERE "...
7
votes
1answer
177 views

Improving a DbGeography query

I am still new to database administration and I'm trying to optimise a search query. I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU ...
2
votes
2answers
103 views

Optimizing a simple query on a large table

DB: MySQL 5.5.20 (WampServer, default configuration) OS: Win 7 HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX) Memory: 8GB MySQL my.ini: http://pastie.org/private/...
0
votes
1answer
74 views

How to optimise multiple joins with OR?

I need to extract some statistical information form drupal database tables. The problem with drupal is that it splits it's data for entities across multiple tables and getting simple information from ...