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)

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 ...
3
votes
1answer
45 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 ...
3
votes
3answers
282 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 ...
3
votes
1answer
106 views

4m Rows in MySQL, ~5 Minutes to Run Query

I have 2 tables, a users table and a log table which literally logs each time a user signs in. What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a ...
3
votes
1answer
99 views

MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort?

This is my table definition, without any special index for now: CREATE TABLE `filter` ( `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `category_id` varchar(32) COLLATE ...
3
votes
1answer
101 views

Improve performance on SQL Server Query

I'm trying to increase the performance of a query and I'm out of ideas: The query does this: gets a 'Process' with specific priorities on selection namely: Picks up a process that has been waiting ...
3
votes
1answer
127 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
343 views

Problem with PostgreSQL and the geqo optimizer

I'm trying to compare the two execution plans (exhaustive search and geqo) from the optimizer, but I always get the same plan from both types. These are the variables I use for geqo: SET geqo = true; ...
3
votes
1answer
572 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
943 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
3k 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
118 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
139 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
681 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
104 views

Best procedure to measure query optimization effects?

I could not find valuable answers or explanations to the following question: How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps. ...
3
votes
0answers
40 views

Slow Postgresql query with left join

I have a query on Postgres, and I also have added proper index. Is there anything missing here? SELECT orders.*, demo.name as d_name FROM orders LEFT JOIN users as demo ON demo.id = orders.dr_id ...
3
votes
0answers
40 views

Online text based game design - Am I linking the databases correctly?

I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
3
votes
0answers
55 views

Optimizing an Intersect query between two huge spatial tables

I am having a hard time trying to improve an intersect between two spatial tables and I would like to receive any tips about the table designs, queries or dba configs. Tables: Table ...
3
votes
1answer
154 views

How to run OPTIMIZE TABLE on a 3-node cluster?

I've my hands on a 3-node Percona XtraDB Cluster where, according to mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries): mydb.mytable Warning : InnoDB: Index ...
3
votes
0answers
207 views

Form builder schema for SQL database?

There are many ways one can envision the solution, from the top of my head: Two table approach: Field table with (name, field_name, type, description, meta) attributes Form table with (name, ...
3
votes
0answers
101 views

Why the stored procedure is much slower than a temporary table? [closed]

I want to do the updating of my database every day and I will like to use stored procedures but the time of execution of the temporary table is faster than the stored procedure. How can I optimize my ...
3
votes
0answers
379 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
0answers
70 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
3
votes
0answers
405 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
3
votes
1answer
2k 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 ...
3
votes
0answers
969 views

Best book on query optimization, rewriting, refactoring [closed]

I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient. But in a more general sense, I need a book that explains ...
3
votes
2answers
264 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
2answers
389 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
2answers
251 views

Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases. I have then passed this .trc trace file as a parameter to the database ...
2
votes
1answer
733 views

Help required with query (join) optimization

I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause ...
2
votes
1answer
5k 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
324 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
1answer
3k 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
3answers
211 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 ...
2
votes
1answer
104 views

Understanding performance of highly unique WHERE clauses

I've been struggling to understand how to deal with a specific type of performance issue that shows up very often in this scenario: when you are wanting to apply multiple filters in a query, but you ...
2
votes
1answer
111 views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
2
votes
2answers
286 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
68 views

Filtering a result set on a very large table (using INCLUDE index in SQL Server)

Consider a very large table having hundreds of millions of rows defined as -- Here ObjectIdName and ObjectTypeName together are unique. -- Due to their size they aren't indexed, but rather they are ...
2
votes
2answers
103 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 ...
2
votes
2answers
54 views

Optimizing query using a WHILE statement

I'm trying to convert a view that extracts a yearly amount into something more WHILEish The query is more or less: SELECT * FROM (SELECT SUM(EmployeePaid)*2 AS Total2013 FROM TblRecords WHERE FYear ...
2
votes
1answer
191 views

Threshold for sort spill and random cardinality estimates

I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014: USE tempdb IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest CREATE TABLE tblTest ( ...
2
votes
1answer
48 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

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
1answer
5k 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
46 views

Does the query optimiser re-use usable data?

This question is to expand my knowledge on how the query optimizer of SQL Server works. I like to know how stuff works behind the scenes so I can better optimist it/debug it. I have the following ...
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 ...
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: ...
2
votes
1answer
69 views

Why OUTER APPLY forces scanning the entire table?

I have a query which does not performs well and optimizer scans all 108864 items from table ExternFile uselessly when seeking only 30 rows would be fine. I have simplified the problem to following ...