Tagged Questions

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

0
votes
1answer
76 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
134 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
58 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
35 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
70 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
245 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
80 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
95 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 ...
3
votes
1answer
159 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
42 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
132 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
74 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
92 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
117 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
64 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
57 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
276 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
359 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
38 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
338 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
152 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
325 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
64 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
193 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
130 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
165 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
132 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
373 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
728 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
234 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
56 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 ...
1
vote
1answer
90 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
4
votes
1answer
116 views

sql indexing and performance

I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities. There are 8 columns in this ...
0
votes
1answer
56 views

How can I apply heuristics to optimise an expression tree?

I've converted the following SQL query: SELECT name FROM dog d, works_at w WHERE (d.owner_name = w.person_name) AND (w.kennal_name = "Breeders"); into the following expression tree: My main ...
2
votes
1answer
146 views

Clustered composite key high fragmentation and fill factor

I've recently made a table to hold the language preferences of my users as follow: CREATE TABLE [dbo].[systemUserLangPreference]( [systemUserID] [int] NOT NULL, [langID] [int] NOT NULL, ...
1
vote
2answers
100 views

What's the best way to translate a website [closed]

I have a website in Django using MySQL, and I have more than 4.000 entries that I want to translate. What's the best way to do it? Create a new database for every language? Create other table for ...
0
votes
0answers
68 views

explain plan ,joins and optimization

I have to join 6 tables in sql query and each of these tables have 1 join to each of there master tables . Hence i have defined 5 subqueries within a sql query but i am seeing a lot of performance ...
0
votes
2answers
74 views

Optimize query with indexes

The following query is looking for all towns that are between 5 and 10 kilometers from a hard-coded latitude/logitude (=another town actually). I have 37010 towns. I'm using Symfony 2 which "creates" ...
1
vote
1answer
556 views

How can I speed up a Postgres query containing lots of Joins with an ILIKE condition

I have an odd problem which I really don't understand. Simply put I have a join containing 4 table joins, which I believe all have appropriate indexes, but which takes a ridiculous amount of time to ...
1
vote
0answers
527 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 ...
2
votes
2answers
278 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 ...
0
votes
1answer
69 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM ...
0
votes
1answer
58 views

MySQL Query Cache - what type of queue is used?

I am wondering what type of queue is MySQL Query Cache using. When there is new query and cache is full which query is deleted? Oldest one? One with fewer number of hits? Or maybe there is some ...
-2
votes
0answers
156 views

How to make make my query run fast? [closed]

How to make this query run faster? Table contains over 2 million rows of data and takes around 30 to 120 seconds to process the query. SELECT INET_NTOA(blocks.startIpNum), ...
2
votes
2answers
1k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
0
votes
1answer
76 views

MYSQL Stock Database Optimization

I am downloading a bulk of stock data: Ticker symbol, company name, industry sector, Open, high, low, close I was wondering performance-wise if separating the ticker, company name, industry sector ...
0
votes
1answer
47 views

Design for hierarchical embedded database

The goal is to construct a hierarchical database of Objects having the following format: TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.] TypeB -> ObjectB [values, contains ...
0
votes
0answers
44 views

Optimizing PostgreSQL 9.0.2

For work we have a 8GB Linode, hosting both the Rails 3 App (Passenger, Apache) and Postgres. The app is a reading heavy app, about 75% reading, and 25% writing. (A conservative estimate would be ...
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 ...
0
votes
0answers
32 views

How can the performance of a query that has a condition on two fields of each row be improved?

I have an images table with approximately 5 million rows in it. For the purposes of this question lets say the table looks like this: CREATE TABLE `images` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...