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
votes
1answer
117 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) ...
3
votes
1answer
104 views

Simple SELECT takes forever, but works as a subquery. Why?

I'm running a MySQL 5.1 server and I have a MyISAM table called my_table with nearly 5 million records. Its structure is the following: ...
3
votes
1answer
465 views

mysql: need help to optimize my query/table

I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
3
votes
1answer
816 views

Mysql Server optimization tips needed! (InnoDB)

I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. ...
3
votes
1answer
2k views

Optimizing MySQL for Read-Only?

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
3
votes
1answer
113 views

How can I find out which statistics are evaluated by the optimizer?

My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. ...
3
votes
1answer
2k views

Understanding optimisation of complex SELECT queries

I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table. I want records that match ((criterion 1 or criterion ...
3
votes
1answer
136 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
3
votes
1answer
525 views

MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob

I have a mysql insert on update query like so insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars'; col1, col2 ...
3
votes
1answer
2k views

Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?

We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM ...
3
votes
0answers
60 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, ...
3
votes
0answers
143 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 ...
3
votes
1answer
152 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 ...
3
votes
1answer
1k views

Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted). Furthermore I'd like to show ...
2
votes
1answer
3k views

Why isn't oracle using an index for distinct query ?

This is the scenario SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true) PL/SQL procedure successfully completed. SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
2
votes
2answers
282 views

Why does adding LIMIT to my query make it crawl?

Simple query: select sum(score) total,name,gender,dob,country from users join scores on users.id = scores.user_id where date between '2012-01-01' and '2012-01-31 23:59:59' group by scores.user_id ...
2
votes
3answers
4k 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?
2
votes
2answers
241 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
2
votes
2answers
1k views

Trying to optimize query for performance (MySQL)

So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
2
votes
2answers
614 views

What are the options of reducing tempdb latency?

Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed. All of them are running inside virtual machines. One SQL Server per a ...
2
votes
1answer
45 views

Convert Log table days in a state

This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following... Input Table Date Item Moved to Box Oct-1 ...
2
votes
2answers
3k views

How to improve MySQL Server Performance..??

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers. Now my question is where to start from like we will need to find out many things as 1.Find the ...
2
votes
1answer
4k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
2
votes
1answer
300 views

Optimize Percona MySQL InnoDB 3.2TB database

(Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project) I have a database server that ...
2
votes
1answer
167 views

Is there a negative effect if I place an index on every column in a wide table?

We have a wide dataset that isn't very tall (say 250K rows, 90 columns). We will be providing users with an interface that allows them to generate arbitrary queries against this dataset. They can ...
2
votes
1answer
209 views

Clustered composite key high fragmentation and fill factor

I've recently made a table to hold the language preferences of my users as follow: CREATE TABLE [dbo].[systemUserLangPreference]( [systemUserID] [int] NOT NULL, [langID] [int] NOT NULL, ...
2
votes
2answers
2k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
2
votes
1answer
57 views

Is there any way to get the optimization time in MySQL?

I'd like to see the time optimizer takes to optimize a query. Is there any way to get that time?
2
votes
1answer
1k views

Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables. But before doing so, I would (of course) like to know if ...
2
votes
1answer
140 views

How to know what to investigate and how for a VERY slow stored procedure

I have inherited a large and slow stored procedure and it's giving me a nightmare: I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck. I have ...
2
votes
1answer
3k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
2
votes
5answers
554 views

Help optimizing query in the MySQL slow-query log

Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these ...
2
votes
2answers
202 views

In MySQL, should I add an index even if the query that scans the table is only ran once a month?

Concretely, is it worth it to add an index to a query that scans the table of a mysql database, even if that index would only be used once a month? For example, having a users a table and then ...
2
votes
1answer
47 views

Oracle 11g hash join vs nested loops question

I'm using Oracle 11g and working on a legacy system. I have two tables, A and B. A has ~40M rows and B ~4M rows. A has a foreign key on B (B is the parent of A). A query does essentially this: ...
2
votes
1answer
101 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
121 views

Is it always faster to create indexes after loading data?

I have a large number of records (~1 billion) that I need to load into MongoDB (actually TokuMX, but whatever). I have about 6 different indices I need to create on the collection. Is it always faster ...
2
votes
1answer
362 views

Specifying columns in LEFT JOIN through subquery beneficial performance wise?

I'm trying to understand how JOINS are handled, and was wondering about the following: Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty ...
2
votes
1answer
1k views

How to optimize a query that includes counts of rows in multiple tables?

My query looks like this: EXPLAIN SELECT ... FROM USER u LEFT OUTER JOIN (SELECT COUNT(*) notified_count, user_id FROM user_email_tracking ON u.id = email_tracking WHERE email_type_id = 4 ...
2
votes
2answers
127 views

Optimize query or model, to get an population overview in time

I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance. I wonder if there is a trick in my model, or MySQL query, to speed up the ...
2
votes
3answers
1k views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
2
votes
1answer
1k views

How to speed up mysql query?

I have query : SELECT `school`.`ARCO_name`, `student-`.`ClassSize_7`, `student-`.`ClassSize_8`, `degree_o`.`degree_code`, `accredit`.`full_faculty_3`, ...
2
votes
2answers
1k views

In DB2, how can I disable NLJOIN?

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query. I've been looking for this for a while without any success. I ...
2
votes
2answers
116 views

How to handle too many inserts?

I have a database that is accessed from remote locations in the world with very long delays and poor transfer speeds. I currently transfer the data using a transaction since either all or no data ...
2
votes
1answer
703 views

How to get rid of clustered index scan?

I have a query that was generated by C#/Linq-To-Entities code. As with this kind of query, it looks seriously ugly and I doubt it would help to include it here. But I have run it through a query ...
2
votes
1answer
573 views

mysql optimize group by having count

I have 2 mysql table: table t1: CREATE TABLE IF NOT EXISTS `t1` ( `id` bigint(20) NOT NULL, `url` varchar(255) DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `type` varchar(255) DEFAULT ...
2
votes
1answer
43 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...
2
votes
2answers
127 views

Convert to Self JOIN

I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE ...
2
votes
2answers
609 views

Setting innodb_buffer_pool_size in for Mysql

We have a dedicated DB box with with 8 GB of RAM and running Redhat linux 64 bit OS. Our MySQL Data + Index size almost 7.8 GB. About setting innodb_buffer_pool_size, I have read 2 suggestions Set ...
2
votes
1answer
135 views

Why isn't the rows of an EXPLAIN accurate?

I have the following table: mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.03 sec) I do an EXPLAIN: ...
2
votes
2answers
112 views

Optimizing index creation

One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns. An example case would be: table1 ...