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

0
votes
2answers
64 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
73 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
2answers
67 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
511 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
925 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
835 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
92 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
335 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
2answers
410 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
45 views

improve long running query

I have this query INSERT INTO interactions( user_id, external_object_id, origin, interaction_type, interaction_sub_type, published_at, origin_id, created_at, updated_at) SELECT ...
0
votes
1answer
75 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 ...
0
votes
2answers
54 views

SQL Server 2005 query with multiple conditions, some indexed, some not

I have a table with 50 million rows. It is indexed on zip. A query Select * from table where zip = '12345' returns 20,000 rows in about 2 seconds When I add an additional condition it jumps to ...
0
votes
1answer
96 views

What is the best query to get the latest row version based on time?

I'm wondering what can be the most efficient query to get the latest rows from the following table. CREATE TABLE AGE_DATA ( INSERTED_DAY TIMESTAMP(6), HOSTNAME VARCHAR2(255 CHAR), CONCEPT ...
0
votes
4answers
101 views

Why does this query take 32 sec to execute?

I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is ...
0
votes
2answers
81 views

Querying MySQL, duration increases 10x fold after adding one more Join

I have a somewhat complicated query consisting of many joins on an issue tracker/project management database in MySQL. My issue is that my query was doing OK while I was in the process of building it ...
0
votes
1answer
77 views

Simplifying query by adding Computed Column, performance impact?

The dev team came across the following query (might be incomplete; it's retyped from a screenshot) which had caused slowness: SELECT d.catalogno, d.itemid, SUM(lineamount+taxamount) AS Amount, ...
0
votes
1answer
73 views

How can I control the order of a query regarding to inner join

I have 3 tables. They have table_a 5m records table_b 20m records table_c 20m records I'm performing the query below. SELECT * FROM table_a a INNER JOIN table_b b ON a.a_id = b.a_id AND ...
0
votes
1answer
457 views

Best way to store country and city information

I always wondered what is more efficient between two. There is one "main" table where I store all user related informations. Also, there are two more tables, country and city tables. city table ...
0
votes
1answer
64 views

How can I write queries as fast as possible in MySQL? [closed]

I'm looking for two things: Awesome manual about writing fast MySQL queries. Tool which will decompose my query into discrete steps, and give estimations of each step. I've seen the next tool, ...
0
votes
2answers
2k views

How to fix slow LIMIT queries with big offsets without heavily rewriting them?

Imagine a very basic example of your average discussion board. For example: CREATE TABLE threads ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) ) CREATE ...
0
votes
2answers
122 views

Get only future records

I have the following query: SELECT master.master_id, master.showname, MIN(CONCAT(shows.show_date, ' ', shows.show_time)) AS first_show_date, MAX(CONCAT(shows.show_date, ' ', shows.show_time)) AS ...
0
votes
2answers
346 views

What is the performance difference between IN (42) and id = 42 in MySQL?

Are there any performance differences between SELECT * FROM table WHERE id IN (42); and SELECT * FROM table WHERE id = 42; ? The question is about just supplying a single value. The ...
0
votes
1answer
286 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
0
votes
1answer
362 views

T-SQL Optimizing a Join on TOP Value from another table

I've got a data warehouse that goes through a full refresh each night that can take about an hour to process 16 million rows/25gigs of data and we're looking for ways to reduce this time without going ...
0
votes
1answer
2k views

Optimize MySQL Self JOIN Query

I have a query which is running in 15+ seconds SELECT t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize` FROM `stocks` AS t1, `stocks` AS t2 WHERE ...
0
votes
1answer
1k views

Query that only uses index takes long time to execute [closed]

I have a table with ~69,000 rows. I have this very simple query: -- SET STATISTICS TIME ON SELECT ID, FirstName, LastName FROM Customers -- SQL Server Execution Times: CPU time = 31 ms, elapsed time ...
0
votes
2answers
506 views

Query gets slow when bind peeking is turned on

There is an old database that was upgraded from Oracle 10 to 11G R1. In Oracle 10 we had disabled bind peeking. Since Oracle 11 has adaptive cursor sharing, we want to turn bind peeking back on. When ...
0
votes
1answer
170 views

Can I speed up this slow query which should be limited by MIN/MAX values?

The intention here is to look at the user retention over a period of time. My current solution employs a temporary table, since I do this over several ranges. CREATE TEMPORARY TABLE ...
0
votes
1answer
1k views

How to measure a perfomance of SQL Server database? [closed]

I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics. So, I need to measure "performance" of the RDBMS ...
0
votes
2answers
101 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
0
votes
1answer
92 views

How to optimize query?

I need to optimize the following query: SELECT DISTINCT column1, column2 FROM tbl_name WHERE column3 = 'x' AND TRIM(UPPER(column4)) = TRIM(UPPER('y')) AND TRIM(UPPER(column5)) = ...
0
votes
1answer
740 views

Filtering records not present with specific conditions in another table

I have a table (let's call it extra_info) in MySQL that has various extra infomation/labels on user accounts: account_id / data_key 11 / 'test' 11 / 'blah' 12 / 'coupon a' 12 / 'coupon b' 15 / ...
0
votes
2answers
261 views

How can I increase performance for select in select statement

The item table is like this id category_id name registered I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05--05, I have to count all the items ...
0
votes
1answer
131 views

Looking for performance improvements for this query

This query is taking a long time to execute. We are expecting results in 0 sec, if possible. Please help me to do any other following: Rewrite the query Suggest any indexes Any other optimization ...
0
votes
1answer
313 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
378 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
209 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
1answer
1k 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
197 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
162 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
1answer
37 views

Better way to full text query across multiple tables?

I've got a rather large query across multiple tables and their fulltext-indices which (unsurprisingly) is rather slow: SELECT t0.[Key], t0.[Nr] FROM dbo.[Customers_View] AS t0 WHERE (t0.[Deleted] ...
0
votes
1answer
101 views

Memory usage on select for large number of rows

I am trying to dump the entire contents of a large table, from the command line using pqsl, but am running into a problem where memory usage goes up, to the point where the process is killed, before ...
0
votes
1answer
83 views

Get data faster from databasse

My site having a more than 1.5 million records and my question is the how may i fetch record fast. i am spending so much time to find the way for it finally find out the one way which is to apply the ...
0
votes
1answer
39 views

represent multiple entities to one entity relationship

If suppose, I have following tables: Here, person can own either home or car. person can't own both at a time. If person owns home, then owns field will be equal to 1, if it owns car, owns field ...
0
votes
1answer
100 views

Complex query performance in MySQL InnoDB versus MyISAM

I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database. My model: users <n---n> right_holders <n---n> tracks ---n> raw_detections I have this ...
0
votes
1answer
87 views

How to speed up mysql query against a 204 million record table

For some reason my server has been finicky lately and for the life of me I can't figure out why. I monitor the memory usage by running shell# watch -n 5 free -m without running any activities ...
0
votes
1answer
45 views

MySQL - How can I improve this query? EXPLAIN attached

I have a query that I feel should go much faster then it does. Right now it'll execute around 1,5xx secs each time. I don't have any advanced knowledge of looking at the EXPLAIN and draw conclusions. ...
0
votes
1answer
152 views

Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench?

Problem Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark. The VM and db's are both fresh installs from vendor apt repos with default configs. ...
0
votes
1answer
42 views

How bigger page size effect at performance?

I know in case of Cluster index bigger page size improve peformance for range and multipoint query. Does bigger page size can effect to degrade performance when we have non cluster index .Because ...