SQL Select 10 Random Rows and Out of It 1 With "x" Length

Question

    $randNo = mt_rand(0,$ProductsCount);
    $sql0 = "SELECT sku FROM catalog_product_flat_1 LIMIT ". $randNo . ",10";
    $data2=$connection->query($sql2);
    $result2=$data2->fetch();

I'm trying to get from my database random SKU from (200k products) which is 13 characters (30-40% of products) in length, So I thought about selecting 10 products and get the one with 13 chars how can I select the 13 chars from this selection? Or any other better way to approach this? I need the best possible speed.

Would need to modify the count query to also only select products with a SKU length of 13 - I'm not entirely sure if you can index that, might be rather slow — Sam Dufel, Jul 28 '14 at 23:17
Note that LIMIT without ORDER BY is pretty much meaningless. — Strawberry, Jul 28 '14 at 23:27

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

If I'm understanding your question correctly, try:

$sql0 = "SELECT sku FROM catalog_product_flat_1 where LENGTH(sku) = 13 ORDER BY RAND() LIMIT 1";

But see this for a more solution that might have better performance: https://stackoverflow.com/a/23568354/2517689

I think I overwrote @Barranka's edit to fix my RANDOM() to RAND(). Thanks for the edit.

edited May 23 '17 at 12:04

Community

1
1

answered Jul 28 '14 at 23:18

Pete Scott

1,516
10
10

it works but still the performance is important I have got this `SELECT sku FROM catalog_product_flat_1 WHERE CHAR_LENGTH(sku) = 13 LIMIT $randNo,1` Its fast this has interesting behavior as I understand it starts from `$randNo` line and keep on until it finds a 13 chars long, also LENGTH i think is also wrong I need in char length as far i understand – zumbamusic Jul 28 '14 at 23:35
Length versus char_length is dependant on the data in the column. I'm not sure which of those performs better when either is an option. – Pete Scott Jul 28 '14 at 23:37
1

I just looked up LENGTH VS CHAR_LENGTH. Length returns the string measured in bytes. Char_length returns the string measured in characters. – Class Jul 28 '14 at 23:38
True, and upvote for great information, but I'm betting a typical SKU almost certainly always consists of 1-byte characters. – Pete Scott Jul 28 '14 at 23:42

score 0 · Accepted Answer · answered Jul 28 '14 at 23:33

Try doing it this way:

First, set $ProductsCount to:

select count(*)
from catalog_product_flat_1
where length(sku) = 13;

Then do:

$randNo = mt_rand(0,$ProductsCount);
$sql0 = "SELECT sku FROM catalog_product_flat_1 WHERE length(sku) = 13 LIMIT ". $randNo . ",1";
$data2=$connection->query($sql2);
$result2=$data2->fetch();

This should be faster than a sort.

SQL Select 10 Random Rows and Out of It 1 With "x" Length

2 Answers2