StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is this MySQL JOIN statement returning more results?
text
Body
copied!<p>I have two (<strong>characteristic_list</strong> and <strong>measure_list</strong>) tables that are related to each other by a column called 'm_id'. I want to retrieve records using filters (columns from <strong>characteristic_list</strong>) within a date range (columns from <strong>measure_list</strong>). When I gave the following SQL using INNER JOIN, it takes a while to retrieve the record. What am I doing wrong?</p> <pre><code>mysql> explain select c.power_set_point, m.value, m.uut_id, m.m_id, m.measurement_status, m.step_name from measure_list as m INNER JOIN characteristic_lis t as c ON (m.m_id=c.m_id) WHERE (m.sequence_end_time BETWEEN '2010-06-18' AND '2010-06-20'); +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 82952 | | | 1 | SIMPLE | m | ALL | NULL | NULL | NULL | NULL | 85321 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 2 rows in set (0.00 sec) mysql> select count(*) from measure_list; +----------+ | count(*) | +----------+ | 83635 | +----------+ 1 row in set (0.18 sec) mysql> select count(*) from characteristic_list; +----------+ | count(*) | +----------+ | 83635 | +----------+ 1 row in set (0.10 sec) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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