In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.

learn more… | top users | synonyms

2
votes
1answer
234 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
516 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
157 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
1answer
139 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
466 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
497 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
77 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
788 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
236 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
1answer
2k views

Why isn't oracle using an index for distinct query ?

This is the scenario SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true) PL/SQL procedure successfully completed. SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
1
vote
2answers
308 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
703 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
101 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
3answers
781 views

Is there any command to optimize all MySQL tables one by one automatically?

I execute below command line statement to optimize a table: optimize table tablename; Is there any command or statement which can optimize all the tables one by one of the selected database?
1
vote
2answers
161 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
73 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
136 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
2answers
183 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
91 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
127 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
262 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
189 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
565 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
57 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
180 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
1answer
3k 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
442 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
504 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
222 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
98 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
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
204 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 ...
1
vote
1answer
75 views

DROP TABLE on a huge InnoDB table

I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query? processor: Intel® ...
1
vote
1answer
82 views

How is the number of records on a select determined as high or low by optimizer?

What is the threshold between using the index and doing an direct access to the database? I assume this decision is related to the number of index access (and subsequent access to the tables). If this ...
1
vote
3answers
721 views

GUID stored in a varchar field

I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by ...
1
vote
2answers
63 views

Need some support on MySQL Query

I have a query SELECT leads.id, sfav.id is_favorite FROM leads INNER JOIN teams tst ON tst.team_set_id = leads.team_set_id INNER JOIN members members ON tst.team_id = ...
1
vote
2answers
753 views

boolean purpose column char(0) or tinyint(1)

For Boolean purpose column which one is better: nullable char(0) or tinyint(1). I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said: "If ...
1
vote
1answer
826 views

Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables. But before doing so, I would (of course) like to know if ...
1
vote
2answers
222 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
2answers
171 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
138 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
5k 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
1answer
3k 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 ...
1
vote
1answer
115 views

MongoDB performance problem

I have a MongoDB for storing result like this {_id, type, imei, lat, lng, spd, dir, time} {_id, type, imei, lac, cid, time} If type is f first one is used and if it is l second one is used. Here ...
1
vote
1answer
56 views

optimize the query with big and low cardinality

I have big enough dataset and few joins to get them all with help next query: SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER ...
1
vote
1answer
75 views

Postgres: my “data/backup” directory is way too large

In my postgres 9.3 data directory, there exists a subdir called "backup" that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup? ...
1
vote
1answer
154 views

inserting large quantity of data into mysql table

I need to insert about 400 000 000 rows of test data into the InnoDB table. Table is quite simple: CREATE TABLE IF NOT EXISTS `tgt` ( `id_1` int(10) unsigned NOT NULL, `id_2` int(10) unsigned NOT ...
1
vote
1answer
92 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...