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.

Query Optimization

Discussion in 'Databases' started by neilfurry, Sep 21, 2016.

  1. #1
    Hi, could anyone help me please.
    im optimizing my query to make it faster.. i did all the necessary requirements, including using indexes.

    here is my query. Im joining two tables here, i have more than 15K of rows on my table

    SELECT
    a.fullname,
    b.address1,
    b.city,
    b.state,
    b.zip,
    b.address2,
    b.unit_number
    FROM
    referrals AS a
    INNER JOIN address AS b ON a.clientid = b.clientid
    WHERE a.clientid=b.clientid
    SEMrush
    This query is too slow to return results. it would take more than 150s to return all results
    how can i make this faster? thank you
     
    neilfurry, Sep 21, 2016 IP
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,644
    Likes Received:
    212
    Best Answers:
    18
    Trophy Points:
    330
    #2
    INNER JOIN address AS b ON a.clientid = b.clientid
    WHERE a.clientid=b.clientid

    This is redundant, otherwise this is as optimized as it can get.

    SELECT
    a.fullname,
    b.address1,
    b.city,
    b.state,
    b.zip,
    b.address2,
    b.unit_number
    FROM
    referrals AS a
    INNER JOIN address AS b ON a.clientid = b.clientid

    The WHERE a.clientid=b.clientid is unnecessary since the relationship is already being declared in the INNER JOIN clause.

    2 questions. First, do you have to select all of the rows in the table, or could you use LIMIT and only select the rows that you need?

    Second, is there an index on these columns, or a foreign key relationship between the tables on the clientid columns?

    These are really the only things that could improve this from a database structure or query standpoint.

    Any other optimization is going to have to occur in the my.ini/my.cnf file or through hardware changes.
     
    jestep, Sep 21, 2016 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    24,486
    Likes Received:
    3,295
    Best Answers:
    94
    Trophy Points:
    615
    #3
    The clientid column is an integer, right?
     
    sarahk, Sep 21, 2016 IP
  4. Einheijar

    Einheijar Well-Known Member

    Messages:
    537
    Likes Received:
    13
    Best Answers:
    3
    Trophy Points:
    115
    #4
    Actually, you can use the explain statement in front of your query to see if its using keys or not. Chances are you have not defined your indexes yet.
     
    Einheijar, Oct 4, 2016 IP