1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Optimising query for date based selecting

Discussion in 'Databases' started by mburdett555, Jul 21, 2019.

  1. #1
    I am looking to speed up querying of records from a specific date range.
    SEMrush
    My table has millions of rows and the dates are in Unix epoch format, in text format IIRC ( i don't work weekends so can't be certain)

    I am pulling in a single schedule record, which has an unique ID field attributed to it.

    This ID field is recognised in a separate table called movement, and this movement field stores movement and timing data for the schedule.

    Each schedule has its associated movement rows (usually around 20 per schedule) in said movements table. I have around 500,000 schedule rows, each with a varying number of movement rows attributed to it.

    As I store more and more historic scheduling data, the movement data backs up and becomes huge. What I am looking for is the best way to fetch this movement data.

    The PHP website I am running would have a date variable in the URL string, which would be used when sourcing the movement data for that day.

    Example

    SELECT planned, stanox, actual FROM trust_movement WHERE schedule_id = "10387" AND created >="X1" and created <="X2" order by created ASC

    The example pulls in all of the movement data for record 10387 where
    Created is greater or equal to today's data
    (Query_var - 12*60*60) stores in PHP var
    Created is less than or equal to (query_var + 12*60*60)

    Guess I'd need to offset it some way ? Any advice pointers much appreciated
     
    mburdett555, Jul 21, 2019 IP
    SEMrush