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
0answers
102 views

can this mysql query be optimized further?

I have this query select counterparty_id, max(id) max_id from client_administrations where counterparty_type = 1 and effective_on <= '2013-10-22' group by counterparty_id When I ...
0
votes
2answers
3k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
0answers
189 views

Vertica pagination

I have a query that gives the total number of rows to use in a pagination: select count(1) as total_rows from (subquery with some sum() and 2 inner joins) as as total_sums ; when I run only ...
0
votes
1answer
56 views

Join on with modified ON

I am trying to create table with summary of few other tables. All solutions i came up with are very slow and clearly not optimal. Initially I tried with multitude of subquerries but that was very ...
0
votes
0answers
59 views

Creating a dedicated HD parition at beginning of disk for data files, benefits and draw backs?

I am thinking of creating a dedicated partition on the harddisk for the mysql database data files in /var/lib/mysql , while considering those 2 points: a dedicated partition will avoid any file ...
0
votes
2answers
114 views

Help creating this query

I'd like to integrate the following query: SELECT parent.id , COUNT(child.id) AS child_count FROM messages parent INNER JOIN messages child ON child.parent_id = parent.id ...
0
votes
0answers
1k views

Using something better than UNION for combining multiple queries

I have one big query which is based on 12 smaller queries that I have put together using UNION. A relative simple example of my query: (SELECT 'type1' AS 'type', fieldOne AS someField, ...
0
votes
0answers
129 views

InnoDB tuning with 1G of ram limit

I am trying to calculate variable moving averages crossover with variable dates. That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
0
votes
2answers
7k views

MySQL tuning (my.cnf) for very large tables and ad hoc queries [duplicate]

We are an email marketing company and recently switched our setup to MySQL. We need to configure mysql (my.cnf) for extreme performance. We have tried to configure my.cnf but heavy queries can get ...
0
votes
0answers
36 views

optimize this query [duplicate]

Possible Duplicate: Slow query performance My database contains 20,000 rows and the following query is slow. Please help me to speed this up. SELECT r.report_id, ...
0
votes
1answer
688 views

Optimising MySQL query with lots of self-joins

I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved. In some cases it can be said that one entity is a constituent of a ...
0
votes
0answers
47 views

mysql select query optimization [duplicate]

Possible Duplicate: mysql select query optimization I have two tables, testa and testb. CREATE TABLE `testa` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, ...
0
votes
0answers
73 views

Load time implications of storing data in multiple rows vs serialized data

I am currently storing data in fields as shown below. Each of these pricing fields has two database rows associated with it, and there is up to 24 of these tables for a house. This therefore means ...
0
votes
0answers
133 views

Optimization of an Oracle query

I have a view which is accessed via DBLink in Oracle. This view is made up of different tables. Now we have to execute queries on this view. Example: Select * from Viewname@dblink where ...
0
votes
1answer
468 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
-1
votes
2answers
55 views

Optimize query or split in two queries and process result separately?

I have this DB schema: As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table: SELECT ...
-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 ...
-1
votes
1answer
94 views

Optimization of a select statement

I'm using MySQL and have a table user_data like this: user_id int(10) unsigned reg_date int(10) unsigned carrier char(1) The reg_data is the unix timestamp of the ...
-2
votes
1answer
31 views

PostgreSQL - what do tms and mt mean in a SELECT query? [closed]

I'm trying to see if I can optimize the following query which was written by my predecessor: SELECT tms.*, 0 as kyori , (power( (139.745069 - ...
-2
votes
1answer
260 views

message table schema - body TEXT or varchar [closed]

I want to make a small social network. For messaging body which one is more suitable? VARCHAR or TEXT? if VARCHAR how long is efficient?
-2
votes
0answers
301 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), ...
-3
votes
2answers
1k views

How to improve TempDb on RAMDisk showing performance on par with physical hard drive [closed]

Given 2 SQL Server Instances where the second instance is configured with a RAMDisk for tempdb and the following test case. -- Create source data select top(1000000) * /* ~10 cols */ into #t1 from ...
-3
votes
0answers
36 views

Doubt :: my sql performance tuning [duplicate]

Possible Duplicate: MySQL performance tuning I would like to optimize my MySQL DB and I identified some important points as follows: enabling query cache, using memory storage (I know ...