Hi, we have a table with many (~0.5 billion) records and a geometry field which was defined as a simple "point". The `show table status` shows that the row format is dynamic, however, a simple point in the GIS representation has a fixed format (see: WKB: 21 bytes: 1 for MSB/LSB, 4 for type and 2x8 for the two doubles). We experienced that in this case, when we include this point field all `select`ions and indexing (alter table ... add [spatial] index ...) are much slower rather than if we exclude this "point" object.
I've tried the `alter table ... row_format=fixed`, on a simple table which has only one row. It says that it's ok: mysql> alter table ... row_format=fixed; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 but after all, `show table status` show that the row format is still dynamic. I suppose that in the case of dynamic row format, the engine is using an additional index table which assigns the real disk (act. in the .MYD file) position for all row. For such a table with ~0.5giga records it is at least 4igabytes; and in all of the indexing/selecting operations the engine should use this table (which may not fit in the memory also...) Our questions are: - why says the server after the `alter table ... row_format=fixed` statement that it is okay when it is not okay, and, the more important: - is it possible to force somehow a fixed row size in the case when one has a point (geometry) field? We _know_ that we only have points (objects with the size of 21 bytes). (Currently, we are using stock 5.0.21-standard and 5.0.22-standard on FC4, `arch` is ix86 and iamd64) cheers, Andras -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
