In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
1
vote
1answer
456 views
Slow complex query with group/order
I have a problem with a very important query in my application.
It is very slow when adding group and order to it.
So the full query is:
SELECT
m.id,
m.title,
m.title_text,
...
1
vote
2answers
335 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`, ...
1
vote
1answer
72 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
41 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
217 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
90 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
74 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
94 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 ...
1
vote
1answer
560 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
553 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
81 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
271 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
117 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
511 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
104 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
...
5
votes
2answers
1k 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 ...
9
votes
4answers
911 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
181 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
309 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
200 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
123 views
Optimizing table and choosing storage engine
Having the follow MyISAM table:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
0answers
69 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
124 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 ...
1
vote
1answer
630 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 ...
1
vote
3answers
670 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
122 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
584 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
83 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
303 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
95 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
317 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
940 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
77 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 ...
2
votes
2answers
1k 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
841 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
107 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
92 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
316 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
312 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
656 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
540 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 ...
2
votes
2answers
2k 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
91 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
258 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
339 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
4answers
392 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
264 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
458 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
1k 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
102 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 ...
