In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
2
votes
2answers
164 views
In MySQL, should I add an index even if the query that scans the table is only ran once a month?
Concretely, is it worth it to add an index to a query that scans the table of a mysql database,
even if that index would only be used once a month?
For example, having a users a table and then ...
5
votes
3answers
393 views
Does SQL server optimize or pre-parse stored procedures?
Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored ...
4
votes
4answers
584 views
Where to look for a bottleneck after elimitating CPU, memory, and disk
I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run ...
3
votes
2answers
163 views
What are the best Database Analytics Companies for analyzing several 1000s or Mio. of products,sum them together and analyze them for statistics?
We will get several thousands products in our database. The problem is that one product can have several different names e.g. "iPad 16GByte UMTS schwarz" or "iPad 16GB 3G black" and so on. But they ...
1
vote
1answer
94 views
Should I Avoid Repeated References to Aggregate Columns in a View?
So I'm wanting to summarize the balance of the accounts in my business application. My (simplified for our purposes) data model looks like:
--Main account table that holds the single accounts
create ...
9
votes
4answers
5k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
1
vote
1answer
130 views
Optimising a sql correlated subquery statement
I am updating a column on tableA with data from tableB like this:
update tableA
set bKey = select bKey from tableB B where B.colB = tableA.colB
bKey has a unique index on tableB.
Which is the ...
3
votes
3answers
505 views
Improving slow queries. Slow query log attached
Below is the Slow Query Log when my .NET application was running. Please let me know how to improve the performance of the queries:
TCP Port: 3306, Named Pipe: (null)
Time Id Command ...
5
votes
5answers
400 views
Does SQL Sentry Plan Explorer work?
Does SQL Sentry Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about?
It looks like it shows the hot path in color commpared to SSMS's ...
6
votes
2answers
787 views
How do Application Like Yelp Retrieve distance information from data base efficiently
For example, say I have a table:
Business(BusinessID, Lattitude, Longitude)
All are indexed of course. Also there are 1 million records
Say I want to find businesses closest to 106,5, for example, ...
3
votes
3answers
853 views
MySQL Query Optimization : Indexing and Pagination
I'm adding pagination to my 'latest news' php script and running in to an issue.
SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit
I ...
4
votes
1answer
2k views
my.cnf values not applying - Optimizing MySQL in CentOS LAMP
I have checked for other occurences of my.cnf and I have found only one occurrence of /etc/my.cnf.
Then, I went ahead to fix in my values, e.g. query_cache_size etc.
After saving and literally ...
1
vote
3answers
164 views
Query optimization
In another question they told me that the speed of a DBMS largely depends on the optimizations of your queries. Does anybody know good documents about this? At school I only learned the basics.
For ...
3
votes
2answers
258 views
How do I calculate working business minutes?
I'm working on business inteligence reports and one of my task is to create reports for time tracking.
I have to report on business minutes (10 hours per day) within business week (monday to ...
5
votes
1answer
2k views
Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server
I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server.
I checked this once and found that after this is run, ...
4
votes
1answer
533 views
Speeding SQL queries for a large(?) WordPress database?
I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here).
The plugin is great ...
6
votes
2answers
215 views
How should I Optimize storage for this table?
I'm using MySql to store a basic table of this format:
id int(11) //Auto-Incrementing ID
data varchar(5120) //Random input data, compressed by a program, not mysql.
...
8
votes
4answers
922 views
How can I optimize this MySQL query further?
I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
4
votes
4answers
255 views
Do cross-joins get optimised by the client library?
Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ.
I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
3
votes
2answers
341 views
What are the key points to avoid table locks?
MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software ...
4
votes
2answers
585 views
SQL Server Wildcardless LIKE Optimization
In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query.
If I was to have all queries generated as like clauses, such as:
...
6
votes
3answers
3k views
Avoiding temporary tables while sorting by a column in a different table
So I have this MySQL InnoDB query here:
SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt
FROM stories s
LEFT JOIN votes v ON s.vid = v.vid
WHERE s.lv = 1
AND s.status = 1
GROUP BY s.vid
...
3
votes
2answers
906 views
DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle
In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table ...
0
votes
2answers
506 views
How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?
SQL Server DELETE only marks as DELETED though leaves as data in the data pages.
Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record ...
9
votes
5answers
1k views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
0
votes
0answers
456 views
PHp Mysql OPtimization using slow-log-query [closed]
Hi there i'm trying to fix a speed connection problem of my app located at:
sandbox.promls.net
try get access by:
david@vivarealty.com.mx
xxxxxx
you could se the sql statements on there
4
votes
2answers
415 views
Fetch first 9 records and make one more record which will be as “Others” in MySQL
I am fetching records from one table with count of one field with other field as name.
I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as ...
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
6
votes
2answers
111 views
Is there a good “rule of thumb” for translating EXPLAIN cost to (wall-clock) runtime?
From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have ...
2
votes
2answers
131 views
How to create multiple entries on index based on the fields of one row?
I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL.
The problem arises when you have fields working as tags or similar concept. Ex.: ...
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
