Optimize query and subquery

Question

Currently i have this:

insert into temp select  * from myTable where (called_phone in
(
    select number1 from
    (
        SELECT *  FROM
        (
            SELECT called_phone as number1, count(*) as conto
            FROM myTable
            GROUP BY called_phone

        ) AS subquery
        union 
        SELECT * FROM
        (
            SELECT calling_phone as number1, count(*) as conto
            FROM myTable
            GROUP BY calling_phone

        ) AS subquery1
    )as subquery3
    GROUP BY number1
    having sum(conto) > 4000
))
or
(calling_phone in 
    (
        select number1 from
        (
            SELECT *  FROM
            (
                SELECT called_phone as number1, count(*) as conto
                FROM myTable
                GROUP BY called_phone

            ) AS subquery
            union 
            SELECT * FROM
            (
                SELECT calling_phone as number1, count(*) as conto
                FROM myTable
                GROUP BY calling_phone

            ) AS subquery1
        )as subquery3
        GROUP BY number1
        having sum(conto) > 4000
    )
);

i have 2 columns (called and calling phone) in which i must check all the occurrency for each number, and store in another table, all the numbers that have the count(*) > 4000 summing the occurence in both the columns. Problem with this query is that i'm doing 2 times the subquery which, by itself scan 2 times MyTable. I was thinking to store the subquery in a temporary table, and then scan it. Is this the best approach? What would you suggest?

EDIT : i'm using MySQL 5.7 with MyISAM as engine

EDIT2 : tried this:

create table test (`number1` VARCHAR(255) not NULL, primary key (`number1`));

insert into test(number1) select number1 from 
    (
        SELECT *  FROM
        (
            SELECT called_phone as number1, count(*) as conto
            FROM myTable
            GROUP BY called_phone

        ) AS subquery
        union 
        SELECT * FROM
        (
            SELECT calling_phone as number1, count(*) as conto
            FROM myTable
            GROUP BY calling_phone

        ) AS subquery1
    )as subquery3
    GROUP BY number1
    having sum(conto) > 4000;



insert into temp select  * from myTable where (called_phone in
(
    select number1 from test
    ))
or
(calling_phone in 
    (
        select number1 from test
));

drop table test;

But this is much more slower (at least on my test data, which is a table of ~14 records)

first approach takes 350ms to 380 ms
second approach take 800ms to 1.8s

FINAL EDIT : I'm writing the query that is producing the best results, and it comes from the answer of @Daniel E.

insert into temp
SELECT t1.* myTable t1 
INNER JOIN 
 (
   select number1 from
        (
            SELECT *  FROM
            (
                SELECT called_phone as number1, count(*) as conto
                FROM myTable
                GROUP BY called_phone

            ) AS subquery
            union 
            SELECT * FROM
            (
                SELECT calling_phone as number1, count(*) as conto
                FROM myTable
                GROUP BY calling_phone

            ) AS subquery1
        )as subquery3
        GROUP BY number1
        having sum(conto) > 4000
 ) t2 ON (t2.number1 = t1.called_phone
        OR 
         t2.number1 = t1.calling_phone)

first approach takes 350ms to 380 ms
second approach take 800ms to 1.8s
last approach take 315 to 335 ms

Unfortunately i had some problem with mu test data. I need to test them on a bigger table. But i need to finish other stuff first — Daniele Sartori, Feb 28 '18 at 16:39

Daniel E. · Accepted Answer · 2018-02-26T16:56:16.907

1

As you seems to understand the Insert Into I will just rewrite the select, instead of using a IN and a OR, I use 1 inner join :

SELECT t1.* FROM myTable t1 
INNER JOIN 
 (
   select number1 from
        (
            SELECT *  FROM
            (
                SELECT called_phone as number1, count(*) as conto
                FROM myTable
                GROUP BY called_phone

            ) AS subquery
            union 
            SELECT * FROM
            (
                SELECT calling_phone as number1, count(*) as conto
                FROM myTable
                GROUP BY calling_phone

            ) AS subquery1
        )as subquery3
        GROUP BY number1
        having sum(conto) > 4000
 ) t2 ON (t2.number1 = t1.called_phone
        OR 
         t2.number1 = t1.calling_phone)

edited Feb 26 '18 at 16:56

answered Feb 26 '18 at 10:25

Daniel E.

2,440
1
14
24

When i combine both Inner join from your answer i get an empty table – Daniele Sartori Feb 26 '18 at 10:33
i think it's because (but i might be wrong) that you will never find an intersection between the 2 join. The number will be present in only 1 column every time, it can't be in both. What i need to do is to get all the numbers that have more then 4000 (this number is not really important at the moment) occurrence summing all the occurrence in both columns – Daniele Sartori Feb 26 '18 at 11:12
It works, but seems that my original query is still the fastest approach. You query take approximately 520 ms while mine 370 ms. I guess that i can't do much better then what i've already done – Daniele Sartori Feb 26 '18 at 13:17
I found that if you remove distinct is better. I'm marking your answer as the accepted one, but you should modify it – Daniele Sartori Feb 26 '18 at 13:22
A "or" in a Join is quite expensive, that's why I am not satisfy with my answer but I don't know what we can do. Not sure you should accept my answer. – Daniel E. Feb 26 '18 at 16:42
well it's the best answer so far, slightly better then my current query. I will have too see how it performs on huge tables – Daniele Sartori Feb 26 '18 at 16:44
All your test are with SQL_no_cache ? – Daniel E. Feb 26 '18 at 16:45
no i was testing them on DBeaver like i would implement them in my code – Daniele Sartori Feb 26 '18 at 16:50
Add it in the query to test all solutions : SELECT SQL_NO_CACHE .... because if you don't mysql may use some cache and the time won't be relevant – Daniel E. Feb 26 '18 at 16:55
thank you for your suggestion. I have now enough elements to proceed in my tests. I have builted 3 different query, and i will test them as soon as possible – Daniele Sartori Feb 26 '18 at 16:58

score 0 · Answer 2 · answered Feb 26 '18 at 10:14

0

You could use the WITH AS ... strategy which is often used in recursive statements.

(Please forgive me for wrong intendation or braces)

       with numbersRelation as (
            select number1 from
            (
                SELECT *  FROM
                (
                    SELECT called_phone as number1, count(*) as conto
                    FROM myTable
                    GROUP BY called_phone

                ) AS subquery
                union 
                SELECT * FROM
                (
                    SELECT calling_phone as number1, count(*) as conto
                    FROM myTable
                    GROUP BY calling_phone

                ) AS subquery1
            ) as subquery3
            GROUP BY number1
            having sum(conto) > 4000
        )
        insert into temp select  * from myTable where (called_phone in numbersRelation or
        calling_phone in numbersRelation)

answered Feb 26 '18 at 10:14

emmics

994
1
11
29

3

MySQL does not support this syntax, at least probably not the version the OP is using. – Tim Biegeleisen Feb 26 '18 at 10:15
yeah in fact i'm having an error. Editing the main post cause i forgot to specify my engine and mysql version – Daniele Sartori Feb 26 '18 at 10:17
@TimBiegeleisen Really?? What exactly does not work with it? Can you only use select's on "with ..." tables? – emmics Feb 26 '18 at 10:23
1

Try running your query on MySQL. Did you test your code before you posted it? – Tim Biegeleisen Feb 26 '18 at 10:25
@TimBiegeleisen Currently, I'm not running any database program on my machine, so no, I'm sorry I didn't. I was just wondering why I could not use it or rather what exact part is invalid syntax for MySQL. – emmics Feb 26 '18 at 10:28
2

with is not a valid syntax for MySQL 5.7. I think it is added in MySQL 8.0 – Daniele Sartori Feb 26 '18 at 10:43

areklipno · Answer 3 · 2018-02-26T11:52:45.113

-1

try:

insert into temp 
select  * from myTable where called_phone in (
    select number1 from (
        SELECT called_phone as number1
        FROM myTable
        union all
        SELECT calling_phone as number1
        FROM myTable ) subquery
    GROUP BY number1
    having count(number1) > 4000 )

union 

select  * from myTable where calling_phone in (
    select number1 from (
        SELECT called_phone as number1
        FROM myTable
        union all
        SELECT calling_phone as number1
        FROM myTable ) subquery
    GROUP BY number1
    having count(number1) > 4000 )

or maybe faster will be:

insert into tabletemp 
    select number1 from (
        SELECT called_phone as number1
        FROM myTable
        union all
        SELECT calling_phone as number1
        FROM myTable ) subquery
    GROUP BY number1
    having count(number1) > 4000 ) ;

insert into temp 
select  * 
from myTable as mt 
join tabletemp tbt on (mt.called_phone = tbt.number1 or mt.calling_phone = tbt.number1);

drop table tabletemp;

edited Feb 26 '18 at 11:52

answered Feb 26 '18 at 10:18

areklipno

538
5
11

I don't get it. You just cut a piece of my query. I need to take all the numbers in called AND calling. That's why i have an OR and that is where my problem is – Daniele Sartori Feb 26 '18 at 10:23
sorry I make mistake - I edit my answer and now probably is better – areklipno Feb 26 '18 at 10:57
this is not working. My subquery is correct, while your is returning an empty result – Daniele Sartori Feb 26 '18 at 11:09
i appreciate your effort, now it's working, but your query is 2-3 times slower then mine. And i'm testing it in a very small db (~14k records in myTable) – Daniele Sartori Feb 26 '18 at 11:29
look at this [that's the reason for all the select *](https://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why) – Daniele Sartori Feb 26 '18 at 11:33
if you can specify columns then query execute faster - "select column1, column2 from myTable where......" – areklipno Feb 26 '18 at 11:42
I add another way to rebuild this query - maybe that will be faster – areklipno Feb 26 '18 at 11:53
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/165824/discussion-between-areklipno-and-daniele-sartori). – areklipno Feb 26 '18 at 12:12

Optimize query and subquery

3 Answers3