Databases
StackOverflow2013
Postsdbo
POSQL JOIN vs. multiple SELECT statements
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL JOIN vs. multiple SELECT statements
    primarykey
    data
    text
    <p>Every developer working with databases has this problem. And often you can not estimate how many records a table will have in x years.</p> <ul> <li><a href="https://stackoverflow.com/questions/376375/left-join-vs-multiple-select-statements">LEFT JOIN vs. multiple SELECT statements</a> </li> <li><a href="https://stackoverflow.com/questions/377729/performance-single-join-select-vs-multiple-simple-selects">performance - single join select vs. multiple simple selects</a></li> </ul> <p>Especially when working with O/R mappers, this is extremely uncomfortable!</p> <p>Why does the RDBMS driver don't solve this problem? Why multiple transfer a record instead of once and then reference it. For the client application this could be completely transparent. Or even offer advanced functions. Especially with OR mapper, it might even be very useful to create subbeans analogous to the DB data, only as a reference.</p> <p>It would be wonderfull if you could join 1:n tables without the strain knowing of redudant data.</p> <p>Does anyone know a RDBMS that optimizes like this? Or can't this be done? And if so, why?</p> <p><strong>----- ---- EDIT ----- -----</strong><br> @Thilo: Thank for the link. Very interesting.</p> <p>I've run a test with XAMPP for Windows.<br> PHP: 5.4.7<br> MySQL: 5.5.27<br> The result shows that you have to be carefull with JOIN in MySQL.</p> <p>Everytime you do a JOIN you will get duplicated data (except 1:1). Why transfering this data multiple?</p> <p><strong>Test:</strong></p> <p>I created two tables. Table A with 500 records and 9 columns with VARCHAR(32) and Table B with 50000 records. (1:100)</p> <pre><code>SET @numA = 500; SET @numBperA = 100; DROP TABLE IF EXISTS `table_b`; DROP TABLE IF EXISTS `table_a`; DROP PROCEDURE IF EXISTS fill_table_b; DROP PROCEDURE IF EXISTS fill_table_a; CREATE TABLE `table_a` ( `id` int(11) NOT NULL, `val1` varchar(32) NOT NULL, `val2` varchar(32) NOT NULL, `val3` varchar(32) NOT NULL, `val4` varchar(32) NOT NULL, `val5` varchar(32) NOT NULL, `val6` varchar(32) NOT NULL, `val7` varchar(32) NOT NULL, `val8` varchar(32) NOT NULL, `val9` varchar(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1; delimiter $$ CREATE PROCEDURE fill_table_a() BEGIN DECLARE i INT DEFAULT 1; SET i = 1; WHILE ( i &lt;= @numA) DO INSERT INTO table_a (id, val1, val2, val3, val4, val5, val6, val7, val8, val9) VALUES (i, md5(rand()), md5(rand()), md5(rand()), md5(rand()), md5(rand()), md5(rand()), md5(rand()), md5(rand()), md5(rand())); SET i=i+1; END WHILE; END$$ delimiter ; call fill_table_a(); CREATE TABLE IF NOT EXISTS `table_b` ( `id` int(11) NOT NULL AUTO_INCREMENT, `table_a_id` int(11) NOT NULL, `val` varchar(32) NOT NULL, PRIMARY KEY (`id`), KEY `table_a_id` (`table_a_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; ALTER TABLE `table_b` ADD CONSTRAINT `table_b_ibfk_1` FOREIGN KEY (`table_a_id`) REFERENCES `table_a` (`id`); delimiter $$ CREATE PROCEDURE fill_table_b() BEGIN DECLARE i INT DEFAULT 1; DECLARE j INT DEFAULT 1; SET i = 1; WHILE (i &lt;= @numA) DO SET j = 1; WHILE (j &lt;= @numBperA) DO INSERT INTO table_b (table_a_id, val) VALUES (i, md5(rand())); SET j=j+1; END WHILE; SET i=i+1; END WHILE; END$$ delimiter ; call fill_table_b(); </code></pre> <p>Now I want to select 300 rows from table A and the dependent 30000 rows from table B.</p> <p>I've done this 3 ways:</p> <p><strong>Select A JOIN B with a single request</strong> </p> <pre><code>$time = microtime(true); for( $i = 0; $i &lt; 50; $i++ ) { $resultA = mysqli_query($link, "SELECT * FROM table_a LEFT JOIN table_b ON table_b.table_a_id = table_a.id WHERE table_a.id BETWEEN 100 AND 399"); $resultArray = array(); //while( $resultArray[] = mysqli_fetch_assoc($resultA) ) {} $numRows = mysqli_num_rows($resultA); } $time2 = microtime(true); echo("numSelectedRows: " . $numRows . "&lt;br&gt;time: " . number_format($time2 - $time, 3) . " sec.&lt;br&gt;Memory: " . number_format(memory_get_peak_usage() / 1024 / 1024, 3) . " MiB"); </code></pre> <blockquote> <ul> <li><p>with fetch<br> numSelectedRows: 30000<br> time: 15.539 sec.<br> Memory: 55.649 MiB </p></li> <li><p>without fetch<br> numSelectedRows: 30000<br> time: 6.262 sec.<br> Memory: 3.431 MiB </p></li> </ul> </blockquote> <p><strong>Select A with single request. Iterate over Result and make 300 Requests to Table B.</strong> </p> <pre><code>$time = microtime(true); for( $i = 0; $i &lt; 50; $i++ ) { $numRowsB = 0; $resultA = mysqli_query($link, "SELECT * FROM table_a WHERE table_a.id BETWEEN 100 AND 399"); while( $row = mysqli_fetch_assoc($resultA) ) { $resultB = mysqli_query($link, "SELECT * FROM table_b WHERE table_b.table_a_id = " . $row['id']); while( mysqli_fetch_assoc($resultB) ) {} $numRowsB += mysqli_num_rows($resultB); } } $numRowsA = mysqli_num_rows($resultA); $time2 = microtime(true); echo("numSelectedRows A: " . $numRowsA . "&lt;br&gt;numSelectedRows B: " . $numRowsB . "&lt;br&gt;time: " . number_format($time2 - $time, 3) . " sec.&lt;br&gt;Memory: " . number_format(memory_get_peak_usage() / 1024 / 1024, 3) . " MiB"); </code></pre> <blockquote> <ul> <li>with fetch<br> numSelectedRows A: 300<br> numSelectedRows B: 30000<br> time: 7.713 sec.<br> Memory: 0.364 MiB </li> </ul> </blockquote> <p><strong>Select A with single request. Select B with single request.</strong> </p> <pre><code>$time = microtime(true); for( $i = 0; $i &lt; 50; $i++ ) { $resultA = mysqli_query($link, "SELECT * FROM table_a WHERE table_a.id BETWEEN 100 AND 399"); $resultB = mysqli_query($link, "SELECT * FROM table_b WHERE table_b.table_a_id BETWEEN 100 AND 399"); $resultArray = array(); //while( $resultArray[] = mysqli_fetch_assoc($resultA) ) {} //while( $resultArray[] = mysqli_fetch_assoc($resultB) ) {} } $numRowsA = mysqli_num_rows($resultA); $numRowsB = mysqli_num_rows($resultB); $time2 = microtime(true); echo("numSelectedRows A: " . $numRowsA . "&lt;br&gt;numSelectedRows B: " . $numRowsB . "&lt;br&gt;time: " . number_format($time2 - $time, 3) . " sec.&lt;br&gt;Memory: " . number_format(memory_get_peak_usage() / 1024 / 1024, 3) . " MiB"); </code></pre> <blockquote> <ul> <li><p>with fetch<br> numSelectedRows A: 300<br> numSelectedRows B: 30000<br> time: 6.020 sec.<br> Memory: 15.928 MiB </p></li> <li><p>without fetch<br> numSelectedRows A: 300<br> numSelectedRows B: 30000<br> time: 3.018 sec.<br> Memory: 1.156 MiB </p></li> </ul> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USStahlkocher
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    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. This table or related slice is empty.
    1. COIt seems that Oracle does something like this: http://jonathanlewis.wordpress.com/2010/05/07/sqlnet-compression/
      singulars
      1. POSQL JOIN vs. multiple SELECT statements
      1. USThilo
 

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