In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

learn more… | top users | synonyms

2
votes
2answers
2k views

Optimizing mysql queries running on 50 million rows

Is there any technique/advice in order to optimize the performance of the queries below? According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, ...
2
votes
2answers
382 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
876 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
1k 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
2k 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 ...
2
votes
1answer
635 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
1k 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
284 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
105 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
172 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
2answers
71 views

How can one optimize a query involving a sort and a LIKE?

Here is the query: EXPLAIN ANALYZE SELECT * FROM "Users" AS "User" WHERE "User"."name" LIKE 'garr%' AND "User"."id" NOT IN (2449214) AND "User"."hellbanned" IS NULL AND ...
2
votes
0answers
73 views

Why is the CTE much worse than inline subqueries

Im trying to better understand how the query planner work in postgresql. I have this query: select id from users where id <> 2 and gender = (select gender from users where id = 2) ...
2
votes
1answer
67 views

What is the best storing device for DB transaction log?

I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 ...
2
votes
2answers
2k views

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): ...
2
votes
0answers
44 views

Efficiently joining two irregular series representing changes

(This is a PostgreSQL-specific question, using version 9.1, but could be upgraded to 9.3.) I have a number of tables that represent a series of changes. These tables all have the same structure: ...
2
votes
1answer
324 views

Optimize table and query, aggregate by date or date-hour

I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows: CREATE TABLE sensor_data ( ...
2
votes
0answers
635 views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
2
votes
0answers
160 views

How to give permissions to 90+ SQL Servers without tweaking in each of them?

There are over 90+ MS SQL Servers (SQL Server 2005, 2008 and 2012). All in the same Windows domain (but physically all over the world). How to give a domain user (administrative, or other) ...
2
votes
0answers
164 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
2
votes
2answers
152 views

Queryplan changes depending on filter values

I created a clustered index on a table expecting it to make the queries with ranges perform better, but, different values in the where clause can produce differente query plans, one uses the clustered ...
2
votes
0answers
555 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
1k 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
583 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
0answers
78 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
873 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
241 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
2answers
981 views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
1
vote
2answers
85 views

Simple query is slow on 4M-rows table

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to ...
1
vote
2answers
644 views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
1
vote
2answers
142 views

Index optimization for table that is truncated every day

I have a bunch of tables in SQL Server 2012. These tables are fed by other tables. Every day there are routines that truncate (fully delete) them and run queries whose result are inserted into them. ...
1
vote
2answers
194 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
1
vote
2answers
81 views

SQL Server 2000 optimzation job hasn't run for a while

Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database. We turned it ...
1
vote
3answers
143 views

One or several tables for attributes

I just spent some time discussing two alternative DB designs with a coworker and I'm not convinced. Neither of us is strictly DBA so we may be missing something. The overall goal is to create the ...
1
vote
1answer
4k views

What is wrong with table_cache hit rate?

In my.cnf I have: table_cache = 524288 open_files_limit = 65535 Both are at max allowed value for mysql config. Both are less than max open file limit: # cat ...
1
vote
2answers
210 views

Prune unused joins

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case. I have a bunch of tables with the following structure: ...
1
vote
2answers
92 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
129 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
272 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
3answers
192 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
1answer
798 views

How can I speed up a Postgres query containing lots of Joins with an ILIKE condition

I have an odd problem which I really don't understand. Simply put I have a join containing 4 table joins, which I believe all have appropriate indexes, but which takes a ridiculous amount of time to ...
1
vote
2answers
59 views

What does enabling containment on server incur?

Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step": "Benefits and limitations of contained databases As with most features, there are benefits and ...
1
vote
2answers
198 views

How to avoid timeouts during big DML operations

I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again. The problem I have is that this runs for about 4 hours ...
1
vote
2answers
472 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
600 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
239 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
78 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
2answers
108 views

How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

I am going through the execution plan of a tsql query. There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index ...
1
vote
1answer
304 views

Multi-Column Full Text Search Going Very Slow

I have a full text index on a table articles for the columns content, title and keywords When doing the search on each of the columns, like so select count(1) from articles where ...
1
vote
1answer
61 views

How to optimize multiple ORDER BYs?

I'm getting sluggish performance on a table with ~300,000 rows and B-Trees on each column. This is for a dynamic pagination page where the query is constructed on demand, and the application caches ...
1
vote
1answer
207 views

Can I rely on this SQL shortcut to save a query?

Consider a table with a status field that holds one of two values: | status | set('active','default') | YES | | active | I need to know how many records are in the table, and additionally how ...