0

I have a problem about sql performance, my db is too many rows, so this make long time to query.

SELECT * FROM A JOIN B ON A.id = B.id where ...

So I change to

SELECT * FROM A where A= a...
SELECT * FROM B where B= b...

I got 2 resultSet from 2 query here. Can someone help me how to join 2 resultset with the best performance. I have to split to 2 query because this database have 10 mil records.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Việt Phú
  • 31
  • 2
  • 4
    It is unlikely that you can write a better join than the join implementation in your database. – Andreas Mar 11 '19 at 03:43
  • 1
    If you want help tuning a SQL query, you should ask on https://dba.stackexchange.com/, where you can ask about [*Advanced Querying* including window-functions, dynamic-sql, and **query-performance**](https://dba.stackexchange.com/help/on-topic). – Andreas Mar 11 '19 at 03:46
  • I can parse 2 resultset to hashmap and join with the key, O(n) will be 2n, better than join sql n^2 – Việt Phú Mar 11 '19 at 03:51
  • SQL engine can do hash join, merge joins, nested loop joins, etc. and they are likely better optimized too. All you need is the ability to tune the SQL. – Andreas Mar 11 '19 at 16:05
  • I'm using mysql, as i know, mysql doesnt support hash join :( – Việt Phú Mar 12 '19 at 06:27
  • Now that is important information, don't you think? Edit the question and say that, e.g. by tagging with `mysql`. See also: [Is there any way to force MySQL use Hash Join instead of Nested Loop Join?](https://dba.stackexchange.com/q/43439/127602) – Andreas Mar 12 '19 at 14:51

1 Answers1

-1
Select col1, col2 ...
from 
    ( 
    -- first query
    ) as tab1 
join 
    (
    -- second query
    ) as tab2 on tab1.colx = tab2.coly
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
umair
  • 525
  • 5
  • 18
  • 1
    Why would that make a difference? – Andreas Mar 11 '19 at 03:44
  • i don't think that it will give the huge performance improvement, but when we joining the small stables,i thought it may give somewhat better performance. because here we perform the query to select the only relevant rows using where clause in each table separately and then perform join. this is better than joining all the rows first and using where clause to filter it out. please correct me if i'm wrong. – umair Mar 11 '19 at 04:15