Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Lightbulb Unanswered: SQL optimization

    Did somebody know how to optimize this line?!

    SELECT login FROM log WHERE substring (timeDate, 1, 10) = '2003-09-03'

    (timeDate format is 1900-01-01 00:00:00)

    thank you!!

  2. #2
    Join Date
    Jul 2003
    Posts
    30

    Re: SQL optimization

    I'll assume timeDate is indexed. Now taking substrings of indexes pretty much makes it like the index doesn't exist, and hence slower queries (actually, performing functions on fields makes it slower in general). Now what I would do, if my assumption is correct and if you're looking for all logins from 2003-09-03 is to rewrite your query to:

    SELECT login
    FROM log
    WHERE timeDate >= '2003-09-03 00:00:00'
    and timeDate < '2003-09-04 00:00:00'

    or something like that. And if you're looking for login's for today, you could omit the "and"

    Hope this helps.

  3. #3
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    Is the timeDate column a DATETIME type? Using

    SELECT login FROM log WHERE DATE_FORMAT( timeDate, '%Y-%m-%d' ) = '2003-09-03' ;

    ..may be faster as it's using one of the specific date functions, but I can't test it at the moment.

    When 4.1.1 is out, there's a new function called DATE() that will return just the date past of a datetime or timestamp type.

    Regards,
    Matt.

    EDIT: You could also try creating a partial index on that column to use with the substring query.

Posting Permissions

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