StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Don't have any experience with PDO so can't help with that but this method is pretty performant, although it's a bit ugly in places ;)</p> <h2>PHP</h2> <pre><code><?php $nums = array(); $max = 10000; for($i=0;$i<$max*10;$i++) $nums[] = $i; $conn = new mysqli("127.0.0.1", "vldb_dbo", "pass", "vldb_db", 3306); $sql = sprintf("call list_products_by_id('%s',0)", implode(",",array_rand($nums, $max))); $startTime = microtime(true); $result = $conn->query($sql); echo sprintf("Fetched %d rows in %s secs<br/>", $conn->affected_rows, number_format(microtime(true) - $startTime, 6, ".", "")); $result->close(); $conn->close(); ?> </code></pre> <h2>Results</h2> <pre><code>select count(*) from product; count(*) ======== 1000000 Fetched 1000 rows in 0.014767 secs Fetched 1000 rows in 0.014629 secs Fetched 2000 rows in 0.027938 secs Fetched 2000 rows in 0.027929 secs Fetched 5000 rows in 0.068841 secs Fetched 5000 rows in 0.067844 secs Fetched 7000 rows in 0.095199 secs Fetched 7000 rows in 0.095184 secs Fetched 10000 rows in 0.138205 secs Fetched 10000 rows in 0.134356 secs </code></pre> <h2>MySQL</h2> <pre><code>drop procedure if exists list_products_by_id; delimiter # create procedure list_products_by_id ( in p_prod_id_csv text, in p_show_explain tinyint unsigned ) proc_main:begin declare v_id varchar(10); declare v_done tinyint unsigned default 0; declare v_idx int unsigned default 1; create temporary table tmp(prod_id int unsigned not null)engine=memory; -- split the string into tokens and put into a temp table... if p_prod_id_csv is not null then while not v_done do set v_id = trim(substring(p_prod_id_csv, v_idx, if(locate(',', p_prod_id_csv, v_idx) > 0, locate(',', p_prod_id_csv, v_idx) - v_idx, length(p_prod_id_csv)))); if length(v_id) > 0 then set v_idx = v_idx + length(v_id) + 1; insert ignore into tmp values(v_id); else set v_done = 1; end if; end while; end if; if p_show_explain then select count(*) as count_of_tmp from tmp; explain select p.* from product p inner join tmp on tmp.prod_id = p.prod_id order by p.prod_id; end if; select p.* from product p inner join tmp on tmp.prod_id = p.prod_id order by p.prod_id; drop temporary table if exists tmp; end proc_main # delimiter ; </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