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
29 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
53 views

How to optimize multiple ORDER BYs?

I'm getting sluggish performance on a table with ~300,000 rows and B-Trees on each column. This is for a dynamic pagination page where the query is constructed on demand, and the application caches ...
0
votes
2answers
69 views

How can I make this query more efficient?

This is the query: SELECT races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at FROM races LEFT JOIN (SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON ...
1
vote
2answers
44 views

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): ...
3
votes
2answers
95 views

How to optimize this slow SQL query

I have a slow SQL query on my Drupal website and I don't know how to optimize it. Here is the query that Views produces: http://pastebin.com/StAEiXbY SQL fiddle: http://sqlfiddle.com/#!2/20081/1/0 ...
3
votes
2answers
68 views

Designating databases to certain CPUs

SQL Server 2012. AlwaysOn Cluster with two nodes, each has 4 cores We are consolidating SQL server instances and moving bunch of databases from various locations to a single cluster which has two ...
2
votes
2answers
69 views

Do MySQL optimizations hold for MariaDB also?

I am a developer who will be starting a new project soon in which database performance will be crucial. The current candidate for the backend is MariaDB. I have worked with MySQL and MariaDB before in ...
0
votes
1answer
51 views

Text Storage and Database Design Optimization in a SQL Server Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
0
votes
2answers
75 views

DB Design Payments Table

I'm working on a Desktop application that uses a DB to store information i.e (customers, houses, proprietors, payments). So the application must accept payments from customers to company, company to ...
0
votes
1answer
51 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 ...
0
votes
0answers
22 views

MySQL Optimisation

I am running mySQL 5.6.16 on a server running Centos with 20Gb RAM ad 1TB disc space. when running SELECT scripts wiht order and group by statements and a number of joins, the results can be very slow ...
0
votes
0answers
27 views

Should I separate my preferences table?

I have a hobby site where users can find other hobbyists of similar interest. Each user can set a series of preferences that get saved into user_prefs. Right now there are 4 types of preferences, an ...
1
vote
2answers
66 views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
0
votes
1answer
45 views

Why is my Mysql Query verry slow when selecting a text field

i have a problem with a left join. Tables are myisam. Tables CREATE TABLE `article` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `force_startpage` tinyint(1) DEFAULT '0', `title` varchar(255) ...
0
votes
2answers
113 views

Long running simple query SQL Server 2008 R2 [closed]

I have a very simple query running in a SQL Server 2008 R2 instance: SELECT TOP 10 * FROM messages m INNER JOIN recipients r ON m.id = r.id WHERE m.timeStamp > '2014-07-14T00:00:00.000' AND ...
0
votes
1answer
16 views

mysql fulltext query is very slow

I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single ...
7
votes
3answers
184 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
3
votes
2answers
56 views

SQL Server Baseline and Performance Optimization

I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance. Could anyone help with this or point me to where I can possibly get a ...
0
votes
1answer
65 views

Help optimizing very slow mySQL query

I have a query which takes about 6 secs to execute. SELECT * FROM (SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...
2
votes
1answer
82 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 ...
0
votes
1answer
48 views

postgres explain plan with giant gaps between operations

I have a postgresql explain plan that seems to have large gaps between the join, sort & filter processes and can't explain (sorry) it. The query is taking an inordinate amount of time and the ...
-1
votes
1answer
31 views

60M+ InnoDB table simple query performance HELP

I know it is a repeated question but really need help here. What can I do to improve the performance of this query? Considering that I have no freedom to change system variables/configs :( Thanks in ...
0
votes
1answer
53 views

Optimization for 100 GB Real-time info table

I have a table of device information feed which is filled by data from remote device units. There will be like 1000 units each sending an update per minute which will be stored as a row in that ...
1
vote
0answers
70 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 ...
0
votes
1answer
45 views

getting “table is full” error even if the table size is much smaller than tablespace

Currently I am using MySQL NDBCluster 7.3.5. I have created one UNDOFILE hhme.dat with INITIAL_SIZE=200m. Also one tablespace hhmefep_tbs with DATAFILE hhmefep_tbs.dat having INITIAL_SIZE=500M ...
2
votes
2answers
81 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
118 views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...
2
votes
0answers
39 views

Efficiently joining two irregular series representing changes

(This is a PostgreSQL-specific question, using version 9.1, but could be upgraded to 9.3.) I have a number of tables that represent a series of changes. These tables all have the same structure: ...
0
votes
1answer
70 views

Need help optimizing query to prevent full table scans

I have the following query, which while it works, is also doing a full scan on each left joined table (the log* tables). I thought by using a subquery in the FROM that this would eliminate the ...
2
votes
1answer
114 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 ...
1
vote
1answer
48 views

Postgres: my “data/backup” directory is way too large

In my postgres 9.3 data directory, there exists a subdir called "backup" that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup? ...
0
votes
0answers
75 views

MySql query, 3 tables join takes 10+ sec

I need help refactoring this query, it joins 3 tables and subquery 4th. Order by should be possible by selected fields from 3 tables. t1 has 70k rows t2 has 20 rows t3 has 10 rows post_cc about 1500 ...
0
votes
0answers
79 views

MySQL my.cnf File Optimization help

I need help for Optimizing my MySQL Server for my Dedicated server with configuration of Intel Core 2 Duo CPU E7500 @ 2.93GHz 4GB of RAM installed. OS installed is CentOS 6.5. Mostly on my Dedicated ...
1
vote
0answers
52 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 ...
0
votes
1answer
36 views

Efficient matching rows across two tables with huge data set

I have two tables with about 300+ columns and 20k+ rows each. Each row is characterized by a primary key which both tables have.I have to find the identical rows, rows with changes,and rows without a ...
2
votes
1answer
129 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 ...
0
votes
2answers
129 views

mySQL Optimization Suggestions

I'm trying to optimize our mySQL configuration for our large Magento website. The reason I believe that mySQL needs to be configured further is because New Relic has shown that our SELECT queries are ...
1
vote
0answers
22 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
155 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
1answer
96 views

inserting large quantity of data into mysql table

I need to insert about 400 000 000 rows of test data into the InnoDB table. Table is quite simple: CREATE TABLE IF NOT EXISTS `tgt` ( `id_1` int(10) unsigned NOT NULL, `id_2` int(10) unsigned NOT ...
-5
votes
1answer
121 views

Recommendation for mysql please :MySQLTuner [closed]

>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options ...
1
vote
0answers
37 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; ...
0
votes
0answers
93 views

How to improve performance of large table in AWS MySQL RDS instance?

I am unfortunate enough to have a somewhat large, unoptimized table: +-------+--------+-------+------------+---------+ | rows | data | idx | total_size | idxfrac | ...
0
votes
1answer
76 views

Optimizing GROUP BY Query

I'm running the following query: SELECT `type`, MAX(`time`) AS `last_updated` FROM `stocks` GROUP BY `type` On the following table: CREATE TABLE `stocks` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
110 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 ...
5
votes
0answers
97 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
5
votes
1answer
339 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
1
vote
3answers
236 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?
5
votes
1answer
182 views

New query slower even if it has less logical reads than the old one

I have a query in a stored procedure that must list all dates in a range and JOIN on it date from a table, if it exists for that day. The procedure was created before I was employed as the DBA, so I ...
0
votes
2answers
43 views

Sorting of results is slow some times but fast at other times

I have a mysql query that is joining a few tables and then uses order by field for sorting. It sometimes runs quite fast at other times it is slow. While it was slow i profiled the query to find that ...