On Mon, 28 Jul 2014 14:53:34 +0100 Jonathan Moules <jonathanmou...@warwickshire.gov.uk> wrote:
> Fair question, but I'm doing log analysis. Each set of tables will be > for a given server that's being analysed. The application that uses > the data is a read-only web-app, so my database needs to be optimised > for SELECT queries. When I do work like this, I make the logfile name the first column in the table. That name becomes part of the primary key. Depending on the situation, it may be the first or last component of the key. You have to do something to put the logfile data into tabular form. Might as well grab the filename and the file's timestamp while you're there. Then it doesn't matter how many systems are producing the logs, or over how much time. If you're really worried about keyspace, number the logfile names in a separate table, and use that number in your key. I wouldn't assume more tables is faster than fewer. A binary search locates one row in a billion in 29 comparisons; in a 100 million rows it it needs 26. Meaning: if you split your one-big-table into 10 smaller ones, each lookup will be 10% faster *assuming* nothing else interferes. While the improvement is marginal and speculative, this is certain: your SQL and your application will be more complex. HTH. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
