Self join query is too slow

Why not using eloquent relationships?

Querying speed depends on how you database is organized. By indexing contact_type it could speed up a bit.

Do you have Models set up? This could be achieved using Eloquent without having to do a massive SQL join (which is probably what's taking the time, but without a similar recordset I can't tell you for sure).

What are you trying to achieve? Some background of the business logic you're trying to implement will help with understanding possible solutions.

Firstly self join queries are very expensive even in Oracle database, in your sql statement above I see you are duplicating join clause

$join->on('uc1.user_id', '=', 'uc2.contact_id'); 

$join->on('uc1.contact_id', '=', 'uc2.user_id');

you need only one join parent to child or child to parent not BOTH

self join queries are a bit complicated than regular queries to achieve desired results you need to determine starting point of join and in witch direction the comparison will be done, unfortunately these are not supported in all RDBMS but in Oracle database you can specify what you want to do

Ex.

     SELECT col, col, col, LEVEL

     FROM table

     START WITH col = {value}

     CONNECT BY PRIOR col = col

this syntax is only available in oracle database I did not see any similarities in other RDBMS

@yaserselim Thank you for reply, yes true for self-join I needed two way join because of business logic. I will rewrite the code.

@mikemacdowell Table structure ~~~ id, user_id, contact_id (user_id, contact_id both are id in user table) ~~~

Now, if user 2, 3 are connected then table entries seems like

id       user_id   contact_id
Auto      2                3
Auto      3                2

so getting all records with dual connection entries

yes,.. eloquent relationship would be better on that.

Hello @prex016 can you provide table structure and what you want to do!

@y Table structure ~~~ id, user_id, contact_id (user_id, contact_id both are id in user table) ~~~

Now, if user 2, 3 are connected then table entries seems like

id       user_id   contact_id
Auto      2                3
Auto      3                2

so getting all records with dual connection entries

Hi @prex016 why you have tow entries if user 2 and user 3 are connected then one entry is enough to assert that they are connected? am I right?

any way if you want return connected rows you can use this sql statement

select * from usr_contacts as s1 where s1.user_id
 in(select s2.contact_id from usr_contacts as s2 where  s2.user_id = s1.contact_id )

I hope this would solve the problem otherwise email me the problem in details

Cheers