A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
1
vote
0answers
202 views
Query tuning help needed (Hash Match and Table Scans)
I need help optimizing the following query (returning ~8k rows):
SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
FROM (
...
1
vote
2answers
304 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
0answers
85 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
500 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
3answers
1k 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 ...
0
votes
1answer
89 views
Similar queries taking different times to complete
I had the following query:
SELECT nvl(sum(adjust1),0)
FROM (
SELECT
ManyOperationsOnFieldX adjust1,
a, b, c, d, e
FROM (
SELECT
a, b, c, d, e,
SubStr(balance, ...
0
votes
2answers
110 views
MySQL: can not get rid of “Using filesort” in a simple query
Simple query but can not get rid of "using filesort":
CREATE TABLE IF NOT EXISTS `online` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`expiration` int(11) NOT NULL,
...
0
votes
2answers
150 views
Large time difference between two almost identical simple queries…why?
I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:
SELECT ...
0
votes
1answer
77 views
Create a VIEW that spans 3 tables
Sorry if the title is not clear - feel free to improve.
Here are 3 tables (not all data is relevant to the question):
CREATE TABLE `content` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
0
votes
1answer
95 views
Design database to query a date range for scheduling
I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made periodically I would like to design the table for them to be as ...
0
votes
2answers
191 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
1answer
137 views
Is possible to optimize this query or it's already optimized?
I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one:
--
-- Table structure for table `default_users`
--
CREATE TABLE IF NOT EXISTS `default_users` (
...
0
votes
1answer
208 views
Slow performance of MySQL Join Query
I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the ...
0
votes
1answer
154 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
95 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 ...
0
votes
2answers
48 views
How to use and optimize subquery on 100 million rows
What I'm trying to do is running a job on more than 100 million domains which haven't processed before.
I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
0
votes
1answer
34 views
Optimizing Query
Can anyone help me tuning this query.....
explain extended
SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a
LEFT OUTER JOIN
(SELECT msisdn
FROM as_treat_pre_usage_30days
GROUP BY ...
0
votes
1answer
91 views
What is the correct model for related tables with millions of rows?
I need to create a question and answer tables that will have millions (maybe billions) of rows.
The current model is:
Question Table
id_question (PK, auto increment)
id_user
question_content
...
0
votes
1answer
19 views
Simple query not using indexes
My query is not using indexes.
It is a simple query.
Can someone explain why that's the case?
The query:
select TIME_FORMAT(start_time,'%H:%i') start_time,
TIME_FORMAT(end_time,'%H:%i') ...
0
votes
1answer
44 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
61 views
Speed efficient query for membership first joined, latest category from membership table (min, max)
I have the following table representing membership information:
CREATE TABLE IF NOT EXISTS `membership` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`organisation_id` int(11) NOT NULL,
...
0
votes
1answer
102 views
slow queries - set weight to token type in tsquery - postgresql
Postgresql version 9.2.3!
I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm ...
0
votes
1answer
46 views
Faster joining on 'most recent' related rows
I have a bug tracking application. The bug table stores very little,
because every state change to a bug happens by adding a comment. For
example, the current state of a bug is determined by the state ...
0
votes
1answer
75 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
0
votes
1answer
59 views
Query taking long time
My query is taking around 540 seconds to get result.
Please help me to rewrite it.
Query:
SELECT pd.domain, fd.passkeyid
FROM portfolio.domains AS pd,
fabulousdomains.domains AS fd
WHERE ...
0
votes
1answer
80 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 ...
0
votes
1answer
196 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 ...
0
votes
1answer
699 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 ...
0
votes
2answers
104 views
Attempting to avoid SORT in query planner
I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically):
...
0
votes
1answer
123 views
Help me to better design my query so I can gain more performance
I need to find so called first entry in database for items regarding their partners.
I wrote query and it is giving me correct results but it sucks with performances.
I suspect that I have missed ...
0
votes
1answer
223 views
Returning Multiple Result-Sets or Single XML Blods
I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with ...
0
votes
0answers
11 views
Identical Postgres index scan taking 5x longer on server
I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject:
Column | Type | Modifiers
...
0
votes
2answers
46 views
How do I use subquery on the same table in MySQL?
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could ...
0
votes
0answers
67 views
how to find similar word with more similarities
how to Find words with length less than or equal....
declare @inp nvarchar(max),@data nvarchar(max)
set @inp='You can dance, you can jive, having .... jove... jve, ...'
set @data = 'jeve'
...
0
votes
0answers
46 views
MySQL query optimization when no row is returned
I am using explain to figure out what is happening in my query which is:
explain select t from c where u1_id = 1 group by t;
I see "Using where" in the Extra column of the resultset. But this only ...
0
votes
0answers
40 views
SQL nested select (query rows after variable), running slow
Have large table (Sybase) rows = 6276225. I'm pulling call status and my query is very slow.
In effect what I’m trying to do is pull data for any event after my variable. While querying for just one ...
0
votes
1answer
33 views
MySQL stored routine performance while using PREPARE
Instead of maintaining stored routines for each database in my current environment i have decided to create separate database just for stored routines storage. Mainly i am using them for reporting. ...
0
votes
1answer
46 views
MySQL 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
2answers
69 views
speeding up a query on MySql
I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query.
create table t2
select field_1,
sum(ifnull(field_2,0)) ...
0
votes
1answer
64 views
Second time query execution using different constants makes faster?
Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
0
votes
1answer
56 views
query taking long time to execute.we are expecting result in very quickly anybody please rewrite it
Query is taking long time to execute.we are expecting result in 0 sec.If possible Please help me to rewrite it. Also suggest me to add any indexes on table.Also suggest me if optimization is needed.If ...
0
votes
1answer
68 views
Mysql. Block/disconnect slow queries
I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches ...
0
votes
0answers
72 views
Performance Optimising MySQL query
I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5)
I was wondering if there is any way to enhance this query so ...
0
votes
1answer
57 views
Long running query
This is my long running query:
select *
from t_keyword
where id in
(
select rel.element_id
from t_event event
join t_event_element_rel rel on
event.id = rel.event_id
where
...
0
votes
0answers
44 views
Query to fetch missing siblings [closed]
DECLARE @AName VARCHAR(100)
DECLARE @BName VARCHAR(100)
DECLARE @CName VARCHAR(100)
SET @AName = ''
SET @BName = ''
...
0
votes
1answer
80 views
How can I optimize this query and support multiple SKUs?
My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
0
votes
0answers
108 views
how to improve performance of my query it was taking more than one hour in my environment
How can I improve performance of below query? It was running more than one hour to execute in my production environment. Query plan:
mysql> explain
SELECT transaction_detail.trans_id,
...
0
votes
0answers
104 views
How to load XML data into an oracle db table ?
Is there a way to load the XML format data into an Oracle 11g DB table directly ?
I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ?
...
0
votes
0answers
101 views
Can I do a MySQL subquery across criteria with different number of results
I inherited a site which has a product search running Coldfusion8 and MysSQL(5.0.88). Both filling the search form with default values and the search take forever, so I'm trying to improve both.
...
0
votes
0answers
39 views
Resource to learn writing most efficient and highly optimized sql queries [duplicate]
Possible Duplicate:
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
Can some one point me to a great book/great books or some really solid reference/references ...
