In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
4
votes
2answers
243 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)?
3
votes
4answers
383 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 ...
2
votes
1answer
310 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 ...
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:
...
8
votes
2answers
494 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 ...
1
vote
2answers
141 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 ...
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 ...
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 ...
3
votes
1answer
258 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
178 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 ...
2
votes
1answer
154 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 ...
0
votes
3answers
171 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 ...
1
vote
2answers
143 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
77 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
420 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
187 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
169 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 ...
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 ...
3
votes
3answers
112 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
1k 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
113 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 ...
2
votes
2answers
101 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, ...
5
votes
2answers
548 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)
...
2
votes
2answers
481 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 ...
3
votes
1answer
216 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
54 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 ...
1
vote
2answers
393 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 ...
1
vote
1answer
542 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 ...
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 ...
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 ...
8
votes
1answer
666 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
981 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
233 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
606 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 ...
1
vote
2answers
337 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 ...
1
vote
1answer
270 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 ...
3
votes
2answers
605 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 ...
1
vote
1answer
132 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 ...
4
votes
1answer
752 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
2k 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 ...
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 ...
9
votes
2answers
223 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 ...
2
votes
1answer
493 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 ...
4
votes
1answer
788 views
How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:
FOR COLUMNS SIZE 5 some_column
and
FOR COLUMNS some_column SIZE 5
both will create a ...
0
votes
2answers
510 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 ...
6
votes
1answer
146 views
On-the-fly query/command modification
I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly ...
2
votes
2answers
111 views
Count() versus keeping a column with a total count [duplicate]
Possible Duplicate:
Storing vs calculating aggregate values
Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos ...
1
vote
3answers
181 views
PostgreSQL and query planner
I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...
3
votes
2answers
530 views
Website is too slow when number of records increases?
I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system ...
3
votes
1answer
1k views
Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?
We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM ...
