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

5
votes
0answers
134 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 ...
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
0answers
517 views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
2
votes
0answers
157 views

How to give permissions to 90+ SQL Servers without tweaking in each of them?

There are over 90+ MS SQL Servers (SQL Server 2005, 2008 and 2012). All in the same Windows domain (but physically all over the world). How to give a domain user (administrative, or other) ...
2
votes
0answers
466 views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
2
votes
0answers
77 views

Optimizations for new SQL Server 2000 database that will act as email queue AND API database

Due to limitations put on me I've got to put my outgoing Email Queue in the same database as my customer information. What are some optimizations I can make, specifically to the Email portion that ...
1
vote
0answers
53 views

Optimizing SELECT queries

I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I ...
1
vote
0answers
255 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
65 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
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
0answers
66 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
0answers
561 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
0answers
84 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
0answers
88 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
137 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
140 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
670 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
714 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
0answers
366 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
0answers
941 views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
0
votes
0answers
44 views

Slow MySQL Query when Ordering

This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and ...
0
votes
0answers
45 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
0
votes
0answers
45 views

Configuration help my.cnf Mysql

Is the following my.cnf file OK for a dedicated server with 56GB of RAM (on ubuntu server and 8 processors)? I'm running just one website on the server and there is a high demand on my cpu from ...
0
votes
0answers
28 views

Oracle estimate cardinality on partitioned table

How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join? For example, if I have a query like this select * from ...
0
votes
0answers
20 views

is there any limit of MySQL status variable value?

As we know MySQL status variable is a counter variable since the service start. MySQL service in my DB server restart every week (on sunday). Below is my observation since last few week: Suddenly ...
0
votes
0answers
18 views

Joining 2 big tables turn into a server down problem

Recently I was using a database (MySQL with Workbench) in the company that I work for (I'm a business analyst, not a DBA), and wrongly I created a query joining 2 tables that basically are the same. ...
0
votes
0answers
49 views

Explain plan shows a table not in the query

In trying to optimize a 2 minute query against an SAP R3 Oracle database, I examined the Explain Plan in Toad and found that most of the query's time is spent on a table that isn't even in the query. ...
0
votes
0answers
26 views

Optimised way to search over 2 milllion poi data in mysql

We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4 select *, ( ...
0
votes
0answers
24 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
47 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 ...
0
votes
0answers
95 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
119 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 ...
0
votes
0answers
197 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
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
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 ...
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, ...
0
votes
0answers
38 views

MySQL: Large data slow reads

I have very large table with 17,044,833 Rows and 6.4 GB in size. I am running the simple query below and it takes like 5 seconds. Any ideas what optimizations can I do to improve the speed of this ...
0
votes
0answers
84 views

Opimizing Mysql

Below is a Snapshot of my Server info. And the results of mysqltuner.pl Some help with commands would be appreciated. I'm new to this and have gotten pretty far, but I'm having issue with Mysql ...
0
votes
0answers
61 views

Why is adding a FK constraint slow in MySQL?

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand. CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY `pk_A` ...
0
votes
0answers
30 views

Optimization for a dynamic M2M value field

I'm using a Configurable Product methodology in my system. Everything works great, but I have one thing I think that might strongly impact the performance in the future. This is a dynamic linking ...
0
votes
0answers
67 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
0
votes
0answers
98 views

expdp taking so much time

I'm doing a full export using the expdp tool. I'm using 4 dump files and parallel parameter with 4 value. The export size calculus shows 30Gb but it takes almost 4 hours for 4 Gb. I'm pretty confused ...
0
votes
0answers
32 views

Which of these two queries is better - can be optimized more?

With regards to the usage of the IF statement, which is better to use? Query #1: SELECT whatever FROM auctions AS a LEFT JOIN ............... WHERE ............... ORDER BY ...
0
votes
0answers
93 views

How do I perform a batch update on the first n matching rows?

I need to perform a large number of updates at the same time. Each update needs to only affect up to a specific number of rows even though the WHERE clause might match to many more rows. So far, I'm ...
0
votes
0answers
168 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
0
votes
0answers
77 views

Is this a normal query time for a left outer join type?

I have Employee Table - 80K records about 100 fields I have an app table - 8K records and 30 fields Then a subset of employee table also stored as table that has our divisions subset - call this ...
0
votes
0answers
203 views

how I can set tmp_table_size for 16GB long table?

I am posting the same question as I posted in SO, but somebody advice me to post it here. I have a 16GB big table with 8 partition. And RAM 4 GB. I am running a query - SELECT ...
0
votes
0answers
46 views

Tips for improving my particular batch processing

I'm working with mail data from the USPS, which contains 10 related tables to make up a single mailing, as well as a single table containing all of the scans that each mail piece may receive. Once per ...
0
votes
0answers
70 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?