Re: [sqlite] Column names in a UNION

[Elcin Recebli]

Same is true for Oracle 8i/9i/10g.

E.


--- Rob Lohman [EMAIL PROTECTED] wrote:
 Microsoft SQL seems to use the column names from the
 first select. So that would be a, b in your example.
 
 Rob
 
 - Original Message - 
 From: [EMAIL PROTECTED]
 To: Sqlite-users sqlite-users@sqlite.org
 Sent: Saturday, March 25, 2006 3:45 PM
 Subject: [sqlite] Column names in a UNION
 
 
  Who can tell me what the correct column names should be
  for a UNION.  For example:
  
   SELECT a, b FROM t1 UNION SELECT x, y FROM t2;
  
  The result set of the query above has two columns.  Should
  those columns be named a and b or x and y?
  
  Does anybody know what the SQL standard says?  Do all the
  other SQL database engines get it right or is there some
  disagreement?
  
  --
  D. Richard Hipp   [EMAIL PROTECTED]
 
 
 
 --
 * Zoner PhotoStudio 8 - Your Photos perfect, shared, organised! 
 www.zoner.com/zps
 
 

__
Stops spam 100% for your email accounts or you get paid. http://www.cashette.com

Re: [sqlite] Join on same table and performance.

[Elcin Recebli]

Hi.

You're joining the table with itself using 'id'. However, there's no index just 
on that field. I'm not sure how exactly SQLite utilises indices, but it might 
be unable to use index on (id,key,source) triple to optimise calculation of 
m1.id = m2.id.

Does this sound sensible?

Cheers.

--- =?ISO-8859-1?Q?Tobias_Rundstr=F6m?= [EMAIL PROTECTED] wrote:
 Hello,
 
 I guess this subject is a bit worn out. But I am having scalabillity  
 problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle  
 insanely huge media libraries, playlists and clients. Our goal is to  
 be able to run medialibs with 50.000 files without problem. Our  
 backend is SQLite3. When we get somewhere around 16 rows (10k  
 entries) we're starting to see problems with scalabillity of SQLite3.  
 I would like some views on how we could speed up the storage backend  
 and also comments on our structure.
 
 Today we save all data in one table called Media. Each entry has a id  
 number and each id number can have a indefinitive number of  
 properties. To keep this flexible and clean we choose to add one row  
 per property. A property can be artist, album, url and more.  
 The schema is this:
 
 create table Media (id integer, key, value, source integer);
 and the indexes:
 create unique index key_idx on Media (id,key,source);
 create index prop_idx on Media (key,value);
 create index source_idx on Media (key,source);
 create index key_source_val_idx on Media (key,source,value);
 
 The most common query is something like this:
 
 select value from Media where id=5 and key='url' and source=1;
 
 This query remains very fast no matter how many entries I have in my  
 database also things like:
 
 select key, value from Media where id=5 and source=1;
 
 is still very fast.
 
 But more advanced queries like show me all albums and artists that  
 are not compilations are very slow:
 
 select distinct m1.value as artist, ifnull(m2.value,'[unknown]') as  
 album from Media m1 left join Media m2 on m1.id = m2.id and  
 m2.key='album' left join Media m3 on m1.id = m3.id and  
 m3.key='compilation' where m1.key='artist' and m3.value is null;
 
 In fact, whenever I join with myself and try to extract a big number  
 of values it can take forever to get the result.
 
 I have tried to increase the cache_size to somewhere around 32000 to  
 see if it made any difference, it didn't.
 idxchk tells me that the good indexes are in use.
 
 Any comments, help or blame is welcome to try to solve this issue of  
 scalabillity. You can download a medialib here:
 http://debian.as/~skid/medialib.db.gz this contains almost 20  
 rows and 14000 songs and is a real user library.
 
 Looking forward to getting your input.
 
 Thanks
 Tobias
 
 
 --
 * Zoner PhotoStudio 8 - Your Photos perfect, shared, organised! 
 www.zoner.com/zps
 
 

__
Stops spam 100% for your email accounts or you get paid. http://www.cashette.com