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
2answers
91 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 ...
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 ...
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
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
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 ...
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 ...
2
votes
1answer
270 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
111 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
919 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
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 ...
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 ...
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 ...
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 ...
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 ...
10
votes
3answers
12k 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
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
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
69 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
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
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
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` ...
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 ...
0
votes
2answers
7k views

How to force higher values of open_files_limit or table_open_cache?

I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'. This is MySQL 5.6.14 on MacOS X 10.8.3 I'm trying to address dropped connections ...
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 ...
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 | ...
5
votes
2answers
109 views

Help optimising delete statement

Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate ...
4
votes
3answers
15k views

Is there any command to optimize all MySQL tables one by one automatically?

I execute below command line statement to optimize a table: optimize table tablename; Is there any command or statement which can optimize all the tables one by one of the selected database?
-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 ...
1
vote
1answer
57 views

Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range

I am building a database in PostgreSQL for financial data, where the table looks like this: create table fin_data( brokerid text, stock int, holding bigint, stake float, value ...
0
votes
1answer
55 views

Optimal design for 90mm row clickstream MySQL

Question Which optimizations should I employ to make this DB manageable? I've read a lot of posts about optimizing big dbs, but I'm pretty new so it's difficult to determine which techniques are ...
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 ...
0
votes
2answers
111 views

PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

I have the following PostgreSQL schema: CREATE TABLE User ( ID INTEGER PRIMARY KEY ); CREATE TABLE BOX ( ID INTEGER PRIMARY KEY ); CREATE SEQUENCE seq_item; CREATE TABLE Item ( ID ...
1
vote
0answers
16 views

Variables for Merging MySQL InnoDB Tables

I have several thousand InnoDB tables in MySQL 5.6 that I am trying to merge together into a single table. A simplified form of the queries I am executing (one for each of the tables to merge) is ...
0
votes
0answers
36 views

Rewrite a Stored Procedure

i would like your help in the best way to tune and re-write a stored procedure. it is consuming a lot of time and don't know what should i do to make it faster CREATE PROCEDURE [dbo].[xxxxxxxxxxxx] ...
0
votes
0answers
30 views

Enormous MySQL 5.5 Handler_read_rnd_next

The value of Handler_read_rnd_next is actually 91,3 G on my Database server but I am pretty sure that all tables have proper indexes. I am 100% sure that there is no query with a WHERE clause that ...
0
votes
0answers
61 views

Postgresql configuration for tiny server and append-only data

Currently my database is runing on tiny-sized hardware (digital ocean 5$) and I want to keep this hardware. It is: 512M memory 1 CPU My database works with these conditions: Data is never ...