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 (1)

0
votes
1answer
63 views

Optimal way of loading, registering, indexing and querying a huge XMLDB

I've got 35 million highly structured XML documents, with a total size of 500 GB. An XSD is available for this data, and the end goal is to transform the data into an RDB. All fields can be considered ...
0
votes
2answers
507 views

How to break table into two without losing performance?

According to http://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
1
vote
1answer
1k views

Multi-Column Full Text Search Going Very Slow

I have a full text index on a table articles for the columns content, title and keywords When doing the search on each of the columns, like so select count(1) from articles where ...
0
votes
2answers
202 views

Best way to store few rows of data every minute without creating 1440 columns a day?

I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values. What is the best way to keep save these ...
1
vote
1answer
141 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
87 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
96 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 ...
6
votes
2answers
6k 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
132 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
89 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 ...
3
votes
2answers
147 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
46 views

MySQL: What is SQL profiler, does it different for MySQL?

Whenever SQL profiling comes in mind, focus goes to two below sections: GUI tools like - MONyog, Neor Profile SQL set profiling =1; Execute Query; show profiles; show profile; I am really confuse ...
1
vote
3answers
163 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
399 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 ...
1
vote
2answers
176 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
80 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
1k 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
543 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
0answers
18 views

Get tp.address_from and tp.address_to from travel_point table

I'm building a view for get tp.address_from and tp.address_to but I can't get this works. This is the query I'm working on: SELECT `tp`.`address_from` AS `address_from`, `tp`.`address_to` AS ...
0
votes
1answer
88 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
0
votes
2answers
198 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 ...
1
vote
1answer
315 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
949 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
375 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
91 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
385 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
113 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
38 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
123 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
568 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
1answer
242 views

MariaDB 5.5 vs MySQL 5.7 query optimizers

I have a query that optimizes differently in MariaDB 5.5.37 and MySQL 5.0 and MySQL 5.7. When poorly optimized it takes about 400 seconds to run, but on MySQL 5.7 which optimizes it differently, it ...
0
votes
1answer
197 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
138 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 ...
4
votes
1answer
317 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
45 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: ...
2
votes
1answer
211 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
144 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
130 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
257 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
90 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
109 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 ...
5
votes
3answers
154 views

Find the user with closest taste of mine

I trying to write in SQL the following requirement: Each user can rate as much films as they like. (1= I don't like, 5=I love it) And the system should provide to the current user a list of films ...
2
votes
1answer
963 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 ...
1
vote
0answers
40 views

Need help in optimizing a Slow Query

I am using userpoints module to provide points for different actions that they do on my site. mysql> desc userpoints; +-------------+---------+------+-----+---------+----------------+ | Field ...
2
votes
2answers
443 views

Optimize a query on two big tables

I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
0
votes
2answers
2k 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
61 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
3answers
844 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 ...
2
votes
1answer
364 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 ...
1
vote
0answers
111 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; ...