Re: [sqlite] Maximum length of the field name

[Martin Engelschalk]

Hi,

AFAIK, there is no limit. At least, browsing 
http://www.sqlite.org/limits.html, I found none. The maximum length of 
an SQL statement, 100 by default, limits the column names you can 
use, because you have to issue a "create table" - statement.

Martin

Ev wrote:
> What's the maximum length of the field name for sqlite?
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Maximum length of the field name

[Ev]

What's the maximum length of the field name for sqlite?

Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

[Igor Tandetnik]

Peter Haworth wrote:
> Interesting you should classify my data need as a waste without
> knowing anything about my application.  What if I want to calculate a
> percentage that the first column is of the total - would it still be a
> waste to calculate the total?

Yes, it would be a waste to calculate the total over and over. You could run a 
query with sum() once, save the result in a program variable, then do something 
like

select col, col / :total as percentage from mytable;

and bind the result of the previous query to :total parameter.

Try this though:

select col, colTotal
from mytable, (select sum(col) as colTotal from mytable);

I'm not sure, but it's possible this query calculates the sum only once. The 
one I suggested before, namely

select col, (select sum(col) from mytable) as colTotal
from mytable;

almost certainly calculates the sum once for each row.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Shane Harrelson]

On Sun, Nov 15, 2009 at 11:41 AM, P Kishor  wrote:
> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
>> I have a query with joined inline views that runs in about 100ms against
>> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>>
>> But when I use the LIKE operator instead of the = operator, the order of
>> the query plan changes, though the same indexes are involved, and the
>> query takes 40 seconds.  I'm trying to figure out what, if anything, I
>> can do to guide SQLite here.
>>
>> In broad terms, what is it about the use of the LIKE operator that
>> causes SQLite to re-order the plan, and is there any way to guide?
>>
>
> LIKE doesn't use indexes, although there are tricks that these SQL
> gurus will probably tell that could help you with workarounds. LIKE
> does a full scan.
>
>

There are certain conditions in which a LIKE (or GLOB) term will be
transformed by the query planner into an equivalent expression to
allow the use of indices.  Please see
http://www.sqlite.org/optoverview.html for a more in-depth discussion.


>> And what does the "from" column in the explain plan results refer to?
>> Are the values the tables/relations in the query statement? If so, how
>> are  they mapped? In order of appearance in the statement, so that 0 is
>> the first table mentioned in the statement?
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

[Peter Haworth]

Thank you Kees.  While not achieving exactly what I was thinking of  
(the total is in an extra row at the end of the selected rows rather  
than a column in each row), this will work for me.


Pete Haworth








http://www.mollysrevenge.com
http://www.sonicbids.com/MollysRevenge
http://www.myspace.com/mollysrevengeband








On Nov 15, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote:


Make that:

SELECT 'detail' AS Descr,
CASE
WHEN  THEN 
ELSE 
END AS CalcA
  FROM yourtable
UNION
SELECT 'total' AS Descr sum(
CASE
WHEN  THEN 
ELSE 
END
) AS CalcA
 FROM yourtable;

(untested)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[P Kishor]

On Sun, Nov 15, 2009 at 1:19 PM, Tim Romano  wrote:
> Thanks for the reply.
>
> A follow question:   I can understand why ... myColumn LIKE "%foo%"
> ...   would have to do a full scan but shouldn't  ...myColumn LIKE
> "foo%" ...  be able to use an index?
>
>

see Section 4 in http://www.sqlite.org/optoverview.html for all your
questions re. LIKE.



> P Kishor wrote:
>> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
>>
>>> I have a query with joined inline views that runs in about 100ms against
>>> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>>>
>>> But when I use the LIKE operator instead of the = operator, the order of
>>> the query plan changes, though the same indexes are involved, and the
>>> query takes 40 seconds.  I'm trying to figure out what, if anything, I
>>> can do to guide SQLite here.
>>>
>>> In broad terms, what is it about the use of the LIKE operator that
>>> causes SQLite to re-order the plan, and is there any way to guide?
>>>
>>>
>>
>> LIKE doesn't use indexes, although there are tricks that these SQL
>> gurus will probably tell that could help you with workarounds. LIKE
>> does a full scan.
>>
>>
>>
>>> And what does the "from" column in the explain plan results refer to?
>>> Are the values the tables/relations in the query statement? If so, how
>>> are  they mapped? In order of appearance in the statement, so that 0 is
>>> the first table mentioned in the statement?
>>>
>>> Thanks
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>>
>>
>>
>> 
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.5.425 / Virus Database: 270.14.66/2504 - Release Date: 11/15/09 
>> 07:50:00
>>
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

Thanks for the reply.

A follow question:   I can understand why ... myColumn LIKE "%foo%" 
...   would have to do a full scan but shouldn't  ...myColumn LIKE 
"foo%" ...  be able to use an index?


P Kishor wrote:
> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
>   
>> I have a query with joined inline views that runs in about 100ms against
>> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>>
>> But when I use the LIKE operator instead of the = operator, the order of
>> the query plan changes, though the same indexes are involved, and the
>> query takes 40 seconds.  I'm trying to figure out what, if anything, I
>> can do to guide SQLite here.
>>
>> In broad terms, what is it about the use of the LIKE operator that
>> causes SQLite to re-order the plan, and is there any way to guide?
>>
>> 
>
> LIKE doesn't use indexes, although there are tricks that these SQL
> gurus will probably tell that could help you with workarounds. LIKE
> does a full scan.
>
>
>   
>> And what does the "from" column in the explain plan results refer to?
>> Are the values the tables/relations in the query statement? If so, how
>> are  they mapped? In order of appearance in the statement, so that 0 is
>> the first table mentioned in the statement?
>>
>> Thanks
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
>
>
>   
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.66/2504 - Release Date: 11/15/09 
> 07:50:00
>
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

[P Kishor]

On Sun, Nov 15, 2009 at 12:34 PM, Peter Haworth  wrote:
> Interesting you should classify my data need as a waste without
> knowing anything about my application.  What if I want to calculate a
> percentage that the first column is of the total - would it still be a
> waste to calculate the total?

I was not casting aspersions on you, but merely commenting on the
technique without knowing any other context. Perhaps you should have
provided more context for your query in the first place.

You are correct.. if you want to calculate the percentage that the
first col is of the total, then you would need to know the total.

Kees and Igor have already responded to you on how to do that.

>
> As far as I'm concerned , the more data manipulation that can be done
> by the underlying db system, the better.  That way my application is
> protected from database structure changes. Subject to performance
> considerations of course.  Isn't that one of the purposes of db systems?
>

Well, yes and no. At its purest, a database stores data with a great
amount of integrity and returns it back to you as quickly as it can.
Rest of the shenanigans are developed in the application that uses
that db. Lines begin to blur, and sometimes you have entire
applications inside a database, and sometimes you have entire
databases inside applications. You are the only one who can choose the
most appropriate strategy for yourself.

A better way, usually, would be to do in the db what the db can do
quickly, then do everything else in the application. As I said above,
Igor showed you how you could return a result set with varying
conditional values in one column, and a sum total of those values in
the other column. You can try that.



> Pete Haworth
>
>
> On Nov 15, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote:
>
>> Think about what you are asking for... your result set is going to
>> contain two columns. The first column will have different values for
>> every row, but the second column will have the same value in every
>> row. Something like so...
>>
>> 5  33
>> 3  33
>> 7  33
>> 4  33
>> 9  33
>> 5  33
>>
>> and so on. What a waste. Still, if you insist on something like that,
>> do it in your application. Else, run two separate queries and union
>> them as Kees showed you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

[P Kishor]

On Sun, Nov 15, 2009 at 12:25 PM, Peter Haworth  wrote:
> Thanks for the suggestion.  Unfortunately, unless I'm doing something
> wrong, this seems to result in just one row being returned showing the
> sum amount.
>

That is what its supposed to do... a few suggestions --


1. Don't reply to Digests. The subject line serves no purpose, and
breaks the thread.

2. Quote what you are replying to, so it is clearly exactly what you
are referring to. For example, looking at your message above, only I
can decipher what you are talking about because I wrote the email you
are referring to, but because you are not quoting my text, even I have
a difficult time trying to figure out what you are saying. We receive
hundreds of emails a day, from many mailing lists. Quoting properly
(and proper subject lines) helps us keep things in context.

3. There seems to be a lag between what you are reading and what
others are sending as responses to you. Several folks have already
responded to you on this subject. If you read all those emails, it
might help a lot.

Good luck.



> Pete Haworth
>
>
> On Nov 15, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote:
>
>> Date: Sat, 14 Nov 2009 14:17:18 -0600
>> From: P Kishor 
>> Subject: Re: [sqlite] Referring to columns named with AS
>> To: General Discussion of SQLite Database 
>> Message-ID:
>>       
>> Content-Type: text/plain; charset=ISO-8859-1
>>
>> On Sat, Nov 14, 2009 at 1:58 PM, Peter Haworth
>>  wrote:
>>> I'm trying to get a SELECT statement in the following general form
>>> to work:
>>>
>>> SELECT CASE WHEN  THEN  ELSE  ?
>>> END AS
>>> CalcA, sum(CalcA) AS CalcATotal 
>>>
>>> I get an error "no such column" referring to CalcA when used in the
>>> sum
>>> function. ?I'm trying to get total of all the values of CalcA
>>> across all the
>>> selected rows. ?Is there a way to do this?
>>>
>>
>> Try
>>
>> SELECT sum(CalcA) AS CalcATotal
>> FROM (
>>  SELECT CASE WHEN  THEN  ELSE 
>> END AS CalcA
>>  FROM table
>>  WHERE ...
>> )
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

[Peter Haworth]

Interesting you should classify my data need as a waste without  
knowing anything about my application.  What if I want to calculate a  
percentage that the first column is of the total - would it still be a  
waste to calculate the total?

As far as I'm concerned , the more data manipulation that can be done  
by the underlying db system, the better.  That way my application is  
protected from database structure changes. Subject to performance  
considerations of course.  Isn't that one of the purposes of db systems?

Pete Haworth


On Nov 15, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote:

> Think about what you are asking for... your result set is going to
> contain two columns. The first column will have different values for
> every row, but the second column will have the same value in every
> row. Something like so...
>
> 5  33
> 3  33
> 7  33
> 4  33
> 9  33
> 5  33
>
> and so on. What a waste. Still, if you insist on something like that,
> do it in your application. Else, run two separate queries and union
> them as Kees showed you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 15

[Peter Haworth]

Thanks for the suggestion.  Unfortunately, unless I'm doing something  
wrong, this seems to result in just one row being returned showing the  
sum amount.

Pete Haworth


On Nov 15, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote:

> Date: Sat, 14 Nov 2009 14:17:18 -0600
> From: P Kishor 
> Subject: Re: [sqlite] Referring to columns named with AS
> To: General Discussion of SQLite Database 
> Message-ID:
>   
> Content-Type: text/plain; charset=ISO-8859-1
>
> On Sat, Nov 14, 2009 at 1:58 PM, Peter Haworth  
>  wrote:
>> I'm trying to get a SELECT statement in the following general form  
>> to work:
>>
>> SELECT CASE WHEN  THEN  ELSE  ? 
>> END AS
>> CalcA, sum(CalcA) AS CalcATotal 
>>
>> I get an error "no such column" referring to CalcA when used in the  
>> sum
>> function. ?I'm trying to get total of all the values of CalcA  
>> across all the
>> selected rows. ?Is there a way to do this?
>>
>
> Try
>
> SELECT sum(CalcA) AS CalcATotal
> FROM (
>  SELECT CASE WHEN  THEN  ELSE 
> END AS CalcA
>  FROM table
>  WHERE ...
> )

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[P Kishor]

On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
> I have a query with joined inline views that runs in about 100ms against
> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>
> But when I use the LIKE operator instead of the = operator, the order of
> the query plan changes, though the same indexes are involved, and the
> query takes 40 seconds.  I'm trying to figure out what, if anything, I
> can do to guide SQLite here.
>
> In broad terms, what is it about the use of the LIKE operator that
> causes SQLite to re-order the plan, and is there any way to guide?
>

LIKE doesn't use indexes, although there are tricks that these SQL
gurus will probably tell that could help you with workarounds. LIKE
does a full scan.


> And what does the "from" column in the explain plan results refer to?
> Are the values the tables/relations in the query statement? If so, how
> are  they mapped? In order of appearance in the statement, so that 0 is
> the first table mentioned in the statement?
>
> Thanks
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

I have a query with joined inline views that runs in about 100ms against 
a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)

But when I use the LIKE operator instead of the = operator, the order of 
the query plan changes, though the same indexes are involved, and the 
query takes 40 seconds.  I'm trying to figure out what, if anything, I 
can do to guide SQLite here. 

In broad terms, what is it about the use of the LIKE operator that 
causes SQLite to re-order the plan, and is there any way to guide?

And what does the "from" column in the explain plan results refer to?  
Are the values the tables/relations in the query statement? If so, how 
are  they mapped? In order of appearance in the statement, so that 0 is 
the first table mentioned in the statement?

Thanks


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help me please to optimize sql query

[Unabashed]

Thank you, Simon! Could you please say me what indexes will be correct?
I'm trying
CREATE INDEX mgwrInd1 ON mgWordsRelations (id_norm)
CREATE INDEX mgwrInd2 ON mgWordsRelations (id_norminrel)
CREATE INDEX wfInd1 ON wform (wordForm)
CREATE INDEX wfInd2 ON wform (wordNorm)
CREATE INDEX wfInd3 ON wform (ancode)
but I have really high lack of perfomance. What is wrong with it? If you
couldn't answer maybe you could post me some link to read about it? Thank
you.


Simon Slavin-3 wrote:
> 
> 
> On 15 Nov 2009, at 12:08am, Igor Tandetnik wrote:
> 
>> select wf1.wordForm
>> from wform wf1 join mgWordsRelations rel on (wf1.wordNorm =
>> rel.id_norminrel)
>>join wform wf2 on (rel.id_norm = wf2.wordNorm)
>> where wf1.ancode = 'someAncode' and wf2.wordForm = 'someWord'
>> order by random() limit 1;
>> 
>> It's the same query, just written in a simpler form that might make it
>> easier for SQLite to optimize.
> 
> Also, we assume that you have all the best indexes to make the JOINs and
> WHEREs work quickly.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Help-me-please-to-optimize-sql-query-tp26354687p26358412.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help me please to optimize sql query

[Unabashed]

Thank you very much! It works well!


Igor Tandetnik wrote:
> 
> Try this:
> 
> select wf1.wordForm
> from wform wf1 join mgWordsRelations rel on (wf1.wordNorm =
> rel.id_norminrel)
> join wform wf2 on (rel.id_norm = wf2.wordNorm)
> where wf1.ancode = 'someAncode' and wf2.wordForm = 'someWord'
> order by random() limit 1;
> 
> It's the same query, just written in a simpler form that might make it
> easier for SQLite to optimize.
> 
> Igor Tandetnik
> 

-- 
View this message in context: 
http://old.nabble.com/Help-me-please-to-optimize-sql-query-tp26354687p26358367.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] german documentation

[Artur Reilin]

That's one point. I also prefer the german version instead of an english
one. It's almost better to understand if there are many technic related
words and topics in it.

I searched for an community or something like that, but i didn't found
one, so i started a new project for what. I know, i have a lot of
translation work to do now, but that's not a problem for me ^^

with best wishes

Artur


---

> Marcus Grimm schrieb:
>> this will be a lot of work and I'm wondering why
>> you do this ?
>> Despite beeing a german with a rather poor english knowledge,
>> I guess a programmer should still be able to understand
>> the english sqlite documentation, right ? :-)
>>
> There are still lots of programmers out there, who prefers documentation
> in German and buy and use tools, books and journals which are
> written/translated in German.
>
> Though I would not like to do this work - there might be a community for
> it ...
>
> Marten
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


Artur Reilin
sqlite.yuedream.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] german documentation

[Marten Feldtmann]

Marcus Grimm schrieb:
> this will be a lot of work and I'm wondering why
> you do this ?
> Despite beeing a german with a rather poor english knowledge,
> I guess a programmer should still be able to understand
> the english sqlite documentation, right ? :-)
>   
There are still lots of programmers out there, who prefers documentation 
in German and buy and use tools, books and journals which are 
written/translated in German.

Though I would not like to do this work - there might be a community for 
it ...

Marten
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users