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

1
vote
2answers
799 views

Slow mysql fulltext query

I have a little problem with the performance of a mysql-query which uses a fulltext index. The following query SELECT Mention.id FROM mentions AS Mention WHERE (MATCH (`Mention`.`title_text`, ...
2
votes
1answer
96 views

Optimising query on view that merges similar tables with a clear discriminator

Using PostgreSQL 8.4, I have a number of tables that have a very similar structure, but that belong to different categories: CREATE TABLE table_a ( id SERIAL PRIMARY KEY, event_time TIMESTAMP ...
2
votes
1answer
44 views

Convert Log table days in a state

This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following... Input Table Date Item Moved to Box Oct-1 ...
4
votes
3answers
252 views

How does the T-SQL optimizer handle “and false”

In SQL SERVER 2008 I'm thinking about a query such as: SELECT ... FROM ... WHERE ... AND @a=1 UNION SELECT ... FROM ... WHERE ... AND @a=2 Given that @a is clearly the same value, and therefore ...
1
vote
1answer
94 views

What is the best relationship model for high performance?

I want to model a relationship between Customer and Transaction. However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
1
vote
2answers
89 views

Efficiency for contact-contact relationships schema

I'm migrating data from a schema where people and organisations are separate tables to one where people and organisations are all treated as contacts (they share a lot in common). Currently, the ...
2
votes
1answer
125 views

How to know what to investigate and how for a VERY slow stored procedure

I have inherited a large and slow stored procedure and it's giving me a nightmare: I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck. I have ...
2
votes
1answer
2k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
1
vote
1answer
1k views

Planning simple CMS: How much relation?

I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
0
votes
1answer
90 views

Why searching with $all in Mongodb is FAR SLOWER than the EQUIVALENT search?

$all with only one item is equivalent with normal search right? Here is the log without $all (I used 4700 test cases with 30 thread): Thu Sep 20 17:27:05 [conn11] query newisikotagte15.tablebusiness ...
5
votes
1answer
559 views

Query performance while using sqlcmd [duplicate]

Possible Duplicate: Running queries with SQLCMD vs. Running queries with SSMS I have a number of scripts that query the data from different tables and consequently update them. I have ...
1
vote
1answer
215 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);
2
votes
1answer
1k views

LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows

I have a table named Product and a table named ProductPropertiesCountry. Product is the definition of a product (description, price, productId, etc) ProductPropertiesCountry contains restrictions on ...
3
votes
1answer
128 views

Analysing A Query Plan

Why is the following query slow? select count(*) from [dbo].[mt_dispatch_link] , [dbo].[_mt_dispatch] [_mt_dispatch] where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id ...
7
votes
2answers
4k views

Why is this query not using my nonclustered index, and how can I make it?

As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default. This query runs in about 2.5 seconds: SELECT TOP 1000 * FROM ...
11
votes
4answers
2k views

Is it possible to increase query performance on a narrow table with millions of rows?

I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
4
votes
1answer
240 views

Optimizing MySQL -

I've been researching how to optimize MySQL a bit, but I still have a few questions. MySQL Primer Results http://pastie.org/private/lzjukl8wacxfjbjhge6vw Based on this, the first problem seems to be ...
7
votes
2answers
403 views

Data Compression Through Base36 Encoding

I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type. I'm thinking about using Base36 encoding to accomplish this. Are ...
3
votes
3answers
603 views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
1
vote
1answer
153 views

Optimizing table and choosing storage engine

Having the follow MyISAM table: +----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
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 ...
4
votes
2answers
143 views

Optimizing my update sequence

Whenever a user updates his profile, we conduct a set of queries like so: DELETE FROM `user_likes` WHERE `ID` = $id INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name) Is ...
2
votes
1answer
2k views

Optimizing MySQL for Read-Only?

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
3
votes
3answers
2k views

How can I speed up “show columns” on MySQL?

My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying information_schema directly is even slower. ...
7
votes
2answers
165 views

Optimization problem: compound clustered keys, flag conditions and index-merge

Three tables: product: with columns: ( a, g, ...a_lot_more... ) a: PK, clustered g: bit-column main: with columns: ( c, f, a, b, ...a_lot_more... ) c: PK, clustered f: bit-column (a, b): UQ ...
4
votes
2answers
1k views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
1
vote
2answers
99 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...
2
votes
1answer
574 views

Inserting into three different tables, using foreign keys — 500000x each

We're working on refactoring many of our offline (python) scripts. I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field. We have a ...
3
votes
1answer
108 views

can sql generate a good plan for this procedure?

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
4
votes
1answer
626 views

MongoDB optimization on high data

I have a mongoDB collection with 42M registers with 1M new registers every day. We found a low performance on mongoDB querying. showing in mongostat the fields: qr|qw ar|aw with values of 300-500. ...
2
votes
1answer
2k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...
1
vote
1answer
78 views

How can I select data with one query?

I have the following tables: Table xwrh9_betty_dictionary_ids +----+----------+-------+-------------+---------------------+-------+-------+ | id | ordering | state | checked_out | checked_out_time ...
3
votes
2answers
2k views

How to setup mysql on tmpfs?

How to setup mysql on tmpfs ? I am reading a tutorial in french, where the author do it this way : 1.) Create a tmpfs directory : mkdir /var/tmpfs mount -t tmpfs -o size=1G tmpfs /var/tmpfs chown ...
3
votes
4answers
1k views

Optimising MySQL

As per this post, it looks like I'm having some serious issues with MySQL optimisation. Unfortunately, I have no idea where to start! I'm running a DirectAdmin-based CentOS dedicated server. It's an ...
1
vote
2answers
166 views

Is there a way to get slow-query-log-like profiling information on a single query?

Specifically, I'm looking for this information: # Query_time: 0.011922 Lock_time: 0.000088 Rows_sent: 107 Rows_examined: 663 Rows_affected: 0 Rows_read: 107 # Bytes_sent: 4152 Tmp_tables: 3 ...
3
votes
1answer
112 views

How can I find out which statistics are evaluated by the optimizer?

My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. ...
0
votes
1answer
538 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
664 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
884 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`, ...
1
vote
0answers
913 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
7k 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
114 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
411 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
552 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
448 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
784 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
127 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 ...
2
votes
3answers
8k 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 ...