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

1
vote
1answer
894 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 ...
1
vote
1answer
1k views

Help optimizing MySQL slow query

i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort" query: EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
1
vote
0answers
19 views

Truncating Table Partitions Containing Data and Global Indexes

I have to truncate a partition (392,000 records) of a table with 20.000.000 records. At the moment, I'm making a truncate table and rebuilding indexes. But the table has 37 indexes and it takes 20 ...
1
vote
1answer
67 views

Inserting to table: Insert rows with Left Outer Join or Insert rows then Update?

I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row. Not every row in Orders will have a row in ...
1
vote
0answers
48 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
1
vote
1answer
26 views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
1
vote
2answers
195 views

Optimize a MySQL Database using MySQL Workbench

I actually have a few issues. First, I found this information about optimizing: MySQL OPTIMIZE all tables? The problem is, I don't seem to have a mysqlcheck program anywhere. I'm using MySQL ...
1
vote
0answers
67 views

Query optimization, SQL Server cache query, SQL Server execution query

I have 2 queries, the results of their execution is the same, but I don't know which of these is the best in terms of performance and why. Query #1: SELECT CASE WHEN ( EXISTS (SELECT 1 AS [C1] ...
1
vote
1answer
54 views

Do id columns help (in speed) select statements?

I have a table which contains data from aggregation software. The columns mostly are int columns, but three of them are string columns. It looks sort of like: userId, someData1, someData2, ...
1
vote
1answer
174 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
1
vote
0answers
69 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
0answers
94 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 ...
1
vote
0answers
102 views

Optimising Large SQL Server Tables [closed]

I have a large table with around 50 million rows and 15 columns. Whenever I read, I always need all columns so I can't split them. I have a clustered index on the table with 4 keys (ALL INT) and I ...
1
vote
0answers
107 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
1
vote
0answers
414 views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
1
vote
0answers
445 views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
1
vote
4answers
147 views

Normalization of DB

I have designed a database using MySQL likewise My question is what else i can do to normalize the DB, for now i am using InnoDB engine for the table to maintain the relation among different tables. ...
1
vote
0answers
256 views

Finding the bottleneck by analyzing Munin graphs

We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
1
vote
1answer
511 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
1
vote
2answers
541 views

Slow mysql fulltext query

I have a little problem with the performance of a mysql-query which uses a fulltext index. The following query SELECT Mention.id FROM mentions AS Mention WHERE (MATCH (`Mention`.`title_text`, ...
1
vote
2answers
94 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...
1
vote
0answers
799 views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
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
1answer
368 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 ...
0
votes
1answer
95 views

On which fields to index? Fields queried (WHERE) or fields returned (SELECT)

I have a large MySQL database table (~1 million rows and growing) on an AWS RDS Medium instance: mysql> describe clients; ...
0
votes
2answers
230 views

Is there a good reason to store big text data outside database?

I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb. This data is only used when loading single record (like article or blog ...
0
votes
3answers
104 views

Optimize extremely slow SQL query [closed]

SELECT j.job_id,j.last_updated,j.job_title,j.status, (SELECT COUNT(*) FROM candidates WHERE job_id=j.job_id) resumes_count, (SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=1) ...
0
votes
2answers
853 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 ...
0
votes
1answer
143 views

PostgreSQL FTS and Trigram-similarity Query Optimization

I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
0
votes
2answers
62 views

Filesort while using primary key for orderby

I have been breaking my head for the past two days now for this. CREATE TABLE `clients` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `guarantor_name` varchar(50) NOT NULL, ...
0
votes
2answers
541 views

How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table?

I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference. desc urllist; ...
0
votes
2answers
478 views

Use timestamp(or datetime) as part of primary key (or part of clustered index)

I use following query frequently: SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp I would like to optimize ...
0
votes
1answer
461 views

Why do aggregate functions alter the way my rows are fetched?

Running this query SELECT s0.id, s0.symbol, YEARWEEK(p0.datetime) AS label, p0.id AS point_id, p0.datetime, KG_FIRST(p0.open, p0.datetime) AS open, MAX(p0.high) AS high, ...
0
votes
1answer
22 views

MySQL Query Cache - what type of queue is used?

I am wondering what type of queue is MySQL Query Cache using. When there is new query and cache is full which query is deleted? Oldest one? One with fewer number of hits? Or maybe there is some ...
0
votes
1answer
176 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
0
votes
1answer
105 views

Optimize MySQL Self JOIN Query

I have a query which is running in 15+ seconds SELECT t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize` FROM `stocks` AS t1, `stocks` AS t2 WHERE ...
0
votes
1answer
183 views

How to measure a perfomance of SQL Server database? [closed]

I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics. So, I need to measure "performance" of the RDBMS ...
0
votes
2answers
169 views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
0
votes
1answer
147 views

How to profile a particularly slow or inefficient query

I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries. ...
0
votes
3answers
392 views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
0
votes
1answer
406 views

Large INSERTs performance optimization

I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query). The queries are send ...
0
votes
1answer
43 views

Misunderstanding statement in MySQL documentation

I can't understand this statement in Optimizing Data Size: Declare columns with identical information in different tables with identical data types, to speed up joins based on the corresponding ...
0
votes
1answer
247 views

Optimizing ORDER BY for simple MySQL query

I'm trying to optimize this really simple query, and it gives me grief for a day now :( Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
0
votes
1answer
195 views

Database schema design help needed

I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance. This application ...
0
votes
1answer
83 views

What index to add in case of join by two optional fields

I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional. ...
0
votes
1answer
3k views

Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?

I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation. Would it be useful to run -check with this option or does the -optimize already take care of that? I am only ...
0
votes
2answers
410 views

Slow query with straight_join for small result

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql. The query consists of different joins, especially the join to the mentioncache-table. If the ...
0
votes
3answers
187 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 ...
0
votes
2answers
32 views

What's the best way to translate a website

I have a website in Django using MySQL, and I have more than 4.000 entries that I want to translate. What's the best way to do it? Create a new database for every language? Create other table for ...
0
votes
1answer
58 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM ...