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

2
votes
0answers
79 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
2
votes
0answers
147 views

mariadb out of memory crash

I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider. Both machines have 2GB RAM and 1GB ...
2
votes
0answers
78 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
2
votes
0answers
54 views

Single table row “sharding/versioning” to customize values

Introduction Hello everyone, I have done quite a bit of searching and I cannot find a solution for my specific issue. There are a few factors that complicate my situation, which I have outlined ...
2
votes
0answers
62 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
412 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
0answers
196 views

Simple Select * on SQL Server 2008 R2 table of 1812 rows is taking 3 minutes to load

A query on a table of 1812 rows is taking over 3 minutes to execute. Is there a reason this should be happening? If I execute a SELECT * statement on any other table the results pop back ...
2
votes
0answers
111 views

How To Analyse Index Scans

I am using merge replication with SQL Server 2012. During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
2
votes
0answers
322 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
1
vote
0answers
14 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
0answers
34 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
32 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
77 views

Best database for fast read all in one table

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
53 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
0answers
55 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
82 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
0answers
52 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 ...
1
vote
0answers
71 views

MySQL: What criteria should I base my table column order/arrangement/grouping on?

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...
1
vote
0answers
156 views

Sql Execution Plan - Hash Match

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the ...
1
vote
0answers
2k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
1
vote
0answers
124 views

Read vs. Write speed

In the data import procedures I have, I get external raw data and import it into tables. I don't just overwrite the data, even if it is only from one source, but before I allow an update, I check if ...
1
vote
0answers
79 views

Mysql performance issue when selecting text column

I have a performance problem when selecting rows containing text fields: SELECT id FROM message WHERE NOT(message.sender_id = 368352 AND message.is_deleted_sender = 1) AND ...
1
vote
0answers
120 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
329 views

SOLVED: Postgres 8.4.5: Bad query plan vs Good query plan when changing date range on large data table

SOLVED. I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000; Now the queries are running very fast, down to ...
1
vote
0answers
161 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
1
vote
0answers
847 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 ...
0
votes
0answers
42 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
0
votes
0answers
55 views

A working view now starts timing out

I have 2 identical databases (one larger, one smaller) with identical views. Recently a view on the smaller DB started timing out. Google searches seems to point to deadlocking of some sorts. If I ...
0
votes
0answers
20 views

Help with MySQL query which is only occasionally slow? Temp tables/Disc caching?

We have an issue with one particular query whch runs regularly on our MySQL database, though I am not necessarily sure it is a problem with the query itself. 90% of the time it runs perfectly fine ...
0
votes
0answers
29 views

Performance getting rows with max column

I have an historic table called 'historic', with three columns: object_id, day, value. For historical reason, day(date) is an integer number (number of day since 01/01/1904) Example: object_id | day ...
0
votes
0answers
18 views

Select changes in table by date

I have following table schema name type id int key string count int date date url_id id with example data id key count date url_id 1 system 100 ...
0
votes
0answers
37 views

Why is PostgreSQL doing a seq scan on column != column?

Why is PostgreSQL doing a table scan on column != column? Isn't this query guaranteed to return no results? Isn't anything != anything guaranteed to be always false for any record on a not null ...
0
votes
0answers
30 views

Slow query (Wordpress)

Can I speed up somehow the following query? Somehow to stop from creating a tmp table/filesort. Tried a couple of indexes but no luck. mysql> explain SELECT wp_posts.ID FROM wp_posts LEFT JOIN ...
0
votes
0answers
16 views

View in View slower to execute?

Sometimes it's easier and clearer arranged to create a View from another View. Can this cause some speed losses in relation to selecting everything just in one Big and long View-Command?
0
votes
0answers
44 views

INDEX SKIP SCAN performance on Index where first columns only contain 1 value

We're running an Oracle 11.2.0.3 database and have a table with above 10 columns in it. There is a non-unique index over all these columns. For simplicity in this example, lets assume the columns are ...
0
votes
0answers
17 views

Analize Query in MySQL Server 5.1

I try use MySQL Workbench 6 to analyze the cost, but they don't show the cost of query. How i get this costs?
0
votes
0answers
70 views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
0
votes
0answers
48 views

Latest of group in subquery is bottleneck, how to optimizing query?

See Original question I'm fetching the activity data for the latest activity for each company as part of a bigger query. I've been using the method provided by ypercube, and it works fine, but I find ...
0
votes
0answers
22 views

Can't run subqueries on my server (MySQL)

I have a weird problem, where I can run sub-queries just fine on my local machine, however they seem to be hanging forever when I attempt to run them on my server. Note that my server has plenty of ...
0
votes
0answers
32 views

MySql query keep running for a long time

Following query keep running for a long time and I'm using MySql 5.5.34 on XAMPP on Windows SELECT id_package, `pck`.`fk_sales_order`, `pck`.`fk_picklist`, count(pci.id_package_item) as items, ...
0
votes
0answers
56 views

Adding additional condition significantly degrades performance

Have the following query: SELECT * FROM table_A INNER JOIN table_B ON /* conditions */ INNER JOIN table_C ON /* conditions */ INNER JOIN table_D ON /* conditions */ WHERE table_B.state = 'active' ...
0
votes
0answers
40 views

Big Mysql table optimization

I have a table that I use to cache google translate API answers. Table structure is orig_string and group are indexes Now it has more that 1million rows with 35000 distinct values in group field. ...
0
votes
0answers
47 views

Oracle Merge performance

I've a question about the merge performance in oracle. I work in the business intelligence area of a big company and its quite common to have to move millions of records one side to other. The other ...
0
votes
0answers
82 views

Remote scan in OpenXMl

I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML. Something about this is causing a Remote Scan. Any ideas on how ...
0
votes
0answers
40 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
0
votes
0answers
26 views

Request for Help Optimizing a Query in MySQL

I have a query that is being used to report on the number of times a trigger in Zabbix, was fired by an event. For example, an event (not enough disk space) fires a trigger that sends an email to ...
0
votes
0answers
195 views

Yet another slow query Postgresql 9.3

I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, ...
0
votes
0answers
27 views

What Index can I apply to a GROUP BY, MIN() and WHERE MySQL Query

I have the following table: CREATE TABLE `wp_prize_draws_log_copy` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `entry_date` date NOT NULL, `entry_time` datetime ...
0
votes
0answers
59 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...