Databases
StackOverflow2013
Postsdbo
POHow can I improve the speed of this MySQL query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I improve the speed of this MySQL query?
    primarykey
    data
    text
    <p>Here's the query:</p> <pre><code>SELECT u.uid as UID, fuo.uid as FUO_UID, fo.prid as FO_NAME FROM users u LEFT OUTER JOIN firstpoint_users_organisations fuo ON (u.uid=fuo.uid) LEFT OUTER JOIN firstpoint_organisations fo ON (fo.nid=fuo.nid) WHERE u.status=1 AND u.uid&gt;1 ORDER BY u.uid LIMIT 3; </code></pre> <p>And the tables:</p> <pre><code>users +------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------+----------------+ | uid | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | UNI | | | | status | tinyint(4) | NO | | 0 | | +-----------------------------------------------------------------------------+ firstpoint_users_organisations +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | nid | int(10) unsigned | NO | PRI | 0 | | | uid | int(10) unsigned | NO | PRI | 0 | | +-------+------------------+------+-----+---------+-------+ firstpoint_organisations +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | nid | int(10) unsigned | NO | PRI | 0 | | | prid | varchar(32) | NO | | | | +------------------------------------------------------------+ </code></pre> <p>I wish to show <code>users.uid</code> and <code>firstpoint_organisations.prid</code> for every row in <code>users</code>, even though some users won't have a <code>prid</code>, in which case I show NULL (hence the left outer joins). The connection should be as follows:</p> <pre><code>users uid - firstpoint_users_organisations \----&gt;uid nid - firstpoint_organisations \--------&gt;nid prid </code></pre> <p>So each user (users) has a user id (uid), and the organisation they're associated with (firstpoint_users_organisation) has a node id (nid) and stores this association. The organisation's details are then stored in firstpoint_organisations.</p> <p>So every user will have a <code>prid</code>, but if they don't, show NULL. </p> <p>Now, if I do an INNER JOIN on <code>firstpoint_users_organisations</code> and then on <code>firstpoint_organisations</code>, I get a good query speed (the above query runs in 0.02 seconds). But, when I switch both to LEFT OUTER JOIN, so I can get all users, <code>prid</code> or no <code>prid</code>, the above query takes ~90 seconds to run.</p> <p>Is there anything I can do to speed this query up? There are approx. 70,000 rows in the <code>users</code> table, but even with LIMIT 3, the making the INNER JOIN a LEFT OUTER JOIN takes a horrible amount of time. Interestingly, the query takes the same amount of time to run with LIMIT 30, so I think there's something fundamentally wrong with my query.</p> <p>EXPLAIN as requested:</p> <pre><code>+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+ | 1 | SIMPLE | u | range | PRIMARY | PRIMARY | 4 | NULL | 13152 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | fuo | index | NULL | PRIMARY | 8 | NULL | 3745 | Using index | | 1 | SIMPLE | fo | eq_ref | PRIMARY | PRIMARY | 4 | dbdb-dbdb_uat.fuo.nid | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+ 3 rows in set (0.00 sec) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USnjp
    1. USnjp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I improve the speed of this MySQL query?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I improve the speed of this MySQL query?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow can I improve the speed of this MySQL query?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPlease post the `EXPLAIN` of this statement.
      singulars
      1. POHow can I improve the speed of this MySQL query?
      1. USKermit
    2. COOne question to your firstpoint_users_organisations table is it possible to have 2 PRIMARY keys? It sounds actually a bit strange to me.
      singulars
      1. POHow can I improve the speed of this MySQL query?
      1. USWikunia
    3. COI've added the EXPLAIN, thankyou. 2 primary keys in the FUO table does look strange, thankyou for spotting.
      singulars
      1. POHow can I improve the speed of this MySQL query?
      1. USnjp
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload