Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

1
vote
1answer
407 views

MySQL Query Optimization - Indexes & Joins 12 million+ records

I'm running the following query: SELECT `track_title`.`title`, `track`.`id`, `artist`.`name` artist, `album`.`title` album, `st_tag`.`tag`, `track`.`year` FROM `track` ...
1
vote
2answers
165 views

Improving performance of query with `WHERE <> ''`

I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case] Is there any way to improve the performance of the query SELECT * FROM myTable WHERE ...
1
vote
1answer
76 views

Does limit impact affected rows or not?

I have this table: CREATE TABLE IF NOT EXISTS `usergroups` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `group_id` smallint(5) unsigned NOT ...
1
vote
2answers
2k views

Improve performance with the WHERE NOT IN sub-select clause

In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year. ...
1
vote
1answer
81 views

Query for View with unique entries from Database with repeated entries

I have a table with columns of Hotel names, city name and country name (of the respective hotels). I need to create a view of unique entries which will contain city name and country name columns. Any ...
1
vote
1answer
553 views

MySQL: COLLATE performance

I have a table called users with a column firstname and with collation utf8_bin I want to know what happens under the hood when I execute an query like: SELECT * FROM `users` `a` WHERE `a`.`...
1
vote
1answer
86 views

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 ...
1
vote
2answers
94 views

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
1
vote
1answer
521 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 ...
1
vote
1answer
98 views

What is the best relationship model for high performance?

I want to model a relationship between Customer and Transaction. However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
1
vote
2answers
117 views

How can I improve this query

I have the following table: CREATE TABLE `twitter_relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `source_twitter_id` bigint(20) NOT NULL, `...
1
vote
1answer
45 views

Performance of BINARY vs CAST AS BINARY in MySQL

Using MySQL 5.6.23, I recently changed a prepared statement from: SELECT * FROM table WHERE BINARY column = :boundValue to: SELECT * FROM TABLE WHERE CAST(column AS BINARY) = :boundValue ...
1
vote
2answers
28 views

How can one identify if FK's needed to be indexed apart from the NC indexes already residing

I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done. I have a similar env where there ...
1
vote
2answers
171 views

mysqld is terminated because the system is low on memory; what to do?

I did recently a site migration; during that I had to erase all plugins and the reinstalling only the minimum as the site was causing the server to crash (A Vps with 1gb of ram), the most problematic ...
1
vote
1answer
87 views

Troubleshooting extremely long running query (SQL Server 2012)

We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours). A week ago, this same query completed in less than a minute. I've ...
1
vote
2answers
53 views

How to improve performance of a query with an IN subquery

Get the count of other users having the x tag with order_id in order_id of users having the y tag table: MyCustomTable (200K rows) order_id user_id type ('x' and 'y') my custom tags and the query:...
1
vote
1answer
27 views

index creation - Slow Query - PostgreSQL 9.2

I've got the following query: SELECT * FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
1
vote
1answer
44 views

Can the subquery be simplified/optimized?

Here's a simplified version of a query I run. I've removed a bunch of key/value pairs for simplicity. The constraint is that I can't change anything outside of the ARRAY function; but the ARRAY ...
1
vote
1answer
18 views

Joining on a M-N lookup table vs keyword searching a single column

I am in the process of writing an image gallery webapp similar to that of Danbooru, and in the process, decided to take a look at danbooru's database schema for an idea about how things are structured....
1
vote
1answer
92 views

Most efficient way of finding rows in a table that don't exist in another — SQL Server

What is the best method of finding rows in table A that don't exist in table B based on unique key of the tables, when both tables are huge (A over 300 million rows, B over 500 million rows) and the ...
1
vote
2answers
61 views

Check existence on other tables: performance advice

This is my first post. I have this data model: I want fetch some profile, with checking if they have any page or user assigned or not. I have tried multiple queries and finally came to these tows: ...
1
vote
1answer
112 views

Slower running Query with better Filter?

mysql> SELECT * FROM history_uint h WHERE h.itemid='1825689' ORDER BY h.clock DESC LIMIT 2 OFFSET 0 ; Empty set (0.00 sec) Explain: id: 1 ...
1
vote
1answer
139 views

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables. I was able to get the expected result on MySQL 5.5, but after ...
1
vote
1answer
223 views

Vertica performance on select from view with union

We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others. Now, we are doing a select from ...
1
vote
1answer
82 views

How to increase performance of UPDATE of ~ 50.000.000 entries

We have a query that uses a temporary table to update certain columns in the database: CREATE TABLE #NEW_OLD ( NEW nvarchar(20), OLD nvarchar(20) ); INSERT INTO #NEW_OLD VALUES ('DOMAIN\...
1
vote
1answer
49 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
1
vote
1answer
79 views

Decrease Query Execution Time

I have been having issues with 2 queries with query execution times up to 250 seconds for each query. The tables joined have each less than 10,000 rows, with exception to 2 tables that have around 60k ...
1
vote
1answer
513 views

MySQL performance issue - intermittently slow queries

I manage an intranet application used by around 50 staff in a few locations. The application is hosted on a Windows Server 2008 VM with 8GB RAM and MySQL (5.5) is running on the same server as IIS. I ...
1
vote
1answer
71 views

Optimize slow query

I have this query that takes over half an hour to run on our Linux MySQL db... SELECT ls.emailaddress , lse.subscriberid , lse.eventtype , count(lse.eventtype) as total FROM ...
1
vote
2answers
54 views

DB select query performance when queried by partial key of primary key [closed]

My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, ...
1
vote
1answer
75 views

Is it more efficient to union from sub-query or combine two outer queries?

I searched for this but couldn't find the response I needed. So my apologies if this has already been asked. I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just ...
1
vote
1answer
63 views

Update records from one table to another in different databases where condition is accomplish

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for ...
1
vote
1answer
98 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, ...
1
vote
1answer
85 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 | +---...
1
vote
1answer
972 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 ...
1
vote
1answer
98 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, ...
1
vote
2answers
145 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
100 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, ...
1
vote
1answer
210 views

Postgres - Insert performance with indexes

We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow ...
1
vote
1answer
132 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
1
vote
3answers
171 views

Special function slow in where but not in select

I have kind of a special problem using a levenshtein algorithm in MySql. But I don't think that it's special for levenshtein. This query: SELECT *,levenshtein(word,'Facbook') FROM Words WHERE length(...
1
vote
1answer
374 views

set global variable parameter?

My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time. I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need ...
1
vote
1answer
166 views

intermittent performance issue - query with multiple CTE and trashed execution plan

I'm trying to optimize a SQL query that shows variable performance (less than 2 to more than 15s). Overview This is a ColdFusion website, with a SQL Server 2008 R2 backend The query is part of the ...
1
vote
1answer
2k views

Optimizing Amazon RDS - MySQLTuner Recommendations / Feedback & Suggestions

I'm currently trying to optimize a database I've inherited for speed. The server is an Amazon RDS db.m3.large instance: 2 VCPUs 7.5 GB Memory 500 GB HDD My current strategy is to do the following:...
1
vote
1answer
105 views

Best approach to get last value inserted

I have several devices that report via Edge network their position (and more data) to a UDP server which directly inserts this data in a SQL 2008 r2. I want to know which is the best approach to ...
1
vote
1answer
64 views

Taking the difference in timestamps between successive entries

This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question. If this is not an appropriate question for DBA, let me know and I'll skulk away quietly... ...
1
vote
1answer
48 views

How to query a list of all the different errors?

I have a DB which is very big and stores a lot of eventlogs, around 15 million rows. When I try this query, it is totally impossible to get some results. Any idea how I could do this? Maybe indexed ...
1
vote
1answer
112 views

Summing a column based on uniqueness of another column without using temporary table

I have a table like this: CREATE TABLE `files` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `hash` char(40) DEFAULT NULL, `size` bigint(20) unsigned DEFAULT '0', ...
1
vote
1answer
462 views

Deciding which MySQL execution plan is better

Background: I have a query that I'm trying to speed up, involving 3 tables: omgenvelope has 5 million rows omgcust has 195 rows omginput has 35836 rows I'm obtaining the distinct omginputs ...
1
vote
2answers
160 views

Complicated data

I have a requirement i got from my father. He gave me a table and wanted me to digitalis it. So that he can the system can do all the dirty jobs for him ( that's why there are developers in the world, ...