In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
2
votes
1answer
86 views
Postgres: count(*) vs count(id)
I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).
My question is ...
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 ...
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 ...
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.: ...
2
votes
1answer
60 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
2answers
91 views
Optimizing index creation
One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns.
An example case would be:
table1
...
2
votes
1answer
597 views
MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?
I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
2
votes
1answer
631 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`,
...
2
votes
1answer
153 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 ...
2
votes
1answer
415 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 ...
2
votes
2answers
100 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, ...
2
votes
1answer
172 views
In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?
I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
2
votes
2answers
116 views
Schema design for privacy settings in MongoDB
I have a set of documents that can have various privacy settings around them:
They can be completed public (any registered user) can view them
They can be viewable only by people following you (this
...
2
votes
1answer
186 views
Oracle 10g using collection with sql
I have a stored procedure that takes collection of objects. (TABLE OF MyCustomType). Inside the procedure I'm trying to join this parameter with real tables.
For example, something like
create or ...
2
votes
1answer
462 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 ...
2
votes
1answer
294 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 ...
2
votes
1answer
837 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 ...
2
votes
1answer
302 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
176 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
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
2answers
110 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 ...
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
1answer
33 views
Optimize UNION query in MYSQL
I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, > 35 ...
2
votes
1answer
69 views
mysql: need help to optimize my query/table
I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
2
votes
0answers
133 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 ...
2
votes
1answer
223 views
Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields
I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).
Furthermore I'd like to show ...
2
votes
0answers
117 views
Best book on query optimization, rewriting, refactoring [closed]
I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient.
But in a more general sense, I need a book that explains ...
2
votes
2answers
75 views
How to optimize a log process in MySQL?
In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns ...
2
votes
3answers
442 views
MySQL optimization - year column grouping - using temporary table, filesort
I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
2
votes
0answers
67 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 ...
2
votes
1answer
487 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 ...
1
vote
3answers
179 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 ...
1
vote
3answers
581 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.
...
1
vote
2answers
386 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
2answers
73 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 ...
1
vote
1answer
101 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 ...
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 ...
1
vote
2answers
9k views
“Copying to tmp table” extremely slow
This is my example of query:
SELECT
nickname,
CASE class_id
WHEN 1 THEN 'Druid'
WHEN 2 THEN 'Necromancer'
WHEN 3 THEN 'Mage'
WHEN 4 THEN 'Priest'
WHEN 5 THEN ...
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 ...
1
vote
3answers
190 views
Good, Bad or Indifferent: WHERE 1=1
Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up ...
1
vote
2answers
259 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
1
vote
1answer
111 views
PostgreSQL 9.1 - Asynchroneous commits performance boost
During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the ...
1
vote
1answer
113 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);
1
vote
1answer
76 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 ...
1
vote
1answer
63 views
Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure
While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.
ie:
CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- ...
1
vote
2answers
142 views
XML Large DataBase is the best? [closed]
Is the best way to implement a large database is XML? Or is there a better way?
1 - reduce the time complexity
2 - Reduce the amount of traffic between the tables
And other factors ...
Which ...
1
vote
1answer
430 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
2answers
104 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 ...
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 ...
1
vote
2answers
330 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 ...
