"Roger Binns" <[EMAIL PROTECTED]> writes: >> (by noticable I mean around 750 benchmark queries per second slower)). > > Since you only give the change, not the total, that is hard to put into > context. eg if the total is 750,000 then it is within the margin of error. > >> The main question is, is the method listed above the best way to >> improve the speed of a large table or should they all remain in the >> same table as splitting may cause other problems later on. > > My guess is a far simpler cause. Each row is stored with the fields > sequentially in the database. So the primary key and the second field > will be stored in the same page, but the primary key and the 190'th > field are likely to be on different pages (depending on field sizes). > That means twice as much I/O.
Could it be because of (taken from http://www.sqlite.org/faq.html#q10): ---------------------------------------------------------------------- Only the first 31 columns of a table are candidates for certain optimizations. You can put as many columns in an index as you like but indexes with more than 30 columns will not be used to optimize queries. ---------------------------------------------------------------------- Jarl -- Jarl Friis Softace ApS Omøgade 8, 2.sal 2100 København Ø. Denmark Phone: +45 26 13 20 90 E-mail: [EMAIL PROTECTED] LinkedIn: https://www.linkedin.com/in/jarlfriis
