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.

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"
        );
    }

CodePudding user response：

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);

CodePudding user response：

As I understand your question, you want records for which another record exists with the same user name and an overlapping period of time.

I would recommend exists and a few inequality conditions:

select *
from mytable t1
where exists (
    select 1
    from mytable t2
    where t1.id != t.id
      and t1.username = t2.username
      and t1.acctstarttime <= t2.acctstoptime 
      and t2.acctstarttime <= t1.acctstoptime
)

For each row, the subquery searches for an "overlapping" record of the same user, and filters in rows that match. The inequality conditions on the start and end time qualify the date range overlap.

An index on (id, acctstarttime, acctstoptime) might help performance.