Optimize history.get_last_state_changes query

[bdraco]

This fix is unvalidated and needs a dump of the database from #81989

It should move to the next milestone is its not been validated by the time the patch release is shipping.

Proposed change

history.get_last_state_changes has been optimized for single entity selects.

Previously it allowed passing in None for the entity but that could have resulted in selecting the entire database. Nothing should have been doing that in practice so I didn't mark this as a breaking change as anything doing that was already quite broken.

Type of change

• [ ] Dependency upgrade
• [ ] Bugfix (non-breaking change which fixes an issue)
• [ ] New integration (thank you!)
• [ ] New feature (which adds functionality to an existing integration)
• [ ] Deprecation (breaking change to happen in the future)
• [ ] Breaking change (fix/feature causing existing functionality to break)
• [x] Code quality improvements to existing code or addition of tests

Additional information

• This PR fixes or closes issue: fixes #81989
• This PR is related to issue:
• Link to documentation pull request:

Checklist

• [x] The code change is tested and works locally.
• [ ] Local tests pass. Your PR cannot be merged unless tests pass
• [ ] There is no commented out code in this PR.
• [ ] I have followed the development checklist
• [ ] The code has been formatted using Black (black --fast homeassistant tests)
• [ ] Tests have been added to verify that the new code works.

If user exposed functionality or configuration variables are added/changed:

• [ ] Documentation added/updated for www.home-assistant.io

If the code communicates with devices, web services, or third-party tools:

• [ ] The manifest file has all fields filled out correctly.
  Updated and included derived files by running: python3 -m script.hassfest.
• [ ] New or updated dependencies have been added to requirements_all.txt.
  Updated by running python3 -m script.gen_requirements_all.
• [ ] For the updated dependencies - a link to the changelog, or at minimum a diff between library versions is added to the PR description.
• [ ] Untested files have been added to .coveragerc.

To help with the load of incoming pull requests:

• [ ] I have reviewed two other open pull requests in this repository.

[home-assistant[bot]]

Hey there @home-assistant/core, mind taking a look at this pull request as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of recorder can trigger bot actions by commenting:

• @home-assistant close Closes the issue.
• @home-assistant rename Awesome new title Change the title of the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign recorder Removes the current integration label and assignees on the issue, add the integration domain after the command.

[bdraco]

Its stuck restoring the env

Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec Received 340880014 of 345074318 (98.8%), 0.2 MBs/sec

[bdraco]

The mariadb test is failing because point and point2 are the exact same time and since it now uses the index as expected it undefined which one will come first when there are two exact same times. MariaDB happens to use the insertion order where-as sqlite uses the last insertion order.

        point = start + timedelta(minutes=1)
        point2 = point + timedelta(minutes=1)
    
        with patch(
            "homeassistant.components.recorder.core.dt_util.utcnow", return_value=start
        ):
            set_state("1")
    
        states = []
        with patch(
            "homeassistant.components.recorder.core.dt_util.utcnow", return_value=point
        ):
            states.append(set_state("2"))
    
        with patch(
            "homeassistant.components.recorder.core.dt_util.utcnow", return_value=point2
        ):
            states.append(set_state("3"))

[bdraco]

~~This fix is unvalidated and needs a dump of the database from #81989~~

~~It should move to the next milestone is its not been validated by the time~~ ~~the patch release is shipping.~~

This fix has now been validated