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

3
votes
1answer
582 views

MySQL Handler_read_rnd_next == tablescans without indexes?

I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance. In the MySQL Handlers Plot, i have been noticing alot of ...
2
votes
1answer
770 views

How to speed up mysql query?

I have query : SELECT `school`.`ARCO_name`, `student-`.`ClassSize_7`, `student-`.`ClassSize_8`, `degree_o`.`degree_code`, `accredit`.`full_faculty_3`, ...
1
vote
0answers
796 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
1answer
264 views

MySQL Partition based on month-year

Is there a way that I can create a month-year partition on the below table? Without having to define all the permuations of month/year combos ? CREATE TABLE visitor_visits ( id int(10) UNSIGNED ...
3
votes
1answer
739 views

MYSQL Slow SELECT with multiple joins and ORDER BY

I have this query SELECT r0_.id AS id0, r0_.deleted_at AS deleted_at1, r0_.updated_at AS updated_at2, r0_.created_at AS created_at3, r0_.is_vehicle_offer AS is_vehicle_offer4, ...
1
vote
1answer
883 views

postgres composite index design

In my rails application using the Postgres database, I have a table called tw_schedules which belongs to a scenarios table (one scenario to many tw_schedules). In addition to the scenario_id foreign ...
3
votes
1answer
654 views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
6
votes
2answers
679 views

Parameter Sniffing: Why Does This Become An Issue?

Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds). After suspecting ...
4
votes
3answers
649 views

Some book suggestions for performance tuning

I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
1
vote
1answer
38 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 ...
8
votes
3answers
370 views

What is retrieved from disk during a query?

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google... Do the number of columns in a particular table affect the performance of a query, ...
2
votes
1answer
102 views

Get the id_user_app if not exists in id_user_rq

I'm building a application and have this table for users relationships. I need to build some queries like for example: Who invited me as a friend? Who I invited to be my friend? Who I accept as ...
3
votes
5answers
354 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
2
votes
1answer
481 views

How to calculate query that access system view cost in Oracle?

For a query that returns if there are any active transactions within current session , SELECT COUNT(*) FROM v$transaction t INNER JOIN v$session s ON (t.ses_addr = s.saddr ) INNER ...
0
votes
1answer
168 views

MySQL query took 98 seconds to run but EXPLAIN looks okay

I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something? Here's the SQL: SELECT ...
0
votes
1answer
1k views

Is there an easy way to debug and find out why a query runs slow?

I'm running onto a weird issue. We've got two db servers, one is development and the other one is production. Both have same specs. VMs (Hyper-V) Windows Server 2008 R2 64 Bit SQL Server 2008 R2 64 ...
2
votes
1answer
248 views

datetime index skipped for same query different params

I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575) Any idea why it skips ...
2
votes
1answer
135 views

Should I use an index or a hash function on a primary key if I have 100000 rows of data?

I have a table named PRODUCT that has 100,000 rows. I was wondering if either indexing, or a hash function on the primary key would result in better performance. I was also hoping someone could ...
1
vote
2answers
153 views

Can a poor database layout cause performance issues for simple queries?

I have a database layout that is realy a mess and needs to be optimized. Does the layout of the database and some queries that are executed cause other queries to be slow too? For example, an insert ...
2
votes
1answer
923 views

How to optimise read/write performance a MySQL table with many rows?

I am developing a django application where I use a MySQL database. In my application there is one table for domains information which has only 2 coloumns id domain_name but I have a problem when I ...
9
votes
3answers
749 views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their ...
7
votes
1answer
218 views

Performance of query with a range condition and order by

We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies: lexikon ------- _id integer PRIMARY KEY word text frequency integer ...
6
votes
1answer
201 views

Is it recommended to clear the query plan cache

Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation. After making the changes to the code, ...
1
vote
2answers
212 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 ...
2
votes
1answer
219 views

Why is the following query so bad performance-wise

After asking this question, I got to thinking about why a certain query was doing all the problems. In short, there was a query which took 500ms, I ran some execution plans and applied the ...
2
votes
3answers
191 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
178 views

Normalization and Breaking down tables for InnoDB

I'm designing a web application, something small but deals with lots of read and inserts. Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table called User which has ...
0
votes
3answers
2k views

Will more indexes on a table affect performance? [duplicate]

Possible Duplicate: How do indices impact query performance? How to know when/if I have too many indexes? Say ‘X’ report using some "where" condition in the statement so we are creating ...
7
votes
3answers
1k views

Is it better to populate variables using SET or SELECT?

What is the better way (with regards to performance) to set a value to variable? By SET command: DECLARE @VarString nvarchar(max); SET @VarString = 'john doe'; SELECT @VarString; By SELECT ...
8
votes
3answers
2k views

TSQL performance - JOIN on value BETWEEN min and max

I have two tables in which I store: an IP range - country lookup table a list of requests coming from different IPs The IPs were stored as bigints to improve lookup performance. This is the table ...
1
vote
2answers
147 views

Why are my results returned twice?

I've this query: SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id, `default_wall_posts_comments`.`comments` as comments, UNIX_TIMESTAMP() - default_wall_posts_comments.date_created ...
1
vote
4answers
445 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
3
votes
1answer
2k views

I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation

I want to get a dedicated server for my Database (MySQL) which runs InnoDB Engine. There is this option in InnoDB capacity that you can define the IOPS in it. Let's say that I have a SATA 72K-RPM HDD ...
1
vote
1answer
774 views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
1
vote
1answer
189 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 = ...
0
votes
1answer
128 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
2
votes
2answers
2k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
3
votes
2answers
219 views

How do I count rows with two properties in one index scan in SQL?

I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it. I need to output the following: for each day I need to ...
6
votes
3answers
508 views

Single query sending and retreiving duplicate data on MS SQL Server?

Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
4
votes
2answers
371 views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; ...
2
votes
2answers
511 views

How expensive are SELECT FROM LIKE statements from a design perspective?

I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files ...
2
votes
3answers
2k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
3
votes
1answer
404 views

Measuring the relative performance of queries given the assertion that “cost” isn't reliable

The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable. Source Does ...
1
vote
3answers
356 views

Checking if the query is made async or sync on SQL Server 2008 R2

I have tried to make async calls to my SQL Server instance as you may see on my SO post: ASP.NET MVC 4 asyncronous database call: never returns the HTTP response I did a benchmarking and the sync ...
6
votes
3answers
271 views

How can I identify when to create a new table to hold data that can be obtained from a query?

We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when ...
4
votes
1answer
4k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
4
votes
1answer
523 views

Does the order of columns has a significant effect on INSERT?

Do we experience a noticeable difference in performance for these two queries? INSERT INTO table (col1, col2, col3, col4, col5) ... and INSERT INTO table (col5, col3, col1, col2, col4) ... Do we ...
3
votes
1answer
135 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
5
votes
2answers
816 views

Why does MySQL choose this execution plan?

I have two queries, select some_other_column from `table` order by primary_index_column asc limit 4000000, 10; and select some_other_column from `table` order by secondary_index_column asc ...
12
votes
3answers
2k views

Are Views optimized when I add a WHERE clause to them?

Does it make a difference if you filter a View inside or outside the View? For example, is there any difference between these two queries? SELECT Id FROM MyTable WHERE SomeColumn = 1 Or SELECT Id ...