A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

1
vote
1answer
142 views

Reviewing all new MySql queries on staging environment

The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter ...
1
vote
1answer
43 views

Get wall_post and profile data for several users

I'm trying to build a query for fetch wall_posts and profile fields for several user_id. First see the images at: http://www.dropmocks.com/mBjQ7P. The table with id_user_rq, id_user_ap is the table ...
1
vote
1answer
214 views

MySql - How can I improve this query?

I have the following table: http://pastebin.com/XH6FvhZ8 and the following query: SELECT * FROM (SELECT * FROM `job_logs` ORDER BY id DESC) AS job_logs_tmp WHERE (job_logs_tmp.user_id = ...
1
vote
1answer
463 views

Oracle Benchmarking (SQL_NO_CACHE …)

I want to benchmark some queries on an oracle System from a JDBC application. However, the database seems to cache at some point in time. However, this is not intended in this case. Is there ...
1
vote
2answers
130 views

Improving MySQL efficiency

I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by ...
1
vote
1answer
45 views

Why MySQL SELECT performance depends on table size? [closed]

I've got two identical tables with the following structure: CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT ...
1
vote
0answers
34 views

Most effective way to manage a growing list of time series data in an MS Access database

I am building a database to store time series data along with some other summary statistics. Since this is a local application I do not have access to more robust database solutions and am confined to ...
1
vote
0answers
27 views

PostgreSQL DB freezes with large inserts

I am developing a web application with an SQLAlchemy ORM linked with PostgreSQL database backend. I have this usecase where a client uploads a file of about 20 MB containing large data. What I am ...
1
vote
1answer
48 views

MongoDB document size for collection - impact on RAM and query performance

I have been advised on stackoverflow to ask this question on dba.stackexchange instead - We are using MongoDB version 3.0 with WiredTiger storage. As newbies to MongoDB, we may have designed our ...
1
vote
0answers
54 views

Is there a way to improve this query further?

I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make ...
1
vote
0answers
31 views

Poor performance filtering mysql spatial join

First of all, sorry for the crossposting. I ask this question in stackoverflow and they sent me here I have a mysql table with spatial references (GEOGRAPHIC_FILTER), like cities, countries, natural ...
1
vote
0answers
29 views

MDX query is SLOWER with warm cache

I have a MDX query that executes faster on cold cache than warm cache. If I ran the same MDX query after restarting SSAS it takes 14 seconds. All subsequent runs of the same statement then take 50 ...
1
vote
0answers
77 views

Simple avg query on large table much slower in PostgreSQL than SQL Server

I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012). ...
1
vote
0answers
24 views

Sampling and matching samples with spatial conditions

The Problem I have two tables with start points of a route and possible end points of a route which need to be matched against each other. I also have a given distribution for the distance between ...
1
vote
1answer
43 views

Mysql Partitions with Partition limit

I have a partition table on basis of Primary key having 1.5 Billion rows in it. But when i check ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know how does the ...
1
vote
1answer
333 views

Optimizing Amazon RDS - MySQLTuner Recommendations / Feedback & Suggestions

I'm currently trying to optimize a database I've inherited for speed. The server is an Amazon RDS db.m3.large instance: 2 VCPUs 7.5 GB Memory 500 GB HDD My current strategy is to do the ...
1
vote
0answers
71 views

How to optimize inefficient query in PostGIS

I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow). This query takes about ...
1
vote
0answers
26 views

How to query against legacy data that needs to be cleaned up?

I have an existing SQL Server DB with 3 tables (note: this is a made up example, but modelled after a real problem). Artists : contains music artists MusicRecords : contains information on music ...
1
vote
0answers
46 views

Is it bad practice to launch more than 100 sql statements at once?

I have PHP code that is meant to updating the billing status of someone. I only want it to update if the bill has been successfully created and therefore I think a possible solution would be to ...
1
vote
1answer
152 views

SQL Server 2012 stored procedure runs very slow sometimes

I have a stored procedure with a single large query which takes an ID as a parameter and returns some counts. It has quite a few inner joins and left outer joins but they are all on indexes. The ...
1
vote
0answers
90 views

Oracle instead of trigger update performance issue

I have a large table containing over 15million records with 4 columns that form a compound key. Because we use a development environment that doesn't support compound keys I was required to create a ...
1
vote
0answers
49 views

Is there any way to improve this code?

SELECT tblstud.studid, tblsched.ip FROM tblstud LEFT JOIN tblstudsched ON (tblstud.studid=tblstudsched.studid) LEFT JOIN tblsched ON (tblstudsched.schedid=tblsched.schedid) ...
1
vote
0answers
24 views

Can MySQL Index Merge an Composite index with a single column index?

I am tuning a query and currently have an index as following : Index 1 : (col1, col2) An example query would be : SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2'; I have created an index on ...
1
vote
0answers
25 views

Best ratio for files and file groups for a highly partitioned table

I have a number of tables with exactly 1000 partitions. Each table can be up to 200G with 100M rows and cannot be compressed. Data in partitions distributed quite equally. The SQL Server has 8 ...
1
vote
1answer
23 views

Create a log file for check which time import has been done and to split the import files

How to create a log table for when does the last import has been done and also for Query for split the records in to 1000 while importing. to avoid the bulk insert For example : If the data ...
1
vote
1answer
280 views

MySQL: Optimize query with a range and distinct count

I have a query on a large table with millions of rows that looks like this: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE ...
1
vote
1answer
68 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
1
vote
1answer
42 views

Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit

We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
1
vote
0answers
84 views

SQLServer dependency graph and performance

I have a large stored procedure running on SQLServer2008 to produce a report on the previous day's data. It rehashes data from 12 tables, including a lot of ORDER by date and CASTS. Among others, it ...
1
vote
0answers
68 views

Odd Execution Plan

I have a table in SQL Server that is performing very slowly and I have been trying different indexing options to try to speed it up to no avail. I suspect all of the varchar(max) fields may be a ...
1
vote
1answer
49 views

How much should I care about prepared statements for simple queries?

I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements. The question originates from the fact that I recently noticed ...
1
vote
0answers
66 views

How to use join with this query? [closed]

This query is working fine, and giving the right result. In this query all columns are in one table, but now I have made some changes. Now columns named Semester and Major are in different Table ...
1
vote
0answers
45 views

Copying to tmp table slow for query … but only sometimes

I have a query, and I admit it may not be the prettiest query in the world. Sometimes this query runs fairly fast (e.g. 200ms) and other times it runs super slow (e.g. 30s). I don't know why, and ...
1
vote
1answer
52 views

pt-query-digest results are empty

I have a slow log generated by MySQL 5.1.71-log, that I've copied onto another machine. It appears I'm not getting any results from pt-query-digest. $> pt-query-digest slow.log # 2.1s user time, ...
1
vote
1answer
207 views

Slow SQL Server authentication, but fast SAN, fast querying?

I am asking you after days of research but I think I don't have required skills to understand what is happening. I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the ...
1
vote
0answers
26 views

Why do these two queries (which I think are equivalent) rely on different keys?

We have a query that we don't like, since it ends up doing a full table scan. EXPLAIN SELECT count(*) AS c, location.name FROM study LEFT JOIN location ON study.location_id = location.id ...
1
vote
0answers
58 views

Slow compilation of a SQL statement

I have SQL Server 2014 and a large table (more than 200Gb) with 100M rows and more than 300 columns. For one "unpivot" query I wanted to see its estimated execution plan and it took more than 90 ...
1
vote
1answer
118 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
1
vote
1answer
67 views

Mysql super slow

I have a database of movies that I downloaded, it is around 51mb in size. One of the tables has more than 750k records(all separate insert statements(I believe this is bad)). The rest aren't so bad ...
1
vote
0answers
563 views

How to improve query performance in PostgreSQL? [closed]

I have the following query to be execute within a second but getting time for execute 2.8 minutes: Query: Using Common Table Expression. explain analyze with temp_cte as ( Select ...
1
vote
0answers
24 views

Optimize finding the next batch of data from A to be crunched and inserted into B

I'm using PostgreSQL to store data produced by a large distributed computation. There are several stages to this computation; the idiom we've settled on is that there's a table containing the results ...
1
vote
1answer
827 views

SQL hourly data aggregation in postgresql

I am a newbie with database so I'm seeking your help with this one. I have a table containing time series data. 2012/01/01 00:10, 10 2012/01/01 00:30, 5 2012/01/01 01:00, 10 2012/01/01 01:40, 10 ...
1
vote
0answers
65 views

Why is one of these queries orders of magnitude slower than the other?

Sorry for the vague headline question. Essentially, and I know this is generally terrible practice, but I have query with nested subqueries as follows: SELECT * FROM ibt1 WHERE basetype = 60 ...
1
vote
0answers
22 views

Difference in the workload related to data-amount and search option

A couple of days ago I was asked why it is necessary to use the first normal form also for separation of names. We have a table which stores private customers (Name+Surname;Information) and business ...
1
vote
0answers
50 views

Microsoft Access database performance

I wonder how MS Access manage to show long list of records when we want to browse theme in Datasheet view. 1 - Does it load limited amount of records on start-up and then load the remainder upon user ...
1
vote
0answers
139 views

Best database for fast read all in one table [closed]

I currently have a large database in SQLite that I was using for rapid-prototyping for a software I am working on. Now that I am planning to move it to another database that should give better ...
1
vote
0answers
96 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
1
vote
0answers
177 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The ...
1
vote
1answer
3k views

Query State hangs on sending data

For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why. This is the table creating script: CREATE TABLE IF NOT EXISTS `esp_game` ( `gameID` ...
1
vote
0answers
113 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...