Results 1 to 5 of 5
  1. #1
    Join Date
    May 2014
    Posts
    9

    Unanswered: How to speed up a query

    So this query now works really well

    SELECT DISTINCT
    b.event_id AS a,
    b.user_ID AS a,
    b.date AS a,
    b.Time AS a
    FROM quadrantids2017 AS a
    JOIN quadrantids2017 AS b
    WHERE a.`date` = b.`date`

    AND a.user_ID != b.user_ID
    AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30

    ORDER BY
    a.date ASC,
    a.Time ASC,
    a.user_ID ASC




    Its slow, there are 3144 records and its taking 37 seconds to get the result which is 1519 records so I added an index to event,_id, user_id, date and time and its reduced to 24 secs but thats still really slow

    Pointers appreciated

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9

    Thumbs down

    what index did you create? Also, what are the sizes of the tables in question. Keep in mind that you aren't providing any inputs to the query, so one of the two tables will always be a scan of all rows in the table, whether it be just an index scan or the table directly, depends on a number of factors. It will never run subsecond nor should it be considered for an OLTP transaction.


    Sorry, just saw the piece at end where you described an index.

  3. #3
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    sorry, just saw your piece about the index at end of your post.

    I would think you would want the indexes more like:

    quadrantids2017:
    `date`
    ,`Time`
    ,user_ID
    ,event_id

  4. #4
    Join Date
    May 2014
    Posts
    9

    Progress

    Hi

    Changing the index has reduce the query time to about 12 seconds which is good news
    Table data is below

    CREATE TABLE `quadrantids2017` (
    `event_id` int(11) NOT NULL auto_increment,
    `user_ID` int(11) default NULL,
    `date` date default NULL,
    `Time` time default NULL,
    `Signal` decimal(6,2) default NULL,
    `Noise` decimal(6,2) default NULL,
    `Frequency` int(11) default NULL,
    `Duration` decimal(6,2) default NULL,
    `image` varchar(255) default NULL,
    `wav` varchar(255) default NULL,
    `Lat` decimal(11,1) default NULL,
    `long` decimal(11,1) default NULL,
    `source` varchar(255) default NULL,
    `timesync` varchar(255) default NULL,
    `datetime` datetime default NULL,
    PRIMARY KEY (`event_id`),
    KEY `event_id` USING BTREE (`date`,`Time`,`user_ID`,`event_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=52400 DEFAULT CHARSET=utf8;

    Now I am simply testing at the minute. A bit of context may help

    The data is around meteor detection and I have a main table for ALL events - this will grow in time to around 50,000 per month, querys on the front end via a web interface are simple, you can search for event on a date and between times etc and thats fast


    So for particular meteor showers I create from the main table a subset of data, in this instance quadrantids2017 which covers a 4 day period and then run the query to find events that are Plus or minus 30 seconds


    Maybe it would be better if the plus or minus query actually created a table called quadrantids2017_30secs then the web interface would bring back the results which have already been calculated rather than running on the fly - hope this makes sense

    the output from the time query looks like this, event_id, User_id, Date, Time


    37776 2 2017-01-01 01:08:45
    47827 3 2017-01-01 01:09:07

    33501 1 2017-01-01 01:14:59
    47828 3 2017-01-01 01:15:00

    33503 1 2017-01-01 01:24:48
    47829 3 2017-01-01 01:24:49
    37781 2 2017-01-01 01:24:53


    So in addition to the query creating a new table (which I can do) it would be useful if I added a new field called say group so the results would look like this

    event_id, User_id, Date, Time, Group


    37776 2 2017-01-01 01:08:45 1
    47827 3 2017-01-01 01:09:07 1

    33501 1 2017-01-01 01:14:59 2
    47828 3 2017-01-01 01:15:00 2

    33503 1 2017-01-01 01:24:48 3
    47829 3 2017-01-01 01:24:49 3
    37781 2 2017-01-01 01:24:53 3


    That way via the web interface I can group the results into block

    I just dont know hwo to add the grouping in the query

    Sorry if this is a little long

    John B

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    228
    Provided Answers: 1
    Why do you alias every column as "a" ??? It just makes no sense!

    1) You should avoid using reserved/problematic names like "DATE" or "TIME" for your columns
    2) You should post the result of the EXPLAIN so we can help you
    3) You should provide us with some more details like the number of rows in your table.

    4) Your query should look something like :

    SELECT DISTINCT
    t2.event_id,
    t2.user_ID,
    t2.date,
    t2.Time
    FROM quadrantids2017 t1
    JOIN quadrantids2017 t2
    WHERE t1.`date` = t2.`date`

    AND t1.user_ID <> t2.user_ID
    AND time_to_sec(t1.`Time`) - time_to_sec(t2.`Time`) BETWEEN -30 AND 30

    ORDER BY
    t1.date ASC,
    t1.Time ASC,
    t1.user_ID ASC

    5) Have you tried creating an index on "Time" and see how this helps?
    6) Is the EXPLAIN changed if you use an INNER JOIN ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •