Re: [sqlite] Multiple indexes in SQLite, and selecting which to use
Hi Pavel, > Does INDEXED BY clause work for you? > http://www.sqlite.org/lang_select.html The page suggests that INDEXED BY can only be used in single-table queries. The report queries also join other smaller tables, some of them from an attached database, so I don't think this will work. However, you have given me an idea - maybe I could break it down to two queries. Use the INDEX BY on the big table to get a subset table, and do another SELECT on the subset. Thanks. Sholto (His Nerdship) > Is there any way the code can 'suggest' SQLite use a certain index? Pavel -- View this message in context: http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24999340.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] Multiple indexes in SQLite, and selecting which to use
Thanks for the feedback. I realise that Dan's suggestions won't necessarily fix the problem, but it would be very handy to know if my theory about poor choice of indexes is right. So, Dan, take your bow! - Original Message - From: "Dan Kennedy" To: "General Discussion of SQLite Database" Sent: Friday, August 14, 2009 11:15 PM Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which to use > How will that help him fix this problem, if the problem is that > SQLite's query optimizer is selecting a suboptimal index to use, and > there is no way to specify which index to use? > > On Aug 15, 2009, at 1:08 PM, His Nerdship wrote: > >> >> Good day, >> We have a puzzling problem with a large (1GB+) database. >> Most of our queries are based on 3 columns, say X, Y and Z. >> X is always the first in the index. However, sometimes the query >> involves a >> small range of Y and a larger range of Z, and sometimes the >> reverse. -- View this message in context: http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981973.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
[sqlite] Multiple indexes in SQLite, and selecting which to use
Good day, We have a puzzling problem with a large (1GB+) database. Most of our queries are based on 3 columns, say X, Y and Z. X is always the first in the index. However, sometimes the query involves a small range of Y and a larger range of Z, and sometimes the reverse. We first had an index based on X, Y & Z (in that order), and noticed that the results are fast when there was one X, one Y and many Z's. I check if the range is a single value, and if it is, I change the SQL to a straight '=', e.g: ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20; According to Mike Owens, using an equality or IN operator on Y allows Z to be indexed, speeding up the search. If Y is a range and we use "BETWEEN y1 AND y2" on it, then Z will not be indexed. This is what we found - the second search was much slower. However because sometimes the numbers are reversed, such that there are many Y's and few Z's, we added another index based on X, Z and Y, in that order. In this case, though, it didn't make any difference. It seems like SQLite does not select the correct index to use - it uses XYZ instead of XZY. I know Mr Hipp is reluctant to add the ability to specify which index to use - it is 'un-RDBMS like' in his words. Is there any way the code can 'suggest' SQLite use a certain index? Or at least confirm which index is being used? Also, is there a sensible maximum no of values we can put in an IN clause? Many of the queries involve all records over a month, and hitherto we have used, say, ..WHERE Date BETWEEN 20090701 AND 20090731; In this case would it work better with ..WHERE Date IN (20090701, 20090702, 20090703, ..., 20090731)? Thanks in advance -- View this message in context: http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981846.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
[sqlite] sqlite3_mprintf - C/C++ formatting 64-bit values?
Does anyone know the sqlite3_mprintf/sqlite3_vmprintf format specifier for 64-bit integers? I have tried %Ld and %I64d, but it just guillotines the string at that point. I am using Borland C++ Builder v6. Thanks in advance. -- View this message in context: http://www.nabble.com/sqlite3_mprintf---C-C%2B%2B-formatting-64-bit-values--tp22237504p22237504.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] Any concept of row number in SQLite?
Thanks Derrell, I only need the ROWIDs for the duration of a user edit, and I won't be doing a VACUUM while that is happening, so this sounds exactly what I need. When I first populate the GUI grid I will make a separate array of ROWID's, and these will be in the same order as the rows in the grid. I can then index into this array and do a SELECT based on that ROWID. Problem solved. Thanks a lot, guys. Sholto His Nerdship Pty Ltd Derrell Lipman wrote: > > On Thu, Feb 19, 2009 at 10:45 PM, His Nerdship > wrote: > >> >> Thank you Thomas, that was most helpful. >> I have just found the ROWID information >> (http://sqlite.org/lang_createtable.html#rowid) - this is what I was >> looking >> for! You have to know something exists before you can look for it >> I take it this value will not change, even if the contents of its row >> change? >> > > Be careful. Yes, that value *can* change if you depend on the implicit > ROWID. Specifically, if you delete rows and then VACUUM the rows will be > renumbered. You can avoid that with: > >> >> I can create my own INTEGER PRIMARY KEY column, which becomes an alias >> for >> the ROWID, but is there a way I can get the 'original' (I don't want to >> add >> a column at this stage)? > > > If you use your own INTEGER PRIMARY KEY then it becomes the ROWID and it > will not change upon vacuum. > >> >> If I call sqlite3_get_table(), it returns that ***result array (I'm using >> C++), but AFAIK that doesn't contain the ROWID. >> > > Just request it explicitly: > > SELECT ROWID, * FROM table_name; > > Derrell > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22114498.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] Any concept of row number in SQLite?
Thank you Thomas, that was most helpful. I have just found the ROWID information (http://sqlite.org/lang_createtable.html#rowid) - this is what I was looking for! You have to know something exists before you can look for it I take it this value will not change, even if the contents of its row change? One more question though (there always is) - I see a ROWID is automatically assigned to each row in the table. How does one access this value? I know I can create my own INTEGER PRIMARY KEY column, which becomes an alias for the ROWID, but is there a way I can get the 'original' (I don't want to add a column at this stage)? If I call sqlite3_get_table(), it returns that ***result array (I'm using C++), but AFAIK that doesn't contain the ROWID. As Mañuel would say, ¿qué? Sholto Thomas Briggs-2 wrote: > >"It won't be too big..." famous last words. > >I think the rowid is probably safe for what you're trying to do, > despite the well-intentioned advice others have given you against it. > >Also, if you think the underlying data may change, then I'm not > sure what good reading the whole table will give you... if the > underlying data changes, row #28 the first time around may not be row > #28 the second time around. Even if you use an ORDER BY clause, which > you'll absolutely have to do if you go the read-whole-table route. > >What you might want to do is build a map between row number in your > grid and rowid in the database... that way you aren't relying on the > rowids being set a certain way, nor relying on the rowids being > returned to your app in a particular order. > >-T > > On Thu, Feb 19, 2009 at 9:09 PM, His Nerdship > wrote: >> >> OK, thanks for the info. >> I will just do what I said before, namely read the whole table (it won't >> be >> too big) and extract the required row from the returned array. >> The reason I wanted a row ID was that all the fields in the display grid >> can >> be edited, so by the time I come to process it, any of them might have >> changed from the original in the database so I can't use them in a WHERE >> clause. >> At least I know now >> Thanks again >> >> >> P Kishor-3 wrote: >>> >>> On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship >>> wrote: >>>> >>>> Hi, >>>> I am converting a program from Paradox (stop laughing, please) to >>>> SQLite. >>>> Paradox has a useful feature where you can specify the actual index of >>>> a >>>> row >>>> in the table. This is handy when the table is displayed in a grid and >>>> you >>>> want the record corresponding to a row in that grid - you can just >>>> specify >>>> the index, say 28, of that grid row and it will get the record no 28 >>>> from >>>> the table. It spares the need for a SELECT statement, and is a lot >>>> more >>>> efficient. >>>> As a SQLite newbie, the only way I can see to do this is to read the >>>> whole >>>> table with sqlite3_get_table() and then get the required row from the >>>> returned array. This seems overkill when I just want a single record. >>> >>> There is the rowid, but I am not sure what you want to do... are you >>> expecting a database table to be a linear list of entries? Generally >>> one uses a spreadsheet for that kind of stuff. A SQL database doesn't >>> have an internal concept of order. You specify a criteria and the db >>> returns a SET of rows or records. You can constrain the SET by >>> specifying criteria (the WHERE clause), and you can impose an order on >>> the returned rows by specifying an ORDER clause. >>> >>> If you want just one specific row, just do a >>> >>> SELECT cols FROM table WHERE some_primary_key = ? >>> >>> If you don't want to specify and control your own primary key, you can >>> use the rowid which is something the db uses internally for its own >>> shenanigans. >>> >>> >>>> Is there a more compact way of doing this? >>>> Thanks in advance etc. >>>> Sholto >>> >>> >>> >>> -- >>> Puneet Kishor http://www.punkish.org/ >>> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ >>> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ >>> Sent from: Madison Wisconsin United States. >>> ___ >>&g
Re: [sqlite] Any concept of row number in SQLite?
OK, thanks for the info. I will just do what I said before, namely read the whole table (it won't be too big) and extract the required row from the returned array. The reason I wanted a row ID was that all the fields in the display grid can be edited, so by the time I come to process it, any of them might have changed from the original in the database so I can't use them in a WHERE clause. At least I know now Thanks again P Kishor-3 wrote: > > On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship > wrote: >> >> Hi, >> I am converting a program from Paradox (stop laughing, please) to SQLite. >> Paradox has a useful feature where you can specify the actual index of a >> row >> in the table. This is handy when the table is displayed in a grid and >> you >> want the record corresponding to a row in that grid - you can just >> specify >> the index, say 28, of that grid row and it will get the record no 28 from >> the table. It spares the need for a SELECT statement, and is a lot more >> efficient. >> As a SQLite newbie, the only way I can see to do this is to read the >> whole >> table with sqlite3_get_table() and then get the required row from the >> returned array. This seems overkill when I just want a single record. > > There is the rowid, but I am not sure what you want to do... are you > expecting a database table to be a linear list of entries? Generally > one uses a spreadsheet for that kind of stuff. A SQL database doesn't > have an internal concept of order. You specify a criteria and the db > returns a SET of rows or records. You can constrain the SET by > specifying criteria (the WHERE clause), and you can impose an order on > the returned rows by specifying an ORDER clause. > > If you want just one specific row, just do a > > SELECT cols FROM table WHERE some_primary_key = ? > > If you don't want to specify and control your own primary key, you can > use the rowid which is something the db uses internally for its own > shenanigans. > > >> Is there a more compact way of doing this? >> Thanks in advance etc. >> Sholto > > > > -- > Puneet Kishor http://www.punkish.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > Sent from: Madison Wisconsin United States. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22113562.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
[sqlite] Any concept of row number in SQLite?
Hi, I am converting a program from Paradox (stop laughing, please) to SQLite. Paradox has a useful feature where you can specify the actual index of a row in the table. This is handy when the table is displayed in a grid and you want the record corresponding to a row in that grid - you can just specify the index, say 28, of that grid row and it will get the record no 28 from the table. It spares the need for a SELECT statement, and is a lot more efficient. As a SQLite newbie, the only way I can see to do this is to read the whole table with sqlite3_get_table() and then get the required row from the returned array. This seems overkill when I just want a single record. Is there a more compact way of doing this? Thanks in advance etc. Sholto -- View this message in context: http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22112862.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
