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
124 views

Need to optimize simple SELECT query for InnoDB table with 2million rows

I'd like to make this query the fastest possible, since it's called really often. SELECT login_events.id FROM login_events WHERE ( DATE(created_at) >= DATE(CURRENT_DATE) AND person_id = 1 ) ...
1
vote
1answer
71 views

DROP TABLE on a huge InnoDB table

I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query? processor: Intel® ...
1
vote
2answers
49 views

Convert to JOIN [duplicate]

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 SSF.SM_StockCode = ...
1
vote
1answer
65 views

Does limit impact affected rows or not?

I have this table: CREATE TABLE IF NOT EXISTS `usergroups` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `group_id` smallint(5) unsigned NOT ...
1
vote
3answers
349 views

Table clean up taking too long

I am selecting around 10 million rows which are invalid from table grid into ##grid_temp and deleting their related info from grid_info table. I am running this in a python script. It is taking more ...
1
vote
1answer
77 views

Query optimization when no row is returned

I am using explain to figure out what is happening in my query which is: explain select t from c where u1_id = 1 group by t; I see "Using where" in the Extra column of the resultset. But this only ...
1
vote
2answers
88 views

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
1
vote
1answer
69 views

Message system - delete message, or flag for deletion?

I'm wondering, what is the best approach about message deletion in a mysql based message system. Plan A: When the sender sends a message, it will be saved in 2 versions. One for the sender and other ...
1
vote
1answer
111 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
94 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
153 views

Optimizing table and choosing storage engine

Having the follow MyISAM table: +----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
102 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
13 views

Speed Up Index Creation in MySQL

In MySQL 5.5, I have a table which is recreated on a daily basis and for which the process of adding an index is taking about 50 minutes which I'm hoping to reduce. At the time when the index is ...
1
vote
2answers
101 views

Database design suggestions for a data scraping/warehouse application?

I'm looking into the database design for a data warehouse kind of project which involves a large number of inserts daily. The data archives will be further used to generate reports. I will have a ...
1
vote
1answer
232 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
64 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
56 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
778 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
1k 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
69 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
163 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
85 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
365 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
70 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
688 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
136 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
123 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
174 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
290 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
264 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
1answer
30 views

Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?

I am running MySQL 5.5.37. I have a cronjob set to run every 6 hours to flush the query cache. I realized that if I don't do that, then the cache becomes fragmented and average query response time ...
1
vote
0answers
193 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
62 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
29 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
288 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
62 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
144 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
331 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
0answers
54 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
152 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
82 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
62 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
332 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
87 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
132 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 ...