Stack Overflow
Stack Overflow

Questions tagged [sql-optimization]

SQL Optimization refers to the process of testing and debugging SQL servers and queries in order to increase overall performance.

SQL Optimization refers to the process of testing and debugging SQL databases, servers and queries in order to increase the speed and performance and to reduce used resources.

Usually, SQL optimization can refer to:

  • SQL Query optimization, where the SQL queries are being optimized internally in order to be optimal and to use as little resources as possible.
  • Database (schema) optimization, where the database or schema itself is being optimized in order to minimize redundancy. In literature, this is also referred to as normalization.
  • SQL Server optimization, where server configuration is being modified in order to be optimal for the needs of the used application(s).
295 questions
5
votes
2 answers

How do I select a random record efficiently in MySQL?

mysql> EXPLAIN SELECT * FROM urls ORDER BY RAND() LIMIT 1; +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref…
user198729
  • 61,774
  • 108
  • 250
  • 348
5
votes
1 answer

MySQL query optimization: how to optimize voting calculations?

Hope you're doing fine. I need a help a bit with this database: This is a database that stores votes. Users pick the audio tracks they like, and they vote for them. They can vote 'up' or 'down'. Easy as pie. But, when it comes to the calculating…
Aleksandr Makov
  • 2,820
  • 3
  • 37
  • 62
4
votes
4 answers

Slow SQL query, not sure how to optimize

So I have to deal with a database that has no indexes (not my design, it frustrates the hell out of me). I'm running a query that takes approximately three seconds to return, and I need it to be faster. Here are the relevant tables and…
Jon Martin
  • 3,252
  • 5
  • 29
  • 45
4
votes
5 answers

Very slow stored procedure

I have a hard time with query optimization, currently I'm very close to the point of database redesign. And the stackoverflow is my last hope. I don't think that just showing you the query is enough so I've linked not only database script but also…
Lu4
  • 14,873
  • 15
  • 79
  • 132
4
votes
1 answer

Order of table joining in SparkSQL for better performance

I am new to Spark-SQL to read Hive tables. I want to know that how does Spark performs a multi-table Join. I read somewhere that it is recommended to always keep the largest table on the top of the Join order and so on, which is conducive for Join…
marie20
  • 723
  • 11
  • 30
4
votes
2 answers

Optimization of search on concatenated firstname and lastname in PostgreSQL

I've written a SQL query in Postgres which search for a user by both firstname and lastname. My question is simply if it can be optimized, since it will be used a lot. CREATE INDEX users_firstname_special_idx ON users(firstname…
Cyberlurk
  • 766
  • 3
  • 9
  • 30
4
votes
1 answer

MySQL - "select" query inside IN (...) optimization issue (=> Hierarquical query )

Before you start down voting and deny me an answer, please note that I am a complete beginner :). I already searched for an answer but it seems to be quite specific. (how I understand it as a programmer, if the select statements where for loops, it…
hahaha
  • 1,001
  • 1
  • 16
  • 32
4
votes
3 answers

Has anyone ever successfully make index merge work for MySQL?

Setup: mysql> create table t(a integer unsigned,b integer unsigned); mysql> insert into t(a,b) values (1,2),(1,3),(2,4); mysql> create index i_t_a on t(a); mysql> create index i_t_b on t(b); mysql> explain select * from t where a=1 or…
user198729
  • 61,774
  • 108
  • 250
  • 348
4
votes
1 answer

MySQL log-queries-not-using-indexes and UNION false positive or error?

Am activated slow queries log with this parameters: log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 log-queries-not-using-indexes Now it seems that MySQL is logging only query without index this is a sample of log # Time:…
Zauker
  • 2,344
  • 3
  • 27
  • 36
4
votes
1 answer

Optimize SQL with subquery in from

I would like to optimize a SQL statement, below is the original one. SELECT DISTINCT p.productID, p.filename, p.model, p.code, p.manufacturerID, f2.manufacturerName, m.manufacturerName, CONCAT('INSERT INTO…
user529543
4
votes
2 answers

How to make PostgresQL optimizer to build execution plan AFTER binding parameters?

I'm developing Pg/PLSQL function for PostgresQL 9.1. When I use variables in a SQL query, optimizer build a bad execution plan. But if I replace a variable by its value the plan is ok. For instance: v_param := 100; select count(*) into result …
a.oberon
  • 43
  • 1
  • 4
3
votes
2 answers

oracle order by runs terribly slow

i'm doing a query like this one and it takes 6 seconds to complete : select * from ( select aaa."ID" from "aaa" where aaa."DELETED" is null order by aaa."CREATED" desc ) where rownum <= 15; I've got about 1.6 million records…
Yervand Aghababyan
  • 1,100
  • 1
  • 18
  • 39
3
votes
1 answer

Using hints in oracle

I am trying to apply hints to my query but the explain plan does not change to the hint being used. my query select/*+ USE_HASH(master_flight)*/ bid, b.fno, seat, flight_date from master_booking b, master_flight f where b.fno = f.fno and rownum…
paktrick
  • 93
  • 2
  • 8
3
votes
3 answers

Optimizing IN clause

I am using Oracle 10g. Here is my query select * from Entries where RefKey in (select RefKey from Entries where KeyStat = 1) and RefKey = Key; Here RefKey, Key and KeyStat all are indexed. The table is…
bjan
  • 2,000
  • 7
  • 32
  • 64
3
votes
2 answers

how to optimize several "WHERE (Select .... ) = value" from same table

It's hard to compose a topic name for me. But I can show an example : WHERE (SELECT [ID_Line] FROM [Event] WHERE [Event].[Name] = [A].[Col]) = 2 AND (SELECT [DataType] FROM [Event] WHERE [Event].[Name] = [A].[Col]) = 2 Here I'm processing 2…
cnd
  • 32,616
  • 62
  • 183
  • 313
1
2
3
…
19 20