Databases
StackOverflow2013
Postsdbo
PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
    primarykey
    data
    text
    <p>Here's the situation: </p> <p>I have a few tables (described below) that track residents in an apartment building, their unit number, and when they were "seen" last (we have lots of older people with health issues, so it's important to check on them every 2 days or so; sometimes they die here and that's how we know how to check on them).</p> <p>The contstraints for a "check" are that they have to have been seen in the past 48 hours; if not, the query should pull their record up. Here are the table definitions I'm using:</p> <p>The "people" table, where resident info is stored:</p> <pre><code>MariaDB [olin2]&gt; describe people; +-------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | fname | varchar(32) | NO | MUL | NULL | | | lname | varchar(32) | NO | | NULL | | | dob | date | YES | | NULL | | | license_no | varchar(24) | NO | | NULL | | | date_added | timestamp | NO | | CURRENT_TIMESTAMP | | | status | varchar(8) | NO | | Allow | | | license_exp | date | YES | | NULL | | +-------------+-------------+------+-----+-------------------+----------------+ </code></pre> <p>The "units" table, where unit numbers are stored (people switch units so I didn't want them in the "people" table):</p> <pre><code>MariaDB [olin2]&gt; describe units; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | number | varchar(3) | NO | MUL | NULL | | | resident | int(11) | NO | | NULL | | | type | varchar(16) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ </code></pre> <p>and the "wellness" table, where the "checks" are stored (the resident's id number, when they were seen and by whom, etc.):</p> <pre><code>MariaDB [olin2]&gt; describe wellness; +--------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-------------------+----------------+ | wellness_id | int(11) | NO | PRI | NULL | auto_increment | | people_id | int(11) | NO | | NULL | | | time_checked | timestamp | NO | | CURRENT_TIMESTAMP | | | check_type | varchar(1) | NO | | NULL | | | username | varchar(16) | NO | | NULL | | | return_date | timestamp | YES | | NULL | | +--------------+-------------+------+-----+-------------------+----------------+ </code></pre> <p>The "return_date" field in the "wellness" table is for when a resident leaves for more than 2 days, then they won't be included in the results when they are displayed (they actually will be included in the query results, but I use PHP to filter those out).</p> <p>Here's the query I have been using... It worked well for a few weeks, but as there were more and more records added it's been getting noticably slower (right now its 3.5 seconds to return the results):</p> <pre><code>select p.id, w.time_checked, w.username, w.return_date from people p left join units u on p.id = u.resident left join wellness w on p.id = w.people_id left join wellness as w2 on w.people_id = w2.people_id and w.time_checked &lt; w2.time_checked where w2.people_id is null and w.time_checked &lt; (now() - interval 48 hour) order by u.number </code></pre> <p>I know my problem is the joins, but I don't know how to get the results I need without them and/or how to optimize this query to speed it up... Here's a sample of results (if needed):</p> <pre><code>+----+---------------------+----------+---------------------+ | id | time_checked | username | return_date | +----+---------------------+----------+---------------------+ | 8 | 2013-12-01 11:00:13 | tluce | 0000-00-00 00:00:00 | +----+---------------------+----------+---------------------+ 1 row in set (3.44 sec) </code></pre> <p>So, in this result set, resident 8 hasn't been seen for 3 days... the result is correct but the 3.44 sec isn't acceptable for my users to have to wait.</p> <p>Any ideas on how I can improve this?</p> <p>EDIT (More Info):</p> <p>I realize updating the wellness entry for each person would be easier and quicker to access; however I like to have these data on-hand because I generate graphs from it to show A) when we most often see a particular resident and B) which staff members check on people the most often (aka - who's doing their job and who's not)</p> <p>I DO use indexes, and here's the results of an EXPLAIN on my query:</p> <pre><code> +------+-------------+-------+--------+---------------+---------+---------+----------------- -+------+--------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+----------------- -+------+--------------------------------------------------------------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 107 | Using temporary; Using filesort | | 1 | SIMPLE | p | eq_ref | PRIMARY,idx | PRIMARY | 4 | olin2.u.resident | 1 | Using where | | 1 | SIMPLE | w | ALL | NULL | NULL | NULL | NULL | 7074 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | w2 | ALL | NULL | NULL | NULL | NULL | 7074 | Using where; Not exists; Using join buffer (incremental, BNL join) | +------+-------------+-------+--------+---------------+---------+---------+----------------- -+------+--------------------------------------------------------------------+ </code></pre> <p>The indexes in the people table: <code>id, fname, lname, license_no</code> The wellness table: <code>wellness_id</code> The units table: <code>id, number</code></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjmd9qs
    1. USjmd9qs
    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. PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
      1. USjmd9qs
      1. VTFavorite
    2. VO
      singulars
      1. PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
      1. This table or related slice is empty.
      1. VTUpMod
    1. COcan you run EXPLAIN on this query?
      singulars
      1. PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
      1. USSam D
    2. COSam D: please see my edit for the information you requested.
      singulars
      1. PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
      1. USjmd9qs
    3. COUnder possible keys each field that has NULL means that index was not used. so you can add index to the fields you use to join. for example units.residents. you can do the same thing to wellness.people_id
      singulars
      1. PONeed to speed up MySQL query, it's at 3+ seconds with only 4,000 records, has 3 joins
      1. USSam D
 

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