How can i query the dates between 2 columns with same username in mysql

Question

I have a mysql table which has 'username', 'acctstarttime' and 'acctstoptime'(and an 'id') datetime columns. I need to get records that its acctstarttime or acctstoptime between other 'acctstarttime' and 'acctstoptime' records with same username. For example 'John' has 3 records.

id	username	acctstarttime	acctstoptime
1	John	29.12.2022	01.01.2023
2	John	30.12.2022	03.03.2023
3	John	12.12.2022	14.12.2022

Second rows acctstarttime is between first rows acctstarttime and acctstoptime and first rows acctstoptime is between second rows acctstarttime and acctstoptime. I want to query that 2 records.

I can do it with php but it takes about 3-4 days or more for 1 million records. I have very inefficient function.

How can i reduce this time in mysql or php(just speed up)?

I downloaded each record to different files based on username. I wrote this laravel code but it works too slow. It can be confusing.

    function findRecordsBetweenSameHours()
    {
        $directory = storage_path() . "/app/";
        $files = Storage::files('usernames');
        foreach ($files as $file) {
            $records =json_decode(file_get_contents($directory . $file), true);
            foreach ($records as $record) {
                $record["acctstarttime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record["acctstarttime"]);
                $record["acctstoptime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record["acctstoptime"]);
                foreach ($records as $record2) {
                    $record2["acctstarttime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record2["acctstarttime"]);
                    $record2["acctstoptime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record2["acctstoptime"]);
                    if (
                        ($record2["acctstoptime"]->between($record["acctstarttime"], $record["acctstoptime"], false)
                            || $record2["acctstarttime"]->between($record["acctstarttime"], $record["acctstoptime"], false)
                        )
                        && $record2["acctsessionid"] != $record["acctsessionid"]
                ) {
                        Storage::append('x.log',
                            $record["acctsessionid"] . " - " . $record2["acctsessionid"] . " - " . $record["username"]
                        );

                    }
                }
            }
        }
        Storage::append('x.log',
            "finish"
        );
    }

Answer 1

You can write query like this

SELECT 
    t1.*
FROM
    table_name t1
        JOIN
    table_name t2 ON t1.username = t2.username
WHERE
    (t1.acctstarttime > t2.acctstarttime
        AND t1.acctstarttime < t2.acctstoptime)
        OR (t1.acctstoptime > t2.acctstarttime
        AND t1.acctstoptime < t2.acctstoptime);