On Mar 1, 2012, at 12:20 AM, Roger Binns wrote:
There is a reason developers have gone to the trouble of naming their
constraints!
Indeed. All these constraint names are meant to convey information. They are
not decorative.
___
sqlite-users
On Feb 23, 2012, at 6:21 PM, Levi Haskell wrote:
sqlite select 1 from (select *);
Wow, wicked :)
Confirmed on sqlite3 -version
3.7.10 2012-01-16 13:28:40 ebd01a8deffb5024a5d7494eef800d2366d97204
___
sqlite-users mailing list
On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:
I would like to know the exact limit
on this, so that I could my code to work within this limit :)
See Maximum Number Of Terms In A Compound SELECT Statement:
http://www.sqlite.org/limits.html
On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:
. I was wondering if I could insert them using a
single INSERT query
Ah, also, there is not much benefit in using a compound insert.
You could as well simply insert all your values in one transaction and be done.
On the other hand, the
On Feb 22, 2012, at 10:35 PM, Gour wrote:
Otoh, here we come to the well-known object-relational impedance
mismatch problem and wonder how to resolve it, iow.:
Are you actually trying to solve a concrete problem? Or creating one out of
thin air instead?
On Feb 22, 2012, at 10:21 PM, Pete wrote:
I try to access that column in any way, I get an error, no matter whether I
specify the column name with no quotes, single quotes or double quotes
For the record:
http://www.sqlite.org/lang_keywords.html
On Feb 19, 2012, at 4:59 PM, Jörgen Hägglund wrote:
INSERT INTO History VALUES ('c:\', 1)
UPDATE History SET Hits = Hits + 1 WHERE Path = 'c:\'
Short of a merge statement, which SQLite lacks, you will indeed need to use two
statements.
For example, you could turn your first insert
On Feb 19, 2012, at 6:16 PM, Roger Andersson wrote:
insert or replace
One thing to keep in mind when using insert or replace is that this will
create an entirely new record each and every single time. Which means the rowid
is always going to change. Which makes it a very poor candidate as a
On Feb 19, 2012, at 6:40 PM, Pete wrote:
I'm
really asking the general question what is possible within the CHECK
constraint?
An expression:
http://www.sqlite.org/lang_expr.html
Is it possible to base the check on a SELECT statement on
another table?
Nope. No (sub)queries.
E.g.:
On Feb 19, 2012, at 1:24 AM, Pete wrote:
is it possible to
check if the value of Col1 exists in a column in a different table?
Perhaps you are looking for foreign constraints:
http://www.sqlite.org/foreignkeys.html
___
sqlite-users mailing list
On Feb 15, 2012, at 11:47 AM, bhaskarReddy wrote:
How can i check whether a row is there or not, for particular
column values.
http://www.w3schools.com/sql/sql_where.asp
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Feb 14, 2012, at 8:01 PM, Rob Richardson wrote:
What would be the best way to speed this up?
wrap all your inserts in one transaction. commit at the end.
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Feb 10, 2012, at 4:00 PM, Willian Gustavo Veiga wrote:
Unfortunately, strftime isn't a solution. It's not a standard.
Unfortunately, extract isn't supported. strtime is what you can use.
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Feb 8, 2012, at 12:14 AM, Bart Smissaert wrote:
Have a table with an integer age field and a text age_group field.
Are you 100% positive that you have a number and not a text?
Contrasts:
select case
when '1' between 0 and 9 then '0 to 9'
end as band
Vs.
select case
On Feb 2, 2012, at 10:29 AM, bhaskarReddy wrote:
I have to find a type of a value in sql.
You sent the exact same message 2 hours ago. Are you a bot?
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote:
Does SQLite have FIRST and LAST aggregate function?
No, sadly, SQLite doesn't support any analytic functions (aka window function)
such as first, last, lead, lag, rank, etc, etc... [1]
To achieve the same, you will have to roll your own,
On Feb 2, 2012, at 5:26 PM, Igor Tandetnik wrote:
ORDER BY applies to groups, not to rows within each group (is this different
with Oracle?)
analytic functions works in term of the result set itself.
Here is a simple example:
On Feb 2, 2012, at 9:30 PM, rixtertrader wrote:
This is because the year started in the middle of the first week.
Perhaps you might be interested in using the ISO week instead:
http://en.wikipedia.org/wiki/ISO_week_date
___
sqlite-users mailing
On Jan 31, 2012, at 10:05 AM, bhaskarReddy wrote:
Can any one tell me how to access values of a table column with its
particular row id.
In general, I would suggest that you get the basics straight:
http://www.sqlite.org/books.html
I tried with select * from ontTable where rowid=2;
On Jan 31, 2012, at 12:33 PM, Live Happy wrote:
thx for answer
You asked the very same on January the 28th. Are you a bot?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Jan 31, 2012, at 6:20 PM, Fredrick Ughimi wrote:
Now, I want to be able to use the SQL statement to do the above calculation
for all the products available (Not just ProductNo1 alone) in one SQL
Statement. Is it possible?
Sure. Join your various tables and group the result set by
On Jan 31, 2012, at 6:45 PM, Bill McCormick wrote:
Funny how SQL works; it really can't figure out what you REALLY want to do
:) Maybe somebody who is really smart could figure out how to add a feature
to do this? :)
Try: pragma dwim = yes;
___
On Jan 27, 2012, at 8:43 PM, K Peters wrote:
Why would the second statement still return null if the first statements
returns 'null'?
select typeof(max(id)) from categories -- returns 'null'
select case max(id) when null then 1 end as NextID from categories
As mentioned, null is, hmmm,
On Jan 25, 2012, at 10:09 PM, John Elrick wrote:
DML?
http://en.wikipedia.org/wiki/Data_Manipulation_Language
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Jan 23, 2012, at 8:39 PM, dotolee wrote:
print td.date('Y-m-d h:m:s',$row['updated'])./td/tr;
%m month: 01-12
%M minute: 00-59
Check the fine manual:
http://www.sqlite.org/lang_datefunc.html
___
On Jan 18, 2012, at 3:35 PM, g...@novadsp.com wrote:
INSERT INTO table (columns) VALUES(?,?,?,?);
SELECT last_insert_rowid() FROM table;
These are really two statements, not one. Execute them one after the other and
you will get the desired effect.
On Jan 18, 2012, at 12:00 PM, Petr Lázňovský wrote:
have windows batch working with sqlite, may I insert image into database and
than read this images from?
As pointed out, you might want to use the 'blob' type to store binary data.
That said, why bother storing these images inside the
On Jan 18, 2012, at 3:44 PM, Igor Tandetnik wrote:
INSERT INTO table (columns) VALUES(?,?,?,?);
SELECT last_insert_rowid() FROM table;
These are really two statements, not one. Execute them one after the other
and you will get the desired effect.
Or just call
On Jan 18, 2012, at 4:16 PM, g...@novadsp.com wrote:
Or just use last_insert_rowid() directly in the next insert statement. That
will reuse the rowid of the previous insert for the new one. And both row
will end up with the same id, keeping your source table and its R*Tree index
in synch.
On Jan 18, 2012, at 4:24 PM, Petr Lázňovský wrote:
but wikipedia is reasonlessly turned off today
On the contrary, they have pretty good reasons:
http://en.wikipedia.org/wiki/Wikipedia:SOPA_initiative/Learn_more
https://www.google.com/landing/takeaction/
On Jan 11, 2012, at 9:53 PM, inq1ltd wrote:
Can someone tell me how to get the column names
contained in a table on the fly.
pragma table_info
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Jan 4, 2012, at 12:50 PM, BareFeetWare wrote:
Yes. This works with the above schema:
select
cast.character_name as Character Name
, actors.name || ' ' || actors.surname as Actor
, group_concat(other movies.title, ', ') as Other movies where we've
seen this actor
from
On Jan 4, 2012, at 4:01 PM, Igor Tandetnik wrote:
Unfortunately, looks like this query will filter out actors that have
performed in only that one movie
Just change the last two joins to LEFT JOIN.
And the where clause.
And perhaps add an explicit group by.
On Jan 3, 2012, at 8:30 PM, Abhinav Upadhyay wrote:
What other options do I have ?
Two FTS tables? One with the Porter stemmer, for search, one without, to build
the auxiliary tables?
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Jan 2, 2012, at 5:25 PM, Jay A. Kreibich wrote:
Oracle has some syntax short-cuts to deal with this, but they're
non-standard.
Recursive 'with' clause anyone?
goodbye Connect By or: the end of hierarchical querying as we know it
On Dec 23, 2011, at 2:31 PM, Chris Mets wrote:
A parameterized view allows me to do that just fine in other SQL engines,
Well, MSSQL sports so-called parameterized views, but that's about it.
but apparently not SQLite.
If you insist on that approach, you could rewrite your view in term of
On Dec 22, 2011, at 4:08 PM, Paul Sanderson wrote:
I have a large table with some duplicate rows that I want to
delete.
Something along these lines:
delete
fromfoo
where rowid not in
(
selectmax( rowid )
from foo
group by bar,
On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:
Is this truly a limitation of SQLite or am I doing something wrong?
The later. Simply create your view. Then restrict it.
In other words:
create view foo as select bar from baz
select * from foo where bar = ?
On Dec 20, 2011, at 8:34 PM, jim-on-linux wrote:
cursor.execute insert into default (rowname) values ( '1' ) ;
'default' is a keyword:
http://www.sqlite.org/lang_keywords.html
If you insist on that name, double quote it.
___
sqlite-users mailing
On Dec 20, 2011, at 9:52 PM, Nico Williams wrote:
It'd be nice to have recursive queries (with tail-call optimization).
Yes for recursive with clauses!
http://gennick.com/with.html
Then a lot of things get easier.
Like solving that damn sodoku puzzle:
Solving a Sudoku using Recursive
On Dec 6, 2011, at 1:45 PM, priya786 wrote:
i want to know
Yes.
how to get the database diagram from sqlite.
Yes
Please tell me the solution.
42.
In other news, I prefer mine round:
http://www.visualcomplexity.com/vc/project.cfm?id=42
___
On Nov 29, 2011, at 10:47 AM, Darren Duncan wrote:
Is it possible to insert multiple rows using a single statement ?
Yes.
INSERT INTO foo (x, y)
VALUES (1,2), (3,4), (5,6),...;
I don't think this syntax is supported by SQLite:
http://www.sqlite.org/lang_insert.html
INSERT INTO foo
On Nov 24, 2011, at 8:56 AM, Gaurav Vyas wrote:
I used the following syntax to create index
CREATE UNIQUE INDEX persons_1x
ON persons (pid,hid);
Check you query plan (i.e. explain query plan [1]).
I doubt such index has any use as SQLite doesn't support index skip scans
access plan or
On Nov 23, 2011, at 5:17 PM, Wiktor Adamski wrote:
sqlite select 1 from t order by avg(a); -- should be possible
Error: misuse of aggregate: avg();
As it says on the tin: nonsensical.
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Nov 23, 2011, at 5:35 PM, Pavel Ivanov wrote:
But although it's completely senseless
just syntactically it looks correct - should produce just one row and
thus ORDER BY will be a no-op.
Well, if this is about Alice in Wonderland, then, what about:
select max( 1 ) from t order by avg( a
On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote:
Well, if this is about Alice in Wonderland, then, what about:
select max( 1 ) from t order by avg( a );
1
Well, apparently you did this on non-empty table. This query gives
different and kind of unexpected result on empty table. ;)
Well,
On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote:
This query gives
different and kind of unexpected result on empty table. ;)
Ooops... I see what you mean... on an empty table... this returns one row with
a null value:
sqlite select max( 1 ) from t;
That would qualify as a bug I guess :))
On Nov 23, 2011, at 6:28 PM, Pavel Ivanov wrote:
No, it's not a bug. It's SQL standard that such form of aggregate
query always returns one row. And when there's no rows in the table it
should return NULL (for all aggregate functions except count() which
returns 0). I said it's kind of
On Nov 23, 2011, at 7:58 PM, Pavel Ivanov wrote:
I believe OVER() is an Oracle-specific extension to SQL, not a
standard in any way.
Well, over( partition by... order by ... ) is part of the analytical syntax of
Oracle... nothing to do with ordering a result set...
Function(arg1,..., argn)
On Nov 23, 2011, at 8:50 PM, Wiktor Adamski wrote:
ISO/IEC 9075-2:2003:
window function ::= window function type OVER window name or
specification
This is related to so-called analytics in Oracle parlance. Not quite related to
the topic at hand.
On Nov 24, 2011, at 8:48 AM, Simon Slavin wrote:
Is the table indexed on that column ?
And if it is... what's its selectivity?
What 's the query plan?
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Nov 22, 2011, at 6:44 PM, Pavel Ivanov wrote:
INSERT INTO [filed1] VALUES TIME('29-01-2011 08:00:00')
Result of TIME('29-01-2011 08:00:00') is NULL. So your field1 doesn't
contain anything. Maybe that's why your comparison doesn't work.
As per the fine manual:
On Nov 16, 2011, at 12:02 AM, Nico Williams wrote:
Why are the mail archives for sqlite-users not available for download?
They are:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/
As for loading them into a SQLite3 DB... I once wrote a schema for
this, and it's
On Nov 14, 2011, at 3:33 PM, Paxdo Presse wrote:
If my write transactions consume an average of 10 ticks (1 ticks = 60th of a
second), it means that I can get very approximately 6 write operations per
second? (with a recent computer powerful enough).
At the same time, how many read
On Nov 14, 2011, at 3:45 PM, Paxdo Presse wrote:
The writing is sequential, so I guess only one thread at a time.
Yes, only one writer at one time.
But for reading? Are multiple threads can simultaneously perform read
operations?
As many readers as you want.
On Nov 14, 2011, at 3:53 PM, Paxdo Presse wrote:
If each thread writing (a transaction that contains one or more
INSERT/UPDATE) takes an average of 10 ticks, and each thread reading (a
transaction that contains one or more SELECT) takes an average of 10 ticks, I
would like to know how
On Nov 14, 2011, at 4:49 PM, Paxdo Presse wrote:
I hesitate a lot in my choice of database for my web application.
FWIW...
Situations Where SQLite Works Well
• Websites
SQLite usually will work great as the database engine for low to medium traffic
websites (which is to say, 99.9% of all
On Nov 12, 2011, at 1:58 AM, Pavel Ivanov wrote:
No, exists in this case will change query plan significantly and
performance can degrade drastically as a result.
Why would that be?
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Nov 9, 2011, at 10:24 PM, Fabian wrote:
It seems that FTS doesn't need to read the whole index from
disk, so I'm trying to pinpoint the difference. My best guess is that it
creates a fresh b-tree for the additional inserts, causing the boost in
performance.
Indeed.
Quoting the fine
On Nov 9, 2011, at 10:24 PM, Fabian wrote:
And I'd like to avoid to have some fuzzy logic
that tries to predicts which of the two methods is going to be faster.
Perhaps an alternative to your conundrum is the good, old divide and conquer
approach. In other words, you could partition your
On Nov 9, 2011, at 11:59 PM, Fabian wrote:
So would it be an idea to have a simple flag (NOINDEX for example) which
dictates that a certain column shouldn't be indexed by FTS, just stored? It
may be a lot simpler to implement than actual datatypes, and I could work
around the other
On Nov 4, 2011, at 11:59 PM, Paxdo Presse wrote:
Are we sure that another process is not going to create another row between
my INSERT and SELECT LAST ROWID?
yes
The LAST ROWID is it for sure the id of INSERT INTO of the transaction?
yes
___
On Nov 1, 2011, at 3:34 PM, Ryan Belcher wrote:
Is this a bug or something I'm not doing right or don't understand?
Check your data type. Make sure to use one which can hold the necessary
precision (i.e. real):
http://www.sqlite.org/datatype3.html
If necessary, cast when appropriate:
On Nov 1, 2011, at 3:34 PM, Ryan Belcher wrote:
For that last result, 290.08 - 6.97 = 283.11; so base - thick is equal to
283.11 but not less. Is this a bug or something I'm not doing right or don't
understand?
As mentioned, precision, precision, precision :P
select * FROM test WHERE
On Oct 23, 2011, at 6:06 AM, Paul Linehan wrote:
Is there a way of storing SQLite data (tables) as ASCII text rather
than as binary data?
Perhaps you might be better off with something along the lines of KirbyBase or
such.
http://www.netpromi.com/kirbybase_python.html
On Oct 22, 2011, at 3:17 PM, Fabian wrote:
So is there some way to have a 'stand-alone index', which doesn't store
everything twice?
Not in SQLite, no.
Other databases (such as Oracle) sometime offer so-called Index Organized Table
(IOT).
http://www.orafaq.com/wiki/Index-organized_table
On Oct 20, 2011, at 10:15 PM, Peter Aronson wrote:
And while I suppose I could
ask for these changes to be made as enhancements to
SQLite, I assume from the lack of them at this time that they are not exactly
common requirements.
Well, prising out any type of metadata from SQLite is a
On Oct 18, 2011, at 11:57 AM, Mark Schonewille wrote:
However, if you really want a forum, install one on your web server and find
out how many people use it.
Yes, go install your forum and leave us in peace :P
___
sqlite-users mailing list
On Oct 18, 2011, at 9:21 PM, Jos Groot Lipman wrote:
Yes, go install your forum and leave us in peace :P
Who said mail-lists get less flame-wars ;-)
Flame warriors roster:
http://redwing.hutman.net/~mreed/
Take the personality test!
___
On Oct 18, 2011, at 11:09 PM, Fabian wrote:
Is there any solution for this?
Perhaps this is not a technical issue, but rather a design one, as you seem to
be, hmmm, tilting at windmills.
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Oct 16, 2011, at 1:09 PM, Fabian wrote:
How can you limit a count-query? I tried:
SELECT COUNT(*) FROM table LIMIT 5000
But it ignores the LIMIT clause.
No it doesn't, it works as advertised. You are falling into the same trap as
you did just a couple of threads ago. You need to get
On Oct 16, 2011, at 10:39 PM, Kit wrote:
select count(*) from (select 1 from table limit 5000)
SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);
you realize that count( * ) has a very specific meaning, right?
The count(*) function (with no arguments) returns the total number of rows
On Oct 13, 2011, at 10:57 PM, Fabian wrote:
Thank you very much! This approach solved the problem. However, in my
situation I need to select a lot more columns than just 'id' from
'mail_header',
Feel free to select all the relevant columns from the inner query.
and when I look at the
On Oct 14, 2011, at 6:28 AM, Jay A. Kreibich wrote:
What I want to know is if there is any way to get more better
reporting, such as the column or constraint it is upset about.
No. As several have said.
Well, as this very topic of meaningless error message comes back with a very
On Oct 14, 2011, at 10:23 AM, James Hartley wrote:
When dealing with tables with foreign constraints, how can the value of a
recently inserted primary key be propagated to multiple child tables? Is
there a way to save the value of last_insert_rowid() as a SQL statement?
Sure.
insert
into
On Oct 14, 2011, at 12:39 PM, Fabian wrote:
I still don't have optimal performance in the query (although it's much
better now), and it seems to be related to ORDER BY.
Yes, order by has a cost.
When I execute:
SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50
It's very
On Oct 14, 2011, at 2:49 PM, Fabian wrote:
That explains everything!
Hurray! Now you must have the finest query ever to grace the intraweb! A true
work of beauty :))
___
sqlite-users mailing list
sqlite-users@sqlite.org
On Oct 14, 2011, at 3:37 PM, Fabian wrote:
2011/10/14 Petite Abeille petite.abei...@gmail.com
Hurray! Now you must have the finest query ever to grace the intraweb! A
true work of beauty :))
Here in Belgium we have a saying: Who doesnt honor the petite, is not worth
the big
On Oct 13, 2011, at 5:59 PM, Filip Navara wrote:
Reproduced on Windows, SQLite 3.7.8.
Works ok on Mac OS X 10.6.8.
$ sqlite3 -version
3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177
sqlite CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
sqlite INSERT INTO fts (tags)
On Oct 13, 2011, at 9:17 PM, Dan Kennedy wrote:
It means there is a problem with a foreign key definition in the
database schema. Either an FK specifies parent columns that do not
exist, or parent columns that are not a PRIMARY KEY or UNIQUE.
It also mean there is a problem in how SQLite
On Oct 13, 2011, at 9:36 PM, Frank Missel wrote:
Interesting, how do you get the data from the table or view into Excel to be
the basis of the Pivottable?
Do you paste it to a worksheet (perhaps as arrays) that then becomes the
basis of the Pivottable?
Pivot tables can be populated from
On Oct 12, 2011, at 3:31 AM, Shorty wrote:
Or is the speed difference so small it doesn't matter?
If you have a trivial amount of data, then it doesn't matter much either way.
Anything goes when you have no data :)
But in general, do normalize your data and use foreign keys. There are
On Oct 12, 2011, at 11:36 AM, Fabian wrote:
How would I optimize the above queries, to take advantage of the
LIMIT/OFFSET values, making them suitable for fast pagination?
Are you sure the issue is with the fts table? Or is it the order by desc?
In any case, you can easily refactor the query
On Oct 12, 2011, at 4:27 PM, Pavel Ivanov wrote:
I can confirm this with the following script:
sqlite select a, count(case when b = 't' then 1 end), count(case when b =
'T' then 1 end) from t group by a;
1|2|2
2|1|1
Hmmm... yes... something very wrong:
select a, count(case when lower(
On Oct 12, 2011, at 4:45 PM, Simon Slavin wrote:
You didn't define the columns as text columns, so SQLite doesn't understand
that 'T' is anything like 't'.
Hmmm...?!?!?
select b, typeof( b ) from t;
t|text
t|text
T|text
t|text
T|text
T|text
http://www.sqlite.org/datatype3.html
On Oct 12, 2011, at 5:28 PM, Simon Slavin wrote:
Was that not what you were expecting ? You are using count(2) not sum(2).
I guess the posted test case had a typo.
Nonetheless, contrast:
sqlite select a, sum(case when b = 't' then 1 end), sum(case when b = 'T' then
1 end) from t group by
On Oct 12, 2011, at 5:16 PM, Fabian wrote:
Why is this very fast (20 ms):
0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)
And this very slow (3500ms):
0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows)
0 1 1 SCAN TABLE table2 VIRTUAL TABLE INDEX 1: (~0
On Oct 11, 2011, at 2:39 PM, Frank Missel wrote:
SQLite allows this as an extension. When this happens, a value from an
arbitrary row within the group is reported. This is often convenient.
Ha ha, you must be joking, right?
Think about it as a, hmmm, practical joke.
On Oct 10, 2011, at 3:35 PM, Black, Michael (IS) wrote:
With the relatively new prefix option FTS4 appears to be slightly faster
now...could be within the error spread though.
Hurray for FTS! :)
I had to upgrade to 3.7.8 -- not sure when that prefix came in but it wasn't
in 3.7.5.
On Oct 11, 2011, at 6:00 PM, Frank Missel wrote:
Yeah, a pragma strict would be great. I can't be the only one who would
rather make the queries more error proof.
I wonder how much of that feature is intentional vs. accidental. To me it looks
more like an implementation detail leak being
On Oct 9, 2011, at 1:03 PM, Black, Michael (IS) wrote:
For your example create a separate table with just the first letter and build
an index on it.
This is most likely pointless as the selectivity of such index is going to be
very low.
Plus not point in reinventing a square wheel, instead
On Oct 9, 2011, at 12:15 PM, Mohit Sindhwani wrote:
create table titles (id integer primary key, title text, ...);
could we sort the records by title and use that in some way to restrict the
search space when searching titles starting with a specific letter?
You might be better off moving
On Oct 9, 2011, at 4:49 PM, Mohit Sindhwani wrote:
Maybe, if we had a column called 'published_date' and we did a query for data
within a date range.. the fastest way to get the information back would be to
have an index on that column. Suppose we sorted all the data by date - would
On Oct 9, 2011, at 5:07 PM, Black, Michael (IS) wrote:
Your assumption is that it is.
Why are you assuming that I'm assuming? Is that an assumption? 8^)
In any case, looking forward for your benchmark :)
___
sqlite-users mailing list
On Oct 9, 2011, at 10:46 PM, Black, Michael (IS) wrote:
create virtual table ftext using fts3(t text);
Try this instead:
create virtual table ftext using fts4(t text, prefix=1)
http://www.sqlite.org/fts3.html#section_6_2
___
sqlite-users mailing
On Oct 8, 2011, at 4:42 PM, Mohit Sindhwani wrote:
We have many table that have zid (unique) and all will usually have an index
on zid and (zid,...) for other queries.
If 'zid' is your primary key (INTEGER PRIMARY KEY), then there is no need to
index it again, as it's an alias for the table
On Oct 8, 2011, at 4:42 PM, Mohit Sindhwani wrote:
all will usually have an index on zid and (zid,...) for other queries.
Also such compound indices (zid,...) are pointless as they already have
maximum selectivity incorporating the primary key.
On Oct 7, 2011, at 11:46 PM, Mary Andes wrote:
Can anyone help me?
You cannot 'cd' from inside SQLite shell.
If you wish to open a specific database, simply point sqlite3 to it before hand:
% sqlite3 /path/to/my/db
http://www.sqlite.org/sqlite.html
On Oct 1, 2011, at 2:09 AM, Simon Slavin wrote:
Thanks for sharing. What about insert time (i.e. insert blob vs. write file)?
This would be far more dependent on your combination of operating system and
file system. Reading a file is pretty-much the same on everything. Creating
a new
Hello,
Say one has a hash value as an indexed key for a table.
From an efficiency point of view, would one be better off storing that key as
a blob or text? In other words, store the raw byte sequence directly or use a
hex text representation of it?
So, binary:
hashblob not
301 - 400 of 542 matches
Mail list logo