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)

1
vote
3answers
73 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
270 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
100 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
39 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
57 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
202 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
238 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
853 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
100 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
420 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
83 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
69 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
92 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
62 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
109 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
104 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
36 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
56 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
165 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
99 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 ...
2
votes
1answer
284 views

MySQL - OPTIMIZE TABLE for partitions

I would like to perform OPTIMIZE TABLE opearation , this table is a partitioned table. Which method can be better in this scenario: OPTIMIZE TABLE tblname; or ALTER TABLE tblname ...
4
votes
2answers
114 views

JOIN on 3 tables being slow

everyone. Can't figure out what indices are missing or how to rephrase my query. I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is ...
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 ...
5
votes
2answers
1k views

MySQL queries are 10 to 100 times slower after OS reboot

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to ...
1
vote
2answers
127 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 ...
3
votes
3answers
350 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 ...
2
votes
1answer
104 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 ...
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 INDEX(`COL3`...
0
votes
1answer
33 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 ...
10
votes
3answers
13k views

Why does MySQL ignore the index even on force for this order by?

I run an EXPLAIN: mysql> explain select last_name from employees order by last_name; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | ...
6
votes
1answer
256 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
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
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. ...
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 (...
1
vote
1answer
72 views

Using NOCOUNT to Improve Procedure Performance

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON. I've read a couple of articles on the subject: Aaron Bertrand SET ...
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
1answer
149 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 1:...
0
votes
2answers
166 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
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 ...