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
1answer
19 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 ...
1
vote
1answer
39 views

MySQL JOIN two tables and get latest result

I want to JOIN two tables and get the latest result from each one of those two in a single table. I'm kind of concerned about speed too since tables are growing kinda fast. Close to 60-70k records a ...
0
votes
1answer
19 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] ...
2
votes
1answer
29 views

Optimizing a 3 table join query

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title, job.short_description, job.status, ...
1
vote
0answers
31 views

How to speed up for huge tables in SQL select query?

I have the following huge tables. Table_1 with 500000 (0.5m) rows Table_8 with 20000000 (20m) rows Table_13 with 4000000 (4m) rows Table_6 with 500000 (0.5m) rows Table_15 with 200000 (0.2m) rows ...
0
votes
0answers
27 views

Why is MariaDB 10 refusing to use my index on a date field?

I have the following table definition: CREATE TABLE `bets` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `id_mp` bigint(20) NOT NULL DEFAULT '0', `coin` char(3) COLLATE utf8_unicode_ci NOT NULL, ...
0
votes
1answer
45 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than ...
1
vote
4answers
307 views

Improve delete speed for SQL Server

We have huge production database, its size is around 300GB. Is there any approach to improve performance of a delete query? Right now deletion speed is between 1-10k per minute, it is very slow for ...
0
votes
0answers
55 views

MySql Innodb Database Slow INSERT Query to Remote Server

I have searched thoroughly on how to improve general performance of my database which is hosted on Hostgator. I have mostly come across performance tuning strategies which are catered to only local or ...
1
vote
0answers
37 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | ...
0
votes
0answers
28 views

Postgres Query Optimisation (Rails App)

I have a very slow running SQL query that is run from my Rails app using the Model.find_by_sql method. I've made a number of optimisations but it's still extremely slow. It would be great if anyone ...
5
votes
2answers
208 views
+100

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
0
votes
0answers
22 views

select with a multiple row in pivot table [migrated]

I really don't know how to retrieve a specific row from a pivot table. Here's my pivot table: +----+-------------+--------+ | id | peticion_id | tag_id | +----+-------------+--------+ | 1 | ...
0
votes
1answer
42 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
39 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 ...
3
votes
1answer
41 views

Pagination performance, with subquery, inner join and where

I am trying to optimize following query (this is most simplified version I could came up with): SELECT tr.Id, StatusDate FROM ( SELECT tr.Id, tr.StatusDate FROM mon.ArchivedTaskResults_201504 ...
10
votes
1answer
127 views

How to optimize a query so that it seeks on one index first, and then another index after that

I have two sets of earth measurements from satellite data, each with time fields (mjd for mean julian date) and geography positions (GeoPoint, spacial) and I'm looking for coincidences between the two ...
1
vote
1answer
45 views

Why MySQL SELECT performance depends on table size? [closed]

I've got two identical tables with the following structure: CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT ...
0
votes
1answer
61 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 ...
2
votes
0answers
31 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
1
vote
1answer
78 views

Why SELECT is so slow

I have the following table CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT NULL, w4 int(11) NOT NULL, ...
2
votes
1answer
74 views

Slow PostgreSQL query

I'm building a search interface for a web site that currently searches through approximately 2 million pieces of real estate with approximately 60 million attributes (address, city, state, ZIP, beds, ...
1
vote
2answers
40 views

Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's ...
1
vote
1answer
103 views

high PAGEIOLATCH_SH waits on server [closed]

EDIT: I know this question is closed, but I hope it will help someone. The problem is on our disks ( cluster ). Using PerfMon, I could create some counters ( disk reads and writes ) and the reads ...
1
vote
0answers
37 views

Most effective way to manage a growing list of time series data in an MS Access database

I am building a database to store time series data along with some other summary statistics. Since this is a local application I do not have access to more robust database solutions and am confined to ...
6
votes
3answers
121 views

Convert date in date list condition to list of date ranges

I want to look for all the records that occur on specific dates. SELECT * FROM table1 WHERE date(column) in ($date1, $date2, ...); However, as many of you, know this kind of comparison doesn't get ...
2
votes
0answers
77 views

Why is this MySQL query “using where” instead of “using index”?

Could you help me with this query, please? SELECT p.patid, MAX(c1.eventdate) as eventdate from patient as p left join op_adv_effects._clinical as c1 on p.patid = c1.patid left join ...
2
votes
1answer
84 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND ...
2
votes
1answer
72 views

DB2 CASE statement faster with parentheses around entire WHEN

Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc: CASE WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM AND RECTYPE = 'RO' THEN 1 ELSE 0 ...
1
vote
0answers
28 views

PostgreSQL DB freezes with large inserts

I am developing a web application with an SQLAlchemy ORM linked with PostgreSQL database backend. I have this usecase where a client uploads a file of about 20 MB containing large data. What I am ...
2
votes
2answers
58 views

MySQL taking too much CPU

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that ...
1
vote
1answer
81 views

Index to filter a view before joining the underlying tables [closed]

Using SQL Server 2008 R2 Suppose you have a query like the following select top 1 * from view_cms_user where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n' view_cms_user is basically: ...
0
votes
0answers
7 views

Create table with select Query on DB link

I'm trying to execute the following query, it is taking more than usual time. I'm retrieving data from db link. without the create table statement the query is giving results. for Create table also if ...
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
0answers
55 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
0
votes
1answer
20 views

MySQL 2 Million Rows, longtext and varchar indexes

I'm trying to find a solution for this, with no success. I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR. name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE ...
0
votes
2answers
42 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 ...
1
vote
2answers
34 views

Two (almost) identical queries, but only one of them uses the index

I have a table with two columns, batch_id and study_id. There are indexes on both columns. I want to get a random row back from MySQL. I originally wrote: SELECT * FROM alt_join_batch_study ORDER BY ...
0
votes
1answer
33 views

Optimizing a query on Mysql 5.5.24

I am trying to optimize the query SELECT CONCAT(lastname, ', ', title, ' ', firstname) AS fullName, e.lastname, e.firstname, e.email, e.userType, e.userID FROM employee e, ...
-3
votes
3answers
40 views

How work SELECT in database [closed]

My table has 8,400 rows of user information. Say I submit the query SELECT name FROM users WHERE name = 'jhon'; I know that 'jhon' is the 400th row in my dataset. My question is, will the DBMS ...
1
vote
1answer
54 views

MongoDB document size for collection - impact on RAM and query performance

I have been advised on stackoverflow to ask this question on dba.stackexchange instead - We are using MongoDB version 3.0 with WiredTiger storage. As newbies to MongoDB, we may have designed our ...
-1
votes
2answers
129 views

Why is “select join insert” so much faster than “loop insert”?

MySQL docs state that the time required for inserts has these approximate proportions: Connecting: (3) Sending query to server: (2) Parsing query: (2) Inserting row: (1 × size of row) ...
0
votes
0answers
41 views

Which of sys.dm_exec_query_stats columns should I pay most attention?

I'm trying to tune my database and currently staring into sys.dm_exec_query_stats (with actual queries): SELECT SqlText.text, * from sys.dm_exec_query_stats CROSS APPLY ...
6
votes
2answers
299 views

Unexpected Implicit conversion in a procedure

I have a procedure like this (simplified): CREATE PROCEDURE test @userName VARCHAR(64) SELECT * FROM member M INNER JOIN order O ON M.MemberId=O.MemberId WHERE M.Username = @userName There is a ...
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
1answer
22 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
0answers
8 views

indexing table column on the go of mysql query commit

It is said that to optimize the query cost of execution, then it would be by indexing the field of tables in database. Since I don't have a permission to enter the phpmyadmin in my web server as I am ...
0
votes
0answers
24 views

Best way to run a formula (segment) a large number of users with custom attributes

Here is my question. I have a table that stores a large number of people (80 mil). Each person has a GUID id. Then I have another table (or set of tables) that stores information about people. e.g. ...
0
votes
1answer
75 views

Improve Query Performance

I have the following sqlite3-Tables: db.execute("""CREATE TABLE IF NOT EXISTS STATIONS (StationUID INTEGER PRIMARY KEY AUTOINCREMENT, StationNumber TEXT NOT NULL, ...
0
votes
0answers
71 views

Slow running Oracle query caused by unnecessary full table scan

I'm by no means an expert with Oracle, but I know enough in terms of how to analyze explain plans and perform basic optimizations. However, this time, Oracle has stumped me with it's explain plan. ...