In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
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 ...
5
votes
2answers
1k views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
1
vote
2answers
2k 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 ...
1
vote
1answer
1k views
16 Cores 12 GB RAM server MySql Configuration - my.cnf
The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
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 ...
9
votes
6answers
1k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
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 ...
3
votes
1answer
101 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
...
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 ...
1
vote
3answers
3k 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 ...
9
votes
4answers
781 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 ...
8
votes
2answers
493 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
6
votes
2answers
786 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, ...
8
votes
1answer
790 views
Why is Clustered Index Scan Number of Executions so high?
I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time.
The only difference between ...
2
votes
0answers
135 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
1
vote
2answers
138 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
4
votes
2answers
169 views
How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer?
The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages ...
4
votes
2answers
536 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
...
2
votes
1answer
61 views
Slow Queries Not Logging
I am attempting to enable slow query logging on our server in order to identify any queries that could use optimization. Sounds simple enough, however my file is not being written to. I get no errors ...
2
votes
1answer
308 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 ...
2
votes
1answer
279 views
MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob
I have a mysql insert on update query like so
insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars';
col1, col2 ...
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 ...
0
votes
2answers
152 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 ...
0
votes
1answer
123 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
111 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
