[sqlite] Last time analyze was ran
On 2016/04/12 3:56 AM, Jose I. Cabrera wrote: > > > Maybe this should be something to think about, and perhaps add it as part of > the results or reported items of .schema. Also, only update the date if > completion successful. The reason why it's important is that I have a script > that runs every week, but lately, it appears as if it is failing, but the log > files say it is running. However, the searches are getting slower and > slower. It should not be too hard to add it as part of the .schema repor, > **I think**. Thanks. It's probably not hard to add, but I don't have an opinion on that. In the meantime, while it isn't added yet, you should not have any difficulty establishing the truth. Do the queries you use suddenly speed up after you manually run analyze? Does the output of EXPLAIN QUERY change, especially with regard to the used indices? ANALYZE only really influence which Index gets used according to the current shape of the data. Your query plans should only change once the data shape changed significantly, and even then the plan might simply latch onto a better index /IF/ such an index exists. Perhaps the query can be optimized or better indices added? We'd have to see the schemata to assist there. In short - I very much doubt failing to complete ANALYZE would cause queries to gradually get slower over time. Further to that, I doubt very much that running ANALYZE frequently would gradually improve speed for a growing database. You may at best realize one or two abrupt speed-ups once a better index is selected for use. ANALYZE does no other magic that keeps the query process athletic. Simon already mentioned the need for ANALYZE is infrequent - I would go so far as to say I only ever use it twice, once during design (which is mostly superfluous) and then once later when the data has become a bit more bulky and the shape-to-be is clearly defined. Perhaps once more when a long-running DB has gone flabby and slow, but it's very rare in these cases that a simple ANALYZE fixes things, it usually requires more optimization. I believe running ANALYZE on a cron job or weekly script is doing no good at all. Perhaps a VACUUM might be of more use in these cases, but it comes with its own set of considerations. Cheers, Ryan
[sqlite] Last time analyze was ran
On 12 Apr 2016, at 2:56am, Jose I. Cabrera wrote: > Maybe this should be something to think about, and perhaps add it as part of > the results or reported items of .schema. Also, only update the date if > completion successful. The reason why it's important is that I have a script > that runs every week, but lately, it appears as if it is failing, but the log > files say it is running. However, the searches are getting slower and slower. You should not need to run ANALYZE very often. Perhaps once a year. The only exceptions are when your database schema changes or if the entire nature of the data in it changes. If your searches are getting noticeably slower then you probably have poor indexes (or no indexes) on your tables. If you'd like to post your CREATE TABLE, CREATE INDEX and SELECT commands we might be able to help you. Simon.
[sqlite] Last time analyze was ran
On Apr 12, 2016, at 2:38 AM, Simon Slavin wrote: > > On 12 Apr 2016, at 2:56am, Jose I. Cabrera wrote: > >> Maybe this should be something to think about, and perhaps add it as part of >> the results or reported items of .schema. Also, only update the date if >> completion successful. The reason why it's important is that I have a >> script that runs every week, but lately, it appears as if it is failing, but >> the log files say it is running. However, the searches are getting slower >> and slower. > > You should not need to run ANALYZE very often. Perhaps once a year. The > only exceptions are when your database schema changes or if the entire nature > of the data in it changes. > Yeah? the last time VACUUM was run seems like a much more useful data point. Or the number of database edits made since the last run, which is likely a more useful metric. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson
[sqlite] Last time analyze was ran
Maybe this should be something to think about, and perhaps add it as part of the results or reported items of .schema. Also, only update the date if completion successful. The reason why it's important is that I have a script that runs every week, but lately, it appears as if it is failing, but the log files say it is running. However, the searches are getting slower and slower. It should not be too hard to add it as part of the .schema repor, **I think**. Thanks. On Monday, April 11, 2016 3:40 PM, Simon Slavin wrote: On 11 Apr 2016, at 8:32pm, Jose I. Cabrera wrote: > Just resending, as I didn't see a post for this one. Thanks. I don't think anyone can think of a way to do it. Therefore no responses. You could have your software make a note and store it in the database but SQLite doesn't note it itself. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Last time analyze was ran
On 11 Apr 2016, at 8:32pm, Jose I. Cabrera wrote: > Just resending, as I didn't see a post for this one. Thanks. I don't think anyone can think of a way to do it. Therefore no responses. You could have your software make a note and store it in the database but SQLite doesn't note it itself. Simon.
[sqlite] Last time analyze was ran
Just resending, as I didn't see a post for this one. Thanks. On Monday, April 11, 2016 12:20 PM, Jose I. Cabrera wrote: Greetings! Is there a way to know when was the last time Analyze was ran on a database? My apologies if this was asked before, but I google a few searches to try to find the answer, but there were none that showed anything regarding the question. So, I thought to ask the gurus. Thanks. jos?
[sqlite] Last time analyze was ran
Greetings! Is there a way to know when was the last time Analyze was ran on a database? My apologies if this was asked before, but I google a few searches to try to find the answer, but there were none that showed anything regarding the question. So, I thought to ask the gurus. Thanks. jos?
