In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.

learn more… | top users | synonyms

1
vote
1answer
108 views

Does PostgreSQL optimize queries in transaction?

In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, ...
1
vote
1answer
93 views

What is the best relationship model for high performance?

I want to model a relationship between Customer and Transaction. However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
1
vote
1answer
1k views

Planning simple CMS: How much relation?

I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
1
vote
1answer
152 views

Optimizing table and choosing storage engine

Having the follow MyISAM table: +----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
101 views

Should I Avoid Repeated References to Aggregate Columns in a View?

So I'm wanting to summarize the balance of the accounts in my business application. My (simplified for our purposes) data model looks like: --Main account table that holds the single accounts create ...
1
vote
1answer
40 views

optimize the query with big and low cardinality

I have big enough dataset and few joins to get them all with help next query: SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER ...
1
vote
1answer
146 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 ...
1
vote
2answers
63 views

index for OR operator with different columns

What would be best index for this kind of query: -- id is the PK create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null); select id from test3 where id=:1 and ...
1
vote
1answer
55 views

How can I optimize this query?

I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query: ( SELECT `time` FROM `eventlog` WHERE userid = ...
1
vote
1answer
572 views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
1
vote
2answers
888 views

Optimize a MySQL Database using MySQL Workbench

I actually have a few issues. First, I found this information about optimizing: MySQL OPTIMIZE all tables? The problem is, I don't seem to have a mysqlcheck program anywhere. I'm using MySQL ...
1
vote
1answer
62 views

MySQL geospatial query optimisation suggestions

I am new to DB Administration. Trying to sort out this query which is the heart of our application. I am searching google on similar questions, but would like an experts opinion on my particular ...
1
vote
2answers
141 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
1
vote
1answer
82 views

extra steps after changing storage engine and adding index

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases. I have a php/MySQL site uses the MyISAM storage engine. It ...
1
vote
2answers
312 views

Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

Imagine you have inherited some project with extensive usage of SQL queries. Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point. A question is: could ...
1
vote
1answer
66 views

Mysql performance for a nullable unique id column in a large table that references a record in another table

We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the ...
1
vote
1answer
628 views

MySQL subqueries that use range based on values of main queries don't use indices properly

I think I've isolated a problem that has been affecting many of my queries lately. And would like some help to figure out a solution for this. Ok so my findings are that a normal query that runs very ...
1
vote
1answer
135 views

Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the ...
1
vote
1answer
121 views

Proper indexes or anything else to optimize

I need help to optimize the query to avoid using "Using temporary", "Using filesort". CREATE TABLE `target_bahrain_pepsi` ( `id` int(11) DEFAULT NULL, `col_value` varchar(50) DEFAULT ...
1
vote
1answer
169 views

Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
1
vote
1answer
2k views

MySQL: how to optimize the stored procedure?

MySQL 5.5.28 Running pt-query-digest on a slow query log, I get something like this: # 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # ...
1
vote
1answer
1k views

Slow complex query with group/order

I have a problem with a very important query in my application. It is very slow when adding group and order to it. So the full query is: SELECT m.id, m.title, m.title_text, ...
1
vote
2answers
267 views

Explicitly update a table after sp_tableoption “text in row” advices wanted

I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set ...
1
vote
1answer
248 views

Optimising a sql correlated subquery statement

I am updating a column on tableA with data from tableB like this: update tableA set bKey = select bKey from tableB B where B.colB = tableA.colB bKey has a unique index on tableB. Which is the ...
1
vote
1answer
1k views

At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run. I guess that it ...
1
vote
1answer
1k views

Help optimizing MySQL slow query

i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort" query: EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
1
vote
0answers
93 views

Optimization Tips For MYSQL Tuner/Primer

So I have a site that has a crap ton of traffic. I see a decent amount of latency when the site has over 1,000 active visitors on it. I have new relic installed and it seems Im only using 10% of the ...
1
vote
0answers
55 views

Refactoring IN Subquery, Avoiding Query in a Loop

I've got a table which was designed as both the current data set for participants, as well as being the historical changeset for those participants. This table has been in use for approximately three ...
1
vote
0answers
23 views

Querying number of tries since last successful log, checking IP as well as user

I asked this question before on stackoverflow, but it might fit better here, since it is mainly about database performance. The actual question With performance in mind, how would you go about ...
1
vote
2answers
173 views

Building A SQL table horizontally VS vertically

What is a better design principle when designing SQL Server tables. One can either add columns (ie grow horizontally) or grow it vertically (ie rather adding rows) I have a situation when there are ...
1
vote
0answers
39 views

Significant difference between duration for querys in SHOW PROFILE and SHOW PROFILES?

I have use mysql profiler to optimize an opencart query which takes more than 10 seconds to executes. mysql> SHOW PROFILES; ...
1
vote
1answer
123 views

How to improve the performance of SELECT queries with multiple JOINs on a 50GB database hosted on AWS m3.medium MySQL RDS?

Example query: SELECT p.ListAgentEmail, p.ListAgentFirstName, p.ListAgentLastName, p.ListingKey FROM Property_RES AS p INNER JOIN Property_RES_COUNTIES_OR_REGIONS ON ...
1
vote
0answers
179 views

Truncating Table Partitions Containing Data and Global Indexes

I have to truncate a partition (392,000 records) of a table with 20.000.000 records. At the moment, I'm making a truncate table and rebuilding indexes. But the table has 37 indexes and it takes 20 ...
1
vote
1answer
177 views

Inserting to table: Insert rows with Left Outer Join or Insert rows then Update?

I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row. Not every row in Orders will have a row in ...
1
vote
0answers
53 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 ...
1
vote
1answer
97 views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
1
vote
0answers
79 views

Query optimization, SQL Server cache query, SQL Server execution query

I have 2 queries, the results of their execution is the same, but I don't know which of these is the best in terms of performance and why. Query #1: SELECT CASE WHEN ( EXISTS (SELECT 1 AS [C1] ...
1
vote
1answer
59 views

Do id columns help (in speed) select statements?

I have a table which contains data from aggregation software. The columns mostly are int columns, but three of them are string columns. It looks sort of like: userId, someData1, someData2, ...
1
vote
1answer
275 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
1
vote
0answers
80 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
0answers
123 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
1
vote
0answers
119 views

Optimising Large SQL Server Tables [closed]

I have a large table with around 50 million rows and 15 columns. Whenever I read, I always need all columns so I can't split them. I have a clustered index on the table with 4 keys (ALL INT) and I ...
1
vote
0answers
127 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
1
vote
0answers
570 views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
1
vote
0answers
607 views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
1
vote
4answers
156 views

Normalization of DB

I have designed a database using MySQL likewise My question is what else i can do to normalize the DB, for now i am using InnoDB engine for the table to maintain the relation among different tables. ...
1
vote
0answers
328 views

Finding the bottleneck by analyzing Munin graphs

We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
1
vote
1answer
595 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
1
vote
2answers
724 views

Slow mysql fulltext query

I have a little problem with the performance of a mysql-query which uses a fulltext index. The following query SELECT Mention.id FROM mentions AS Mention WHERE (MATCH (`Mention`.`title_text`, ...
1
vote
2answers
98 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...