SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Trying to improve slow running queries

    I didn't think the following query was overly complex, but apparently it is, as I found this in the slow queries log on my web server.

    Thoughts?

    # Wed Jan 23 10:13:04 2013
    # Query_time: 1.543565 Lock_time: 0.000131 Rows_sent: 0 Rows_examined: 392771
    SET timestamp=1358961184;
    SELECT loginID FROM logins WHERE uID = '12392' and loginDate like '2013-01-23%'
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,203
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    1.5 seconds for 390 thousand rows isn't bad.

    Is the uID field a numeric or character. If numeric, take out the quotes. Is the loginDate a date field? If so, you could try loginDate BETWEEN '2013-01-23 00:00:00' AND '2013-01-23 11:59:59'. The conversions can give you a hit on performance.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try using the Explain syntax before your query to try and understand what is happening - http://dev.mysql.com/doc/refman/5.0/en/explain.html

    Code:
    EXPLAIN SELECT loginID FROM logins WHERE uID = '12392' and loginDate like '2013-01-23%
    Richard
    Resell SSL Certificates - API / WHMCS / HostBill / ClientExec
    ServerTastic - RapidSSL, Geotrust, Thawte, Symantec, SmarterTools and more


Bookmarks

Posting Permissions

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