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
38 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 ...
1
vote
2answers
352 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 ...
2
votes
1answer
36 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[]) ...
1
vote
0answers
43 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
90 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, ...
0
votes
0answers
48 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
24 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, ...
3
votes
2answers
70 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
200 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 ...
1
vote
1answer
37 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
1answer
31 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
408 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 ...
7
votes
1answer
181 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 ...
2
votes
1answer
76 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
0answers
85 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 ...
1
vote
0answers
60 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 ...
1
vote
0answers
35 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 ...
4
votes
2answers
105 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
54 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
153 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
92 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: ...
0
votes
1answer
73 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 ...
0
votes
1answer
31 views

Struggling to design addresses table for fast querying

I have two tables: addresses and locations. Locations zipcode city county state Addresses line1 line2 city county state zipcode Posts Id Address_id Body All my users are required to provide at ...
0
votes
1answer
32 views

Track database name in anemometer queries

Hello i have installed anemometer on our server in order to see the queries that need optimization. From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is ...
2
votes
2answers
71 views

Optimizing a MySQL “IN” query

I'm trying to optimize a MySQL table for a query of the form: SELECT Col1, Col2 FROM MyTable WHERE Col3 IN('Val1','Val2','Val3',...) I've added an index to the table: ALTER MyTable ADD ...
2
votes
1answer
87 views

Slow CREATE TEMPORARY TABLE from SELECT in MySQL

I asked this on StackOverflow, but there are not many hints. So: SHOW CREATE TABLE cashadv_txn: CREATE TABLE `cashadv_txn` ( `txn_id` int(11) NOT NULL AUTO_INCREMENT, `cashadv_id` int(11) NOT ...
3
votes
3answers
276 views

Having both a integer Primary Key and a GUID/UUID collumn on same table-is this bad

I have my MySQL database having tables where on each table, we have a ID (int,auto increment) Clustered Primary key and a UUID(varchar36) collumn thats just a GUID. The ID is there just for indexing ...
1
vote
1answer
33 views

SQL Server 2008 running schema query without identifiable cause

I am analyzing the performance on login to an application and in particular making sure the database response is acceptable. In running just the simple Activity Monitor I can see that there are two ...
0
votes
1answer
35 views

Optimise and merge SELECT query results from 2 tables

I have a schema like this: And a query like this SELECT `users`.user_id, `users`.user_name, `users`.user_email, `users`.user_password_hash, meta_a.first_name, meta_b.last_name FROM ...
6
votes
1answer
244 views

Running out of disk space running MySQL query

I have been trying to create a "gaps and islands" identification routine in MySQL. See the previous question regarding setup here and computational resources required here. I increased the RAM of the ...
1
vote
2answers
114 views

Which service to use for unoptimized, slow mysql database ?

This will sound strange, but long story short. I have client running two CS Cart web shops one one dedicated server. Cs Cart is version 3.0.2 and each shop has around 2000 products. Website opens ...
0
votes
1answer
48 views

Slow query with JOIN

I have a stored procedure which runs pretty slow (6 sec) using the following JOIN: JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange) Without ...
0
votes
2answers
135 views

SQL Server Estimated I/O Cost in real units

As I understand from other StackExchange posts, SQL Servers estimates are unitless meaning an Estimated I/O cost of 2.82387 doesn't refer to the time taken or disk I/O's, but a relative measure of how ...
0
votes
1answer
109 views

How to optimize MySQL SUM query even after adding index

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table ...
0
votes
1answer
28 views

Optimising a query with two aggregate fields

I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results. For every combination of typeId ...
0
votes
2answers
178 views

Force Sql server to use index seek in merge join

I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN: ID in Personal Table is a Primary key ID in Abteilung Table is a primary key There is no other ...
0
votes
0answers
29 views

Optimize FROM clause by checking row existence

Provided that I have following type declaration: CREATE TYPE dbo.ID_TABLE AS TABLE ( id INT NOT NULL, PRIMARY KEY (id) ) and the following query: DECLARE @ids dbo.ID_TABLE INSERT INTO @ids ...
1
vote
1answer
61 views

Join external data in SELECT query

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
0
votes
0answers
97 views

Join on “poor” table

I have some data on same legacy tables that we've inherited. From those tables I need to make a query from 3 them, but the seems like the join for one of them is taking for ever! The query itself in ...
0
votes
1answer
63 views

get all the count of sub category from the product table in single query with mysql optimization [closed]

Table structure: CREATE TABLE some_table ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `Product_model` varchar(255) NOT NULL, `product_discount` ...
5
votes
3answers
3k views

Where column not like multiple values

I am trying to select records in a postgresql db where username is not like a list of strings. SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%'; The issue is there ...
0
votes
1answer
70 views

Finding the right pattern for creating indexes,redesign query structure

I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables. Because the logic of the initial query was to bring data based on ...
2
votes
1answer
2k views

MySQL InnoDB page_cleaner settings might not be optimal

Seeing this note in mysqld.log: [Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.) There seems to be ...
0
votes
1answer
48 views

Insert query optimization

Need your help optimising an insert query. I have two tables in my MySQL DB. I will be inserting into Table1. Information will be retreived in Table2. Table 2 contains a couple of million records. ...
1
vote
1answer
125 views

How to get SQL Server CPU and Memory read and write usage within two weeks?

Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems ...
0
votes
0answers
65 views

Mysql FULLTEXT index not used in Many to Many table join search

I've got a set of data in the Books table, with BookID as the primary key. I've also got Subject and Location tables, each of which is joined to the primary Books table with a Many to Many table. ...
2
votes
1answer
76 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
1answer
112 views

Better ways than rownum limits on a union all for fallback-style queries?

I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to ...
-1
votes
2answers
236 views

Is there a way that I can merge the following update queries into one?

Is it possible to merge the following update queries into a single query? UPDATE tag.ItemTagValue SET [Value] = @firstNode WHERE FkTagID = 6 and TagIndex = 1 ...
0
votes
1answer
33 views

Exhaustive SQL stored proc validation make insert slow

So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job. The requirement are as follow: - Exhaustive validation should be done for each ...