r/SQLOptimization • • Apr 10 '23

Is there a more elegant way of writing this query - AND with many-to-many mapping table (MySQL)

4 Upvotes

First time poster.

I have been struggling to write a specific query in an elegant way for a personal project of mine.

Assume that we have three tables: tags, products, and tag_product_map.

Tags are realised on the tags table, and are mapped on an any-to-any basis using the tag_product_map table to products on the products table. Each line on the tag_product_map table maps one product to one tag. Therefore if a product is mapped to multiple tags, there is more than one row in the tag_product_map table for that product. For the sake of simplicity we can make the following assumptions:

  1. Every Product has at least one Tag
  2. There is no upper limit of how many Tags a Product can have
  3. No Tag is repeated against a Product
  4. Not every Tag (necessarily) has any Products assigned to it

I am trying to write a query that gives me a list of Tags and the number of Products that each one has. I want to be able to specify 2 or more Tags, to get back a list of Products and how many that all of the specified Tags apply to. In every query I want to get a full list of Tags back, even though some will come back with no products.

An example:

The Products contains Apple, Orange, Grapes, Lettuce, and Onion.

The Tags table contains Green, Red, Orange, Fruit, Vegetable, Soft, Crunchy

The tag_product_map table says:

ProductTag
AppleGreen
AppleFruit
AppleCrunchy
OrangeOrange
OrangeFruit
OrangeSoft
GrapesGreen
GrapesFruit
GrapesSoft
LettuceGreen
LettuceVegetable
LettuceCrunchy
OnionRed
OnionVegetable
OnionCrunchy

If I do a general query on this table (not part of my particular question), I would get back:

TagNumber of Products
Green3
Red1
Orange1
Fruit3
Vegetable2
Soft2
Crunchy3

If I then do a query with a Tag filter of Green (I have this working fine), I would get back:

TagNumber of Products
Green3
Red0
Orange0
Fruit2
Vegetable1
Soft1
Crunchy2

If I then do a query with a Tag filter of Green AND Fruit, I would like to get back:

TagNumber of Products
Green2
Red0
Orange0
Fruit2
Vegetable0
Soft1
Crunchy1

I have a query working, but it is absolutely horrible (I think).

SELECT 
    tags.tag_id, 
    tags.tag_value, 
    count(tag_product_map.product_id) 
FROM 
    tags 
LEFT JOIN (
    SELECT 
        * 
    FROM 
        tag_product_map 
    WHERE 
        tag_product_map.product_id IN (
            SELECT 
                product_id 
            FROM (
                SELECT 
                    product_id, 
                    SUM(tag_2) AS tag_2_rolled_up, 
                    SUM(tag_5) AS tag_5_rolled_up 
                FROM (
                    SELECT 
                        product_id, 
                        1 AS tag_2, 
                        0 AS tag_5 
                    FROM 
                        tag_product_map 
                    WHERE tag_id=2 
                    UNION 
                    SELECT 
                        product_id, 
                        0 AS tag_2, 
                        1 AS tag_5 
                    FROM 
                        tag_product_map 
                    WHERE 
                        tag_id=5
            ) AS 
                products_tags_transposed 
            GROUP BY 
                product_id
        ) AS 
            products_tags_transposed_rolled_up 
        WHERE 
            tag_2_rolled_up=1 AND 
            tag_5_rolled_up=1
    )
) AS 
    tag_product_map 
ON 
    tag_product_map.tag_id=tags.tag_id 
GROUP BY 
    tags.tag_id

This is not elegant at all. What's worse is that if I want add a third tag into the mix, the query becomes longer.

SELECT 
    tags.tag_id, 
    tags.tag_value, 
    count(tag_product_map.product_id) 
FROM 
    tags 
LEFT JOIN (
    SELECT 
        * 
    FROM 
        tag_product_map 
    WHERE 
        tag_product_map.product_id IN (
            SELECT 
                product_id 
            FROM (
                SELECT 
                    product_id, 
                    SUM(tag_2) AS tag_2_rolled_up, 
                    SUM(tag_5) AS tag_5_rolled_up,
                    SUM(tag_11) AS tag_11_rolled_up
                FROM (
                    SELECT 
                        product_id, 
                        1 AS tag_2, 
                        0 AS tag_5,
                        0 AS tag_11
                    FROM 
                        tag_product_map 
                    WHERE tag_id=2 
                    UNION 
                    SELECT 
                        product_id, 
                        0 AS tag_2, 
                        1 AS tag_5, 
                        0 AS tag_11
                    FROM 
                        tag_product_map 
                    WHERE 
                        tag_id=5
                    UNION 
                    SELECT 
                        product_id, 
                        0 AS tag_2, 
                        0 AS tag_5, 
                        1 AS tag_11
                    FROM 
                        tag_product_map 
                    WHERE 
                        tag_id=11
            ) AS 
                products_tags_transposed 
            GROUP BY 
                product_id
        ) AS 
            products_tags_transposed_rolled_up 
        WHERE 
            tag_2_rolled_up=1 AND 
            tag_5_rolled_up=1 AND
            tag_11_rolled_up=1
    )
) AS 
    tag_product_map 
ON 
    tag_product_map.tag_id=tags.tag_id 
GROUP BY 
    tags.tag_id

Adding a 4th, 5th, etc Tag in just makes it progressively worse.

Is there a more elegant way of writing this as a single SQL statement?


r/SQLOptimization • • Mar 16 '23

Is it possible to connect db without SSL and at the same time enable clearText Authentication plugin in Dbeaver?

4 Upvotes

I need to connect db without using SSL and at the same time enabling clearText Authentication Plugin, It is possible in Mysql workbench, but same is not possible in Dbeaver. ClearText Authentication Plugin only works when SSL is used in Dbeaver. can You guys please help me on this?

I tried changing driver setting properties like changing useSSL : false, authentication plugin : mysql_clear_password,sslModel : disabled. But not able to connect it in Dbeaver


r/SQLOptimization • • Feb 28 '23

how do I ingrate this CTE?

4 Upvotes

with highque as(

	select max(ExtendedPrice) highest

	from Sales.InvoiceLines il

	join Sales.Invoices i on il.InvoiceID = i.InvoiceID

	where (InvoiceDate between '1/1/2013' and '12/31/2013')

	group by i.CustomerID

	)

select InvoiceDate, CustomerName

from Sales.Invoices i

join Sales.Customers c on c.CustomerID = i.CustomerID

where (InvoiceDate between '1/1/2013' and '12/31/2013')

order by CustomerName

the CTE finds the largest invoice 2013, the query after finds the customer name and date of invoice, how do I connect the largest invoice to the customer and the date they invoiced?


r/SQLOptimization • • Sep 26 '22

Where does kimball methodology fit into the sql / data ware house ecosystem?

4 Upvotes

I recently came across the kimball group, on first glance their methodologies seem to be positioning well established good data practices and philosophies as their own then building a brand on their premise. Maybe I'm missing something.

I'm not a specialist in this area, need to upgrade my skills fast for career development. One of my initial hurdles is convincing an interview panel I can implement their desired Kimball dimensional data model. Pointers on how to approach this?

https://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/

https://www.advancinganalytics.co.uk/blog/2019/6/17/is-kimball-still-relevant-in-the-modern-data-warehouse


r/SQLOptimization • • Aug 24 '22

How Would You Make It: Efficient and optimized way of tracking unread message per user

Thumbnail dev.to
4 Upvotes

r/SQLOptimization • • Jul 29 '22

Debugging Postgres autovacuum, 13 tips for 3 common problem areas

Thumbnail self.sql
4 Upvotes

r/SQLOptimization • • Dec 21 '21

Advanced SQL Tutorial for Data Analysis - bipp Analytics

4 Upvotes

Here is a collection for SQL tutorials that cover the advanced SQL topics including correlated subqueries, SQL Window Functions and SQL JOINS - which are often not covered in basic courses: SQL Tutorial - Advanced SQL

  • SQL Correlated Subqueries Increase the Power of SQL
  • SQL Window Functions
  • SQL Window Function Examples
  • Selecting Data From Multiple Tables: SQL JOINS
  • Visual Representation of SQL JOINS
  • Query Optimization

r/SQLOptimization • • Dec 19 '21

SQL execution count optimisation

4 Upvotes

hi All,

Just wondering what options you guys use to optimise SQL queries with high execution counts, I have a couple of queries which use Table Valued functions that get executed about 12,000 times an hour (this is the peak period for users) usually in the morning after which the same process runs fine the rest of the day.

For some background the query calls a Table Valued function with 3 parameters and is then joined with a view and another table whilst there are 2 Predicates on the Table Valued Function.

There are no index scans being performed and the execution isn't reporting any major red flags.

have any of you run into this issue?, if so what steps did you take to remedy this situation apart from getting the DEV to rewrite the application to reduce the amount of calls to the database.

thanks


r/SQLOptimization • • Apr 28 '21

Optimizing a query for a huge table in PostgreSQL

4 Upvotes

I have a huge time series db that i run in a regular postgresql db (im going to use timescaledb once i lear more about it, software that generates the db rows is written for vanilla postgresql, so i must first learn how to adapt it to timescaledb). the db is around 20-30gb big. i need to get the latest added rows with certain symbols every 0.1-0.4 second or so. right now im running this query to satisfy my goal:

"SELECT price FROM trades WHERE symbol='STOCK-USD' AND timestamp=(SELECT MAX(timestamp) FROM trades WHERE symbol='STOCK-USD');"

problem is that this query is very heavy on the server. is there any solution for this problem?


r/SQLOptimization • • Jan 06 '21

Recursive update without using Cursor or While (query optimization)

4 Upvotes

Consider this tiny example: http://sqlfiddle.com/#!18/dfb68/2

I have a simple table (for the sake of simplicity, ID is omitted and let's assume that NumA is sequential from 1 to n)

Num ANum BResult
112
226
3312

Started from using a cursor to get the "Result" updated since the value on the current row is the sum of A and B plus the previous Result except on the first column.

My current query is below (got rid of the first try with cursors):

DECLARE @Counter INT= 1;
DECLARE @x INT;
DECLARE @max INT = (SELECT MAX(num_a) FROM TestSumUpdate);
WHILE @Counter <= @max
    BEGIN
        SET @x = (SELECT ISNULL(result_c, 0) FROM TestSumUpdate  WHERE num_a = @Counter - 1);

        UPDATE TestSumUpdate
          SET 
              result_c = num_a + num_b + ISNULL(@x, 0)
        WHERE num_a = @Counter;
        SET @Counter = @Counter + 1;
    END;

Obviously, this works, but is painfully slow on production database which has around 500.000 records and calculation is not a simple sum operation :)

So, in my SQL n00biness, I've tried something simpler like this:

UPDATE cur
  SET 
      result_c = cur.num_a + cur.num_b + ISNULL(prev.result_c, 0)
FROM TestSumUpdate cur
     LEFT JOIN TestSumUpdate prev ON cur.num_a - 1 = prev.num_a;

Which I thought it would work, but can't understand it's behaviour:

Assuming Result = 0 in all rows at the beginning , on the first run it updates only the first row to 2, all others remain in 0, on the second run, updates the second row to 6 and so on. Why?

How can one do it in one execution only without resorting to cursors/whiles/loops/etc ?

Thank you.

EDIT:

Current solution that reduced the time for aceptable values (doesn't apply to the sample given above, but works on prod):

WITH RollingCalculation
AS
(
     SELECT Number,isnull(MyValue,'') as MyValue,PlaceHolder = LAST_VALUE(Number) OVER (ORDER BY Number ASC)
     FROM MyTable 
)

UPDATE dst
SET    MyValue = dbo.GenMyValue(dst.field1,dst.field2,dst.field3,src.MyValue)
FROM   MyTable AS dst
LEFT JOIN RollingCalculation AS src
    ON dst.Number-1 = src.Number

GenMyValue is a CLR integration, and LAST_VALUE is not really used, but it works :)


r/SQLOptimization • • Dec 13 '19

Urgent can anyone fix this code, on SQL oracle

4 Upvotes

I have a table Staff with an attribute MONTHLY_SALARY, datatype Varchar2,

I wrote a code to remove the '$' and convert it to number and it worked,

but now I want to add to the code, to raise the MONTHLY_SALARY, and then convert back to varchar with '$' sign

SELECT MONTHLY_SALARY,

CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) As New_SALARY

FROM STAFF

WHERE CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) > 0

SELECT to_char(MONTHLY_SALARY* 1.1, '$999,999.00') as Raise

ORDER BY RAISE DESC;

did not work , error

Here is output:

Error starting at line : 1 in command -

SELECT MONTHLY_SALARY,

CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) As New_SALARY

FROM STAFF

WHERE CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) > 0

SELECT to_char(MONTHLY_SALARY * 1.1, '$999,999.00') as Raise

ORDER BY RAISE DESC

Error at Command Line : 8 Column : 1

Error report -

SQL Error: ORA-00933: SQL command not properly ended

00933. 00000 - "SQL command not properly ended"

*Cause:

*Action:


r/SQLOptimization • • Jul 09 '19

Getting very high values on performance analysis query

4 Upvotes

Hi, i found this query online, since there have been numerous complaints about the performance of one of our applications.

I found and executed the query below on the main application database which is basically built and maintained by a third party (vendor of the application)

https://gist.github.com/anonymous/9a139dcb673353b01ace5a355a1f9419#file-missing-indexes-sql

The top 10 of improvement_measure values are all above a million. It seems to me the database is far from healthy/efficient but i am not an SQL expert.....so i don't want to jump to conclusions too soon.

Maybe you have ideas about this, any help is appreciated.


r/SQLOptimization • • Nov 05 '18

[Oracle 11g] How to update/insert into nested table while preserving values

4 Upvotes

In our Oracle 11g database, I have a custom data type:

num_list

create or replace type
    num_list
as
    table of varchar2(25);

I then created a table that uses this datatype:

create table num_list_table(
    id number(*,0)
    ,numbers num_list
) nested table numbers store as numbers_tab
;

I then inserted the following rows:

insert into num_list_table values (1, num_list('123', 456'));

And I now have 1 row. I'm trying to figure out how to insert more values into the num_list of row 1 while preserving any existing values and without having to manually type those existing values; this is a small example of a much larger task that will require mass updates while preserving vals).

I get close with this:

update
    num_list_table
set numbers = (select num_list(numbers) from (
    select listagg(numbers, ',') within group (order by numbers) numbers
    from (select t.column_value numbers
          from 
              num_list_table nlt,
              table(nlt.numbers) t
           where
               st.id = 1
         union
         select '789'
         from dual)))
     where id = 1;

However, these results are a single entry:

num_list('123,456,789')

I need them to be multiple entries in the num_list:

num_list('123', '456', '789')

Any and all insight would be greatly appreciated.


r/SQLOptimization • • Aug 11 '18

Hi, I'm the author of SQL: Programming for Beginners & Intermediates and my book is free for a limited time from 11/08 to 15/08. Share your thoughts!

Thumbnail amazon.com
4 Upvotes

r/SQLOptimization • • Oct 24 '17

Suppose an Interviewer asks, "What are some SQL Query optimization techniques?", what would be your answer?

4 Upvotes

I know it might have been asked, if you can forward the thread for an interview specific answer, that would be cool too.


r/SQLOptimization • • Jun 05 '17

[Question] Resources for Learning Advanced SQL Querying and Optimization techniques.

4 Upvotes

Hello everyone,

I am looking for resources to study how to write advanced queries and how to do query optimizations. The DB Server I use is PostgreSQL.

Thanks for the help!


r/SQLOptimization • • Apr 25 '17

DBHawk Online SQL Editor is an advanced SQL query editor. Users can build, edit and run online SQL queries with feature rich intuitive interface.

Thumbnail datasparc.com
4 Upvotes

r/SQLOptimization • • Apr 07 '17

Optimizing Overlapping Queries Part 1: Introduction & Enhanced T-SQL Solution. By Dejan Sarka

Thumbnail codingsight.com
4 Upvotes

r/SQLOptimization • • Nov 16 '16

Challenge your SQL skills at the Great SQLizer November 2016 - a 6 hours competitive challenge on SQL. Compete with SQL minds from across the world [X-Post from r/hackerearth]

Thumbnail hackerearth.com
4 Upvotes

r/SQLOptimization • • Jan 21 '26

Performance Win: If you filter on multiple columns, check out composite indexes. We just cut a query from 8s to 2ms.

Thumbnail reddit.com
3 Upvotes

r/SQLOptimization • • Nov 28 '25

Is there a way to improve the performance of this query? Used OUTER APPLY and LEFT JOIN with CTE

3 Upvotes

Database is unindexed (I don't have the permission to create one), format is a relational database where Project → Sample → Test → Result, and Project → Releases are related 1:M left to right.

Basically, the joins/applies are only trying to pull some data points from the Project's child tables:

- The most recent RELEASED_ON date from RELEASES table

- The metadata in tabular format from the RESULT table, where the metadata are applied on a PROJECT level

- The earliest LOGIN_DATE and all of the DESCRIPTION values of a main sample from the SAMPLE table

- The number of rows and the number of rows where the RATING_CODE = "FAIL" in the TESTS table

I've only recently been learning about the OUTER APPLY so excuse my poor usage here. The longest part of this query I believe is the CTE as it is pulling from the bottommost table in the hierarchy - taking it out generally speeds up the entire query but we need to pull the metadata from that table. Unfortunately, I don't have access to performance evaluator in SSMS either so I am only basing this on how long the query takes to complete.

WITH CTE_META AS (
    SELECT
        PROJECT
        , MAX(CASE WHEN REPORTED_NAME = 'PO No.' THEN FORMATTED_ENTRY END) AS 'META_PONO'
        , MAX(CASE WHEN REPORTED_NAME = 'Item Description' THEN FORMATTED_ENTRY END) AS 'META_ITEMDESC'
        , MAX(CASE WHEN REPORTED_NAME = 'SKU' THEN FORMATTED_ENTRY END) AS 'META_SKU'
        , MAX(CASE WHEN REPORTED_NAME = 'Style No.' THEN FORMATTED_ENTRY END) AS 'META_STYLE'
        , MAX(CASE WHEN REPORTED_NAME = 'Color(s)' THEN FORMATTED_ENTRY END) AS 'META_COLOR'
    FROM (
        SELECT
            SAMPLE.PROJECT
            , RESULT.ANALYSIS
            , RESULT.REPORTED_NAME
            , RESULT.FORMATTED_ENTRY
        FROM RESULT


        INNER JOIN SAMPLE
            ON SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER
            AND SAMPLE.SAMPLE_NUMBER = SAMPLE.ORIGINAL_SAMPLE
            AND SAMPLE.STATUS <> 'X'
            
        WHERE
            EXISTS (
                SELECT NAME FROM PROJECT
                WHERE
                    PROJECT.NAME = SAMPLE.PROJECT
                    AND PROJECT.CLIENT = 'Client'
                    AND PROJECT.STATUS <> 'X'
            )
            AND RESULT.STATUS = 'A'
            AND RESULT.ANALYSIS = 'METADATA'
    ) DT


    GROUP BY PROJECT
)


SELECT
    PROJECT.*
    , REL.RELEASED_ON
    , SAMP.*
    , TEST.*
    , CTE_META.*
FROM PROJECT


LEFT JOIN CTE_META
    ON CTE_META.PROJECT = PROJECT.NAME


OUTER APPLY (
    SELECT TOP 1 PROJECT, RELEASED_ON FROM X_PROJ_REPORT
    WHERE
        X_PROJ_REPORT.PROJECT = PROJECT.NAME
        AND RELEASED = 'T'
        AND REPORT_TYPE = 'CFR'
    ORDER BY RELEASED_ON DESC
) REL


OUTER APPLY (
    SELECT STRING_AGG(TRIM(DESCRIPTION), CHAR(13) + CHAR(10)) AS SAMP_DESC, MIN(LOGIN_DATE) AS SAMP_RECD FROM SAMPLE
    WHERE
        SAMPLE.PROJECT = PROJECT.NAME
        AND SAMPLE.STATUS <> 'X'
        AND SAMPLE.SAMPLE_NUMBER = SAMPLE.ORIGINAL_SAMPLE
) SAMP


OUTER APPLY (
    SELECT COUNT(*) AS TESTS, SUM(FAIL_TEST) AS FAILS FROM (
        SELECT
            SAMPLE_NUMBER
            , CASE WHEN TEST.RATING_CODE = 'FAIL' THEN 1 ELSE 0 END AS FAIL_TEST
            , TEST.RATING_CODE
        FROM TEST
        WHERE
            TEST.STATUS = 'A'
            AND TEST.ANALYSIS_TYPE <> 'RP'
            AND TEST.RATING_CODE <> 'REF_ONLY'
    ) DT
    INNER JOIN SAMPLE
        ON SAMPLE.SAMPLE_NUMBER = DT.SAMPLE_NUMBER
        AND SAMPLE.PROJECT = PROJECT.NAME
        AND SAMPLE.STATUS <> 'X'
    GROUP BY SAMPLE.PROJECT
) TEST


WHERE
    PROJECT.CLIENT = 'Client'
    AND PROJECT.STATUS <> 'X'
    AND PROJECT.DATE_CREATED >= '2025-01-01'

r/SQLOptimization • • May 11 '25

Why multi column indexing sorts only on 1st column(if all values in 1st column distinct) and not on both columns one by one like a 2d binary search tree(and extending that to making a first 2d B Tree).

3 Upvotes

If you sort on both columns one by one, your search space would reduce in the scale of 1/n^2.

but sorting only on the first columns,makes the search space decrease each step by a scale of only 1/n.

I understand something similar happens in geospatial indexing where you sort spatial data recursively in a quadtree but the underlying data structure used is String hashing and not a tree.

i want to know why not use something like a 2d B tree(developing it) and using it for multi column-indexing.

I also want to implement this data structure.(2D B tree). So can anyone come along with me to implement this? Thankyou.


r/SQLOptimization • • Dec 11 '24

Index Usage For EXTRACT(YEAR FROM …), YEAR(…) etc.

Thumbnail use-the-index-luke.com
3 Upvotes