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

0
votes
1answer
591 views

Why do aggregate functions alter the way my rows are fetched?

Running this query SELECT s0.id, s0.symbol, YEARWEEK(p0.datetime) AS label, p0.id AS point_id, p0.datetime, KG_FIRST(p0.open, p0.datetime) AS open, MAX(p0.high) AS high, ...
6
votes
2answers
717 views

What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?

My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month ...
2
votes
1answer
1k views

How to speed up mysql query?

I have query : SELECT `school`.`ARCO_name`, `student-`.`ClassSize_7`, `student-`.`ClassSize_8`, `degree_o`.`degree_code`, `accredit`.`full_faculty_3`, ...
2
votes
0answers
1k 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 ...
3
votes
2answers
10k views

forcing Oracle to use hash join for a subquery

I have a query that looks like SELECT * FROM table0 WHERE id IN (SELECT id FROM table1 JOIN table2) Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and ...
0
votes
0answers
125 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 ...
4
votes
2answers
480 views

Can I optimize for the zIIP processor?

Is it possible to change SQL in a z/OS mainframe COBOL application so that it becomes eligible to be directed to the IBM System z Integrated Information Processor (zIIP)?
2
votes
1answer
1k views

Why does MySQL only sometimes use my index for a range query?

I have a table of IP address ranges and their country, here is the structure: CREATE TABLE `geoIP` ( `startBlock` int(11) unsigned NOT NULL DEFAULT '0', `endBlock` int(11) unsigned NOT NULL ...
3
votes
3answers
593 views

This query is choking the server. How can I improve it?

Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof! The MySQL Processes list shows that as the number of requests ...
5
votes
2answers
566 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
3
votes
4answers
1k views

Should we add extra 5 columns or build a separate table?

Currently we have a table with 35 fields in it. Now, we got a requirement to add 5 extra columns but these columns will not be fill always. So, I was thinking just add one column and have that id as ...
2
votes
2answers
3k views

How to improve MySQL Server Performance..??

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers. Now my question is where to start from like we will need to find out many things as 1.Find the ...
1
vote
1answer
132 views

Help Optimizing Query

For the following table structure in MySQL 5.1.49: CREATE TABLE `leads` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `institution_id` int(10) unsigned NOT NULL, `lender_id` int(10) unsigned ...
4
votes
3answers
10k views

I've got 16GB of ram, how should I configure MySQL Server?

I've got a dedicated production server with multiple cpus and 16GB of ram. I would like to know how can I configure it better to take advantage of the server resources for better performance because ...
1
vote
2answers
331 views

Explicitly update a table after sp_tableoption “text in row” advices wanted

I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set ...
3
votes
1answer
2k views

Understanding optimisation of complex SELECT queries

I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table. I want records that match ((criterion 1 or criterion ...
2
votes
1answer
287 views

datetime index skipped for same query different params

I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575) Any idea why it skips ...
0
votes
3answers
196 views

Dog slow query, needs optimizing, please help

I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is ...
2
votes
1answer
259 views

Changing to InnoDB increases iostat

I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat. But how can it increase ...
1
vote
2answers
285 views

Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

I'm wondering if this statement can cause real overhead if many concurrent users send the same request to mySQL InnoDB storage engine. Let's say I have a table that has the user_name, and someone want ...
2
votes
2answers
106 views

Performing a “Spread Negitive” over a dataset

I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
2
votes
1answer
761 views

Poor MySQL for Drupal on Shared Hosting

My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly. Here are some of the flagged issues that I can see... In ...
3
votes
1answer
229 views

Optimizing queries against a set of tables that contain partitioned data

I have a third party program that creates and populates a series of tables to hold some historical data: data_33 -- contains data for 12/01/11 - 12/05/11 data_34 -- contains data for 12/05/11 - ...
4
votes
1answer
253 views

MySQL: A better way to write this query?

We are using the following query in our system: SELECT f.type AS type, COUNT(i.id) AS submissions, SUM((CASE (e.oiPost + e.oiPhone + e.oiEmail) WHEN 0 THEN 0 WHEN ...
3
votes
3answers
136 views

Optimisation Newbie: How much of a 'sin' is redundancy?

I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end. I have 2 SQL tables, as such: VOTES ...
5
votes
2answers
4k views

REBUILD - Clustered Index, TABLE, or both?

I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here. I have a very large table that we had to add a column to. The clustered index is ...
1
vote
2answers
141 views

How to restructure this slow query

For the following query: SELECT l.* FROM `leads` AS `l` INNER JOIN `users` AS `u` ON l.client_id = u.id WHERE (`l`.`lender_id` = 1) AND (l.claimed_date IS NULL) AND (`l`.`disabled` = 0) AND ...
26
votes
1answer
4k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
20
votes
2answers
24k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
2
votes
2answers
130 views

Why isn't the following query using the `always` index?

This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query: SELECT * FROM movies, ...
2
votes
2answers
1k views

Trying to optimize query for performance (MySQL)

So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
4
votes
1answer
721 views

Measuring the relative performance of queries given the assertion that “cost” isn't reliable

The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable. Source Does ...
1
vote
0answers
55 views

Link-table(s) vs Redundant Columns? [duplicate]

Possible Duplicate: Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server I have two tables, Users and People, both of which share a common ...
3
votes
2answers
918 views

Upper or Lower Case Optimisation on MS SQL Server

Does anyone know if MS SQL Server text queries are optimised for upper or lower case strings? I read somewhere the it was uppercase, but cant seem to find the reference to it again. Of course if ...
3
votes
1answer
136 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
12
votes
1answer
2k views

Monumental difference in execution time between queries when using RECOMPILE query hint

I have two almost identical queries running on the same SQL Server 2005 instance: The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
5
votes
4answers
2k views

MySQL subquery slows down drastically, but they work fine independently

Query 1: select distinct email from mybigtable where account_id=345 takes 0.1s Query 2: Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
4
votes
2answers
503 views

Huge database logging of event type rows and ways to optimize it

We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years. Usual use of these events is selecting some of them from the ...
3
votes
3answers
2k views

How to restructure this slow query containing subquery?

This query takes about 2-6 seconds to run depending which tag id(s) is/are selected: SELECT t2.id, t2.name, count(*) as kpl FROM (SELECT pt1.post_id FROM posts_tags pt1 WHERE pt1.tag_id ...
5
votes
2answers
2k views

Speed up INSERTs

I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is : Have no index on the table whatsoever Switch to simple logging (from full logging) ...
4
votes
2answers
2k views

Fastest validation query in PostgreSQL

What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource? I though that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we ...
8
votes
1answer
2k views

MySQL reads / writes per table

I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives. Is there a way ...
1
vote
2answers
6k views

What would be the fastest approach to bulk update this mysql table (Innodb)?

I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up? I am using following approach: mysql -uroot < query_file where query_file contains 1M ...
3
votes
2answers
5k views

Scheduled optimization of tables in MySQL InnoDB

What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
3
votes
2answers
2k views

How can I optimize performance on a MySQL ndbcluster?

So, this is what I have: A simple table classes__to_students: |class |student| --------------- Math Alice Math Bob Math Peter Math Anne Music Bob Music Chis Music ...
9
votes
2answers
635 views

How many Sproc Parameters is too many?

I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
1
vote
1answer
513 views

Optimization: Delete with Top and minimize the where clause date range

I have a delete statement that get run repetitive until all the data are removed. Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...) I am considering of ...
2
votes
1answer
954 views

MySQL Tuner OPTIMIZE and Defragmentation

I have just started using mysqltuner to help keep my mysql server fast and healthy. However, I am having a little trouble optimizing my tables. When I execute mysqltuner, it all works fine and returns ...
2
votes
2answers
1k views

In DB2, how can I disable NLJOIN?

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query. I've been looking for this for a while without any success. I ...
1
vote
1answer
1k views

At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run. I guess that it ...