Cross table query including COUNT and LEFT JOINS in mysql

Question

I am building a query that will generate a preview for a profile on my website.

The query involves several tables, getting certain information, i.e. a name and COUNT()'s for statistics of a certain profile i.e. how many likes they have.

The query below is taking forever to execute. One of the problems I see is that I'm using LEFT JOINS to get all the data. So first of all can anybody help me on how to choose what JOIN I use for what query.

Other than that, can anyone see what I'm doing wrong. Whats taking it so long to execute.

Here is the query:

SELECT  u.id AS id, 
        u.about AS About, 
        CONCAT(u.fn,' ',u.ln) AS Fullname, 
        u.username AS Username, 
        i.image AS Image,
        COUNT(DISTINCT a.id) AS Contacts,
        COUNT(DISTINCT ul.id) AS Thumbs,
        u.views AS Views,
        COUNT(DISTINCT o.id) AS TrybesStarted,
        COUNT(DISTINCT ti.id) AS TrybesJoined,
        COUNT(DISTINCT ai.id) AS AmbitionsSupporting,
        COUNT(DISTINCT am.id) AS AmbitionsCompleted,
        COUNT(DISTINCT sp.id) AS Posts
FROM x_table1 u
    /* PIC */
    LEFT JOIN x_table2 i
    ON u.id = i.user_id
    /* CONTACTS */
    LEFT JOIN x_table3 a
    ON (u.id = a.to  AND a.accepted = 1 OR u.id = a.from AND a.accepted = 1)
    /* THUMBS UP */
    LEFT JOIN x_table4 ul 
    ON (u.id = ul.profile_id)
    /* TRYBES STARTED */
    LEFT JOIN x_table5 o 
    ON (u.id = o.profile_id)
    /* TRYBES JOINED */
    LEFT JOIN x_table6 ti 
    ON (u.id = ti.to AND ti.accepted = 1)

    /* AMBITIONS SUPPORTING */
    LEFT JOIN x_table7 ai
    ON (u.id = ai.to AND ai.accepted = 1)

    /* AMBITIONS COMPLETED */
    LEFT JOIN x_table8 ao
    ON (u.id = ao.profile_id)
    LEFT JOIN x_table9 am
    ON (ao.ambition_id = am.id AND am.complete = 1)

    /* POSTS */
    LEFT JOIN x_table10 sp 
    ON (u.id = sp.profile_id)

WHERE u.id = '1234userid'

Assume all table data is correct, if you would like it request it and I'll get it done for you.

P.s. Before I added the /* AMBITIONS COMPLETED */ the query seemed to run fine. I think its because it contains a double LEFT JOIN.

Thanks in advance

Answer 1

After hours of research I came across another query technique to retrieve the following data:

Array
(
    [ID] => useridentifier
    [ABOUT] => ABOUT :)
    [NAME] => Chris m
    [REGISTERED] => 2013-10-21 12:54:50
    [USERNAME] => Cwiggo
    [VIEWS] => 3
    [IMAGE] => useridentifier
    [AWARDS] => 0
    [TRYBANK] => 1
    [USER_CONTACTS] => 6
    [USER_THUMBS_UP] => 6
    [TRYBES_STARTED] => 28
    [TRYBES_JOINED] => 13
    [USER_POSTS] => 8
    [AMBITIONS_STARTED] => 40
    [AMBITIONS_JOINED] => 13
    [AMBITIONS_COMPLETE] => 1
    [Retreival] => 0.00026202201843262
)

This data is collected from several tables from my database. As you can see, the query speed is very quick. Better than a hang anyway!

The change to the query was as follows. I'll provide a snippet as it's basically repeated code for different tables:

SELECT u.id AS ID, 
    u.registered                AS REGISTERED, 
    u.x_account_username        AS USERNAME,
    COALESCE(uc.cnt, 0)         AS USER_CONTACTS,
    COALESCE(ut.cnt, 0)         AS USER_THUMBS_UP,

    FROM x_user u

    #IMAGES
    LEFT JOIN x_user_images images  ON u.id = images.user_id

    #CONTACTS
    LEFT JOIN 
      ( SELECT `to`,accepted,`from`, COUNT(*) AS cnt FROM
        x_allies
        GROUP BY `to`) uc
    ON (u.id = uc.to AND uc.accepted = 1 OR u.id = uc.from AND uc.accepted = 1)

    #THUMBS UP
    LEFT JOIN 
      ( SELECT user_id, COUNT(*) AS cnt FROM
        x_user_likes
        GROUP BY user_id ) ut
    ON u.id = ut.user_id
WHERE u.id = 'useridentifier'

I hope this answer is of some help for developers who are trying to access, summarise and collate statistics from their database tables.

Thanks for commenting

Answer 2

If query got slow after adding table x_table8 and x_table9 then I think x_table9 table contains large data. Try to avoid LEFT JOIN. To use LEFT JOIN if you want a record from x_table8 even if it dont have associated records in x_table9 table then only use LEFT JOIN other wise JOIN can do the job for you much faster.

As you said you are using this to generate a Profile then I will suggest using JOIN as tables with no data for the profile ID will me meaning less to you.

If you cant avoid LEFT JOIN try to use temp tables and use those TEMP TABLES in your query that will be much faster.

Hope you will do this much faster.