Tagged Questions

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

0
votes
2answers
49 views

How to use max function in such condition?

My query is select * from guestuser union select * from olduser ORDER BY userid DESC LIMIT 1; Is that correct query to find last userid from different table have different userid? can i change ...
0
votes
0answers
36 views

Slow MySQL Query when Ordering

This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and ...
0
votes
0answers
42 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
0
votes
1answer
53 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
1answer
35 views

How to manipulate join order of PostgreSQL optimizer?

I am working on table join order optimization. While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL? How can I update ...
0
votes
4answers
78 views

DB performance vs replication

I am working on a db optimization (planing for future project growth) and need some help. Currently, every table is using uniqueidentifier as PK (clustered index) and we have high index fragmentation ...
0
votes
1answer
31 views

MySQL optimize range duplicate query

I need to eliminate duplicates from a time sheet. I found this solution and have adapted it for my own needs: DROP TABLE IF EXISTS `activity`; CREATE TABLE IF NOT EXISTS `activity` ( `id` int(11) ...
0
votes
0answers
38 views

Configuration help my.cnf Mysql

Is the following my.cnf file OK for a dedicated server with 56GB of RAM (on ubuntu server and 8 processors)? I'm running just one website on the server and there is a high demand on my cpu from ...
1
vote
0answers
53 views

Optimizing SELECT queries

I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I ...
0
votes
0answers
25 views

How to optimize aggregation

I have a query which is sometimes taking too long to execute. I tried to optimize it in every possibile way (AFAIK). When I run this query repeatedly I get fast results. In production environment ...
3
votes
1answer
85 views

When is mariadb creating tmp tables?

I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
0
votes
0answers
27 views

Oracle estimate cardinality on partitioned table

How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join? For example, if I have a query like this select * from ...
0
votes
0answers
20 views

is there any limit of MySQL status variable value?

As we know MySQL status variable is a counter variable since the service start. MySQL service in my DB server restart every week (on sunday). Below is my observation since last few week: Suddenly ...
0
votes
1answer
11 views

How to Search a Database of Package Deals for Specific Items with the Fewest Unwanted Extra Items

Short Description I have a database of special offers that bundle several items together. I want to be able to search for a list of specific items and get a combination of bundles that includes all ...
0
votes
1answer
44 views

Help with optimising a sql query on two partitioned tables

I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate ...
0
votes
1answer
28 views

How to setup large database access/cache on LAMP environment? [closed]

My application is getting very slow I guess because of the ever growing database (30+ tables some with 50,000+ rows). Using CodeIgniter's model tier to call MySQL database, I optimized queries & ...
0
votes
1answer
70 views

Optimisation and Performance Mysql , Zend Framework 2 and Doctrine 2?

i'm facing some problem with performance in an application using zend framework2 and doctrine 2. i would like to optimis it but i don't know from where to start , i read some many questions/ answers ...
2
votes
1answer
54 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 ...
1
vote
1answer
19 views

Speed Up Index Creation in MySQL

In MySQL 5.5, I have a table which is recreated on a daily basis and for which the process of adding an index is taking about 50 minutes which I'm hoping to reduce. At the time when the index is ...
1
vote
1answer
36 views

Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?

I am running MySQL 5.5.37. I have a cronjob set to run every 6 hours to flush the query cache. I realized that if I don't do that, then the cache becomes fragmented and average query response 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 ...
0
votes
1answer
41 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
3
votes
1answer
74 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
0
votes
0answers
18 views

Joining 2 big tables turn into a server down problem

Recently I was using a database (MySQL with Workbench) in the company that I work for (I'm a business analyst, not a DBA), and wrongly I created a query joining 2 tables that basically are the same. ...
1
vote
2answers
100 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. ...
0
votes
0answers
49 views

Explain plan shows a table not in the query

In trying to optimize a 2 minute query against an SAP R3 Oracle database, I examined the Explain Plan in Toad and found that most of the query's time is spent on a table that isn't even in the query. ...
1
vote
1answer
104 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 ...
0
votes
0answers
25 views

Optimised way to search over 2 milllion poi data in mysql

We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4 select *, ( ...
0
votes
1answer
47 views

Optimal way of loading, registering, indexing and querying a huge XMLDB

I've got 35 million highly structured XML documents, with a total size of 500 GB. An XSD is available for this data, and the end goal is to transform the data into an RDB. All fields can be considered ...
0
votes
2answers
106 views

How to break table into two without losing performance?

According to http://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
0
votes
1answer
92 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 ...
0
votes
2answers
52 views

Best way to store few rows of data every minute without creating 1440 columns a day?

I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values. What is the best way to keep save these ...
1
vote
1answer
55 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
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 ...
0
votes
2answers
84 views

How can I make this query more efficient?

This is the query: SELECT races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at FROM races LEFT JOIN (SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON ...
1
vote
2answers
624 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): ...
3
votes
2answers
111 views

How to optimize this slow SQL query

I have a slow SQL query on my Drupal website and I don't know how to optimize it. Here is the query that Views produces: http://pastebin.com/StAEiXbY SQL fiddle: http://sqlfiddle.com/#!2/20081/1/0 ...
3
votes
2answers
81 views

Designating databases to certain CPUs

SQL Server 2012. AlwaysOn Cluster with two nodes, each has 4 cores We are consolidating SQL server instances and moving bunch of databases from various locations to a single cluster which has two ...
3
votes
2answers
113 views

Do MySQL optimizations hold for MariaDB also?

I am a developer who will be starting a new project soon in which database performance will be crucial. The current candidate for the backend is MariaDB. I have worked with MySQL and MariaDB before in ...
0
votes
1answer
85 views

Text Storage and Database Design Optimization in a SQL Server Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
0
votes
2answers
159 views

DB Design Payments Table

I'm working on a Desktop application that uses a DB to store information i.e (customers, houses, proprietors, payments). So the application must accept payments from customers to company, company to ...
1
vote
2answers
117 views

Database design suggestions for a data scraping/warehouse application?

I'm looking into the database design for a data warehouse kind of project which involves a large number of inserts daily. The data archives will be further used to generate reports. I will have a ...
0
votes
0answers
24 views

MySQL Optimisation

I am running mySQL 5.6.16 on a server running Centos with 20Gb RAM ad 1TB disc space. when running SELECT scripts wiht order and group by statements and a number of joins, the results can be very slow ...
0
votes
0answers
45 views

Should I separate my preferences table?

I have a hobby site where users can find other hobbyists of similar interest. Each user can set a series of preferences that get saved into user_prefs. Right now there are 4 types of preferences, an ...
1
vote
2answers
280 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, ...
0
votes
1answer
137 views

Why is my Mysql Query verry slow when selecting a text field

i have a problem with a left join. Tables are myisam. Tables CREATE TABLE `article` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `force_startpage` tinyint(1) DEFAULT '0', `title` varchar(255) ...
0
votes
2answers
163 views

Long running simple query SQL Server 2008 R2 [closed]

I have a very simple query running in a SQL Server 2008 R2 instance: SELECT TOP 10 * FROM messages m INNER JOIN recipients r ON m.id = r.id WHERE m.timeStamp > '2014-07-14T00:00:00.000' AND ...
0
votes
1answer
99 views

mysql fulltext query is very slow

I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single ...
7
votes
3answers
371 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
3
votes
2answers
107 views

SQL Server Baseline and Performance Optimization

I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance. Could anyone help with this or point me to where I can possibly get a ...