I did some tests and I compared the table bib99x with an index length of 35 and of 200 on the column "value".
In the first case after the upload of a file with 10K records, the size of the table was: ll bib99x.* -rw-rw---- 1 mysql mysql 8616 Mar 16 13:13 bib99x.frm -rw-rw---- 1 mysql mysql 48579572 Mar 16 14:06 bib99x.MYD -rw-rw---- 1 mysql mysql 7051264 Mar 16 14:06 bib99x.MYI In the second case it was: ll bib99x.* -rw-rw---- 1 mysql mysql 8616 Mar 16 14:25 bib99x.frm -rw-rw---- 1 mysql mysql 48579572 Mar 16 14:49 bib99x.MYD -rw-rw---- 1 mysql mysql 25404416 Mar 16 14:49 bib99x.MYI Between the two uploads I dropped the database to be sure that in the second case I had a clean instance. As you can see, while the .MYD has the same dimension, the .MYI is ~360% bigger. I also checked the database where we are uploading all 10M records and right now, after almost 6M records uploaded, the same files are 1.4G for the MYD and 1.2G for the MYI. If we consider that probably the only other table that needs an increment on the index size is the bib85x, an estimated increase of ~4G maximum on the DB size is absolutely not a big problem for us. Thanks, Giovanni On Fri, Mar 16, 2012 at 5:18 AM, Tibor Simko <[email protected]> wrote: > On Thu, 15 Mar 2012, Benoit Thiell wrote: >> The problem was that the index on the bibxxx tables that holds these >> fields has the default length of 35 and the 35 characters of this >> timestamp are always equal. So basically the index was useless and a >> single query took between 0.5 and 1 second. Now, with an index with a >> length of 200, the query takes less than a millisecond. > > This is what we did for some bibxxx tables in the past, notably for the > ones storing URLs (bib85x). Maybe we can do this more generally for > other tables too. Did you compare MYD and MYI table and index sizes > from before and after your change? > > Best regards > -- > Tibor Simko
