Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: slow query

  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    slow query

    Managed to do a wery slow query,
    this takes like 4 - 5 sec on fast machine, on tables with like 3000/200 records.
    It does not use any indexes, I gues I cant make index on RIGHT(c.jsp,32) ?

    Code:
    select c.dsp, c.comment  from comments c 
    join galery_1 g on RIGHT(c.jsp,32) = g.id_galery_obfuscated
    order by c.dsp desc LIMIT 0,5
    Any suggestions, besides inventing new keyed column ?
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SELECT Comments.* from Comments INNER JOIN galery_1 ON RIGHT(Comments.jsp,32) = galery_1.id_galery_obfuscated order by Comments.dsp desc LIMIT 0,5

    Note: that this query will work only in MY SQL server and not on MS SQL server
    as LIMIT is not supported in MS SQL

    -------------------------
    Mobile Application Development

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    I give up. What did "reforminfotech" change in that query that could possibly matter to performance?

    I think maybe he doesn't understand that Bubikol was simply using table aliases to shorten his coding??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    Bubikol:

    Code:
    ALTER TABLE comments ADD jsp32 VARCHAR(32);
    
    UPDATE comments SET jsp32 = RIGHT(jsp,32);
    
    CREATE INDEX comments_jsp32 ON coments(jsp32);
    *NOW* do
    Code:
    select c.dsp, c.comment  
    from comments c join galery_1 g 
    on c.jsp32 = g.id_galery_obfuscated
    order by c.dsp desc LIMIT 0,5
    In other words, if you will do this all the time, add an indexed field specifically for this purpose.

    If need be, add a TRIGGER to maintain the added jsp32 field whenever jsp field is updated.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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