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
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 ...
1
vote
4answers
295 views

Which of these queries is best for performance?

Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i ...
1
vote
0answers
91 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
53 views

Optimizing a query using derived tables [duplicate]

As part of a daily cron job, I need to run a query that processes a whole lot of data. This data is related to the visitors coming to a website, and updating the data with what we have captured ...
1
vote
0answers
270 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
4k 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
3answers
4k views

Simple select is taking too long

I am facing an issue with the performance. The selection of data from table is taking more than 20 sec to retrieve close to 300k records. Created a clustered index on col1 and col2 but no improvement. ...
1
vote
1answer
449 views

Retrieving data speed tweaks SQL Server 2005

I have a database in Microsoft SQL Server 2005. I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT). Columns HASHKEY has 19 digit BIGINT values, which can ...
1
vote
0answers
221 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
136 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
145 views

Optimising Large SQL Server Tables [closed]

I have a large table with around 50 million rows and 15 columns. Whenever I read, I always need all columns so I can't split them. I have a clustered index on the table with 4 keys (ALL INT) and I ...
1
vote
0answers
164 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
3answers
3k 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 ...
1
vote
1answer
180 views

SQL Server 2008 search joining query

I have two table as one is the message table and another one is messageUser table. Now i need to check before insert a new row. for example, M_MessageMessageId========= ...
1
vote
0answers
463 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
203 views

How can I use mongoDB efficiently in this scenario?

I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned: ...
1
vote
1answer
272 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
1
vote
1answer
457 views

Optimizing User Defined Custom Columns

I am building a web based app that has the potential to have very high traffic and collect a lot of data. Most of this traffic will be utilizing the database. Database is MySQL InnoDB. I want to give ...
1
vote
2answers
1k 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
185 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
1answer
391 views

MySQL query optimization with aggregate ORDER BY

I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items." The schema is as follows: ...
1
vote
1answer
118 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
0
votes
4answers
613 views

Can migration from MS Access to SQL Server or … make database faster? [closed]

A company asked me to migrate their database from MS Access to SQL Server or MySQL because they think that on those engines their database will run faster. But I wonder if their problem isn't because ...
0
votes
2answers
97 views

Can I improve this query?

SELECT * FROM SameLogTable WHERE ID_Table IN (SELECT ID_Table-1 FROM SameLogTable WHERE <SameCondition>) OR ID_Table IN (SELECT ID_Table ...
0
votes
2answers
83 views

How can I replace the following query with ISNULL function in SQL?

I have the following query. For performance optimization reasons I have been asked to replace the logic with ISNULL function in SQL. How can I achieve this? please advice. SELECT ...
0
votes
4answers
80 views

Single SQL query to retrieve data in a particular order (not ASC/DESC)

I have a MySQL table, let's say called reviews review_id, product_id, language_id, rating, review, author language_id ranges from 1 to 10 for 10 different languages. Let's say I determine that my ...
0
votes
2answers
357 views

Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

I have been working on making this query work more efficiently. I found that the number of Ors within the where clause are the biggest problem within this query. This query sits in a stored ...
0
votes
2answers
209 views

Database SQL Script very, very slow (string manipulation over 700k rows)

I have table containing 700,000 rows containing a string id field with values such as rec-232276-dup-0 and rec-354240-org. The rec- is constant but the other parts of the id can change. I want to ...
0
votes
3answers
4k 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
2answers
64 views

Oracle query optimization

We have a query that runs on a view and its execution time is about 6 min. SELECT COUNT(*) FROM someschema.item_total_violations WHERE channel_id = 122507833 AND item_date >= ...
0
votes
2answers
706 views

Why would a long running query remain sleeping and with null wait_info

The following query was run on SQL Server 2008 R2 and took over 1 hour to run: select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id It produced 0 rows ...
0
votes
1answer
68 views

Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server [closed]

I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit. MySQL 5.1.58 community edition is installed on Windows servers while ...
0
votes
2answers
60 views

Which one is better way to insert data? [closed]

I have two cases case create proc createEmployee @firstName varchar(255), @lastName varchar(255) = null as insert into Employee_Tbl values (@firstName, @lastName) case create proc ...
0
votes
2answers
67 views

A simple database and Query System? [closed]

I am new to databases and trying to solve one problem in which I have to build a simple database having few entries and then querying that database and then recording that answers from the database. I ...
0
votes
3answers
2k views

Duration Field in Profiler

I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query. My ...
0
votes
2answers
766 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
1answer
130 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
1answer
25 views

Clustering a heap by date in stead of unique composite key

I've been working on improving the performance of a large reporting database. This database is 2TB in size, and one of the larger tables is a heap with three non-clustered indexes on it. The ...
0
votes
2answers
63 views

Does it make sense to order condition in where query?

We have a clustered index in table Tbl with order of A,B,C. Does it make senses to write a query to have WHERE A = @a, B = @b, C = @c or it'll be as same as WHERE C = @c, B = @b, A = @a Does the ...
0
votes
3answers
72 views

How to return last used record?

I have simple table called ips with 2 field ip and address, which schema is following CREATE TABLE IF NOT EXISTS `ips` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(255) ...
0
votes
3answers
1k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
0
votes
2answers
63 views

MySQL query optimization for legacy application

I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone. ...
0
votes
1answer
402 views

Take query results and insert into new table [closed]

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
0
votes
2answers
827 views

How can I speed up this query on table with 65 million records

select hash_id, hash_value, substring(T.cri, 1, 7), substring(T.cri, 9, D2.Pos-9), substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1), ...
0
votes
1answer
789 views

Calculating time spent on query cache lock

How can I calculate the total time my MySQL database is spending Waiting for query cache lock ? I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the ...
0
votes
2answers
91 views

Query performance differs greatly between a development setup and production

I'll try to keep this question straightforward, though I am dealing with a big ball of mud. When I run my test query acrossed linked servers both located locally (on a shared virtual host), the ...
0
votes
1answer
1k 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
327 views

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
1answer
36 views

Optimizing key lookup in subquery with UNION ALL

I am trying to optimize following query (little bit more complex actually, but this is important part): SELECT Id, StatusDate, [...Lot Of Columns...] FROM ( ( SELECT Id, StatusDate, [...Lot ...