Geek asks and answers

Best practices for optimizing SQLite

I have a SQLite "Details" table with the structure:

ID  Name    Category   
---------------------
1   Matt    0 
2   Shervin 0 
3   Bob     0   
4   Lee     0 
5   Rick    0   
6   Suraya  0 
7   Susan   0   
8   Adam    0   
9   Jon     1   
10  Lorna   1 
... and so on .......

I want to select a row in random order, and then three names from three different rows (again, randomly). I would like all of this to be returned from a single SQLite statement. For instance.

ID  Name  Category  Name1  Name2  Name 3  
----------------------------------------
3   Bob   0         Matt   Lee    Susan

My attempt at this is described below, but it has two problems:

  • The three additional names are not always always different - I cannot exclude the name that was previously selected because the b / c / d variables are not in scope except for the COALESCE native function.
  • Since each nested selection uses the Random () function, it is not very efficient.

- ( SQLite)? / - , , , - .

:

SELECT a.Id,
       a.Name,
       a.Category,
       COALESCE((SELECT b.Name 
                   FROM Details b 
                  WHERE b.Id NOT IN (a.Id)
                    AND b.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name1",
       COALESCE((SELECT c.Name 
                   FROM Details c 
                  WHERE c.Id NOT IN (a.Id)
                    AND c.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name2",
       COALESCE((SELECT d.Name 
                   FROM Details d
                  WHERE d.Id NOT IN (a.Id)
                    AND d.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name3"
    FROM Details a
     AND a.Category IN (0)
ORDER BY Random()
   LIMIT 1
+3
5

. , , , , .

, ( >= min (pkcol) <= max (pkcol)), (.. /). , , . , . .

P.S. (, iPhone) , ID ( PK, ). ( , ), , PK [ ID (a, b, c, d)] ; . , -, , Random(), . 8, 12, 16 4 , 4.

P.P.S. , , , . , , , , .

+3

, :

CREATE TEMP TABLE names
AS
SELECT
  Id,
  Name,
  Category
FROM Details
WHERE Category IN (0)
ORDER BY Random()
LIMIT 4;

SELECT
  MAX(CASE rowid WHEN 1 THEN Id END) AS Id,
  MAX(CASE rowid WHEN 1 THEN Name END) AS Name,
  MAX(CASE rowid WHEN 1 THEN Category END) AS Id,
  MAX(CASE rowid WHEN 2 THEN Name END) AS Name1,
  MAX(CASE rowid WHEN 3 THEN Name END) AS Name2,
  MAX(CASE rowid WHEN 4 THEN Name END) AS Name3
FROM names;

DROP TABLE names;
+1

x3, , ?

0

, , , . , . . Id , , , .

 SELECT Id
      ,Name
      ,Category
      ,bName
      ,cName
      ,dName 
FROM Details,
    (
        SELECT Name AS bName, cName, dName 
        FROM Details,
            (
                SELECT Name AS cName, dName 
                FROM Details,
                    (
                        SELECT Name AS dName 
                        FROM Details 
                        WHERE Category IN (0) 
                        ORDER BY Random() 
                        LIMIT 1
                    ) td
                WHERE Name <> dName 
                AND Category IN (0) 
                ORDER BY Random() 
                LIMIT 1
            ) tc 
        WHERE Name <> dName 
        AND Name <> cName 
        AND Category IN (0) 
        ORDER BY Random() 
        LIMIT 1
    ) tb
WHERE Name <> dName 
AND Name <> cName 
AND Name <> bName 
AND Category IN (0) 
ORDER BY Random() 
LIMIT 1;

Random() , , Id, .

0

random().

-, , : limit 1 offset random(), order by random() limit 1.

, , ( - , ). , , :

select *
from (select *
      from tbl
      where id between :x and :x + 20
      )
order by random()
limit 1
0

More articles:


All Articles