Hi,
Am Mittwoch, den 09.11.2005, 10:12 +0100 schrieb PaginaDeSpud:
> Hi,
> ALTER TABLE `mytable` ORDER BY `key_field` DESC
>
> Why if i set the default order by of my table, being key_field PRIMARY KEY
> AUTOINCREMENT, it only makes an initial order by but all rows inserted later
> are not ordered?
If you have PRIMARY KEY AUTOINCREMENT and never delete any row, the
order of the rows should be preserved. If you need to delete rows, write
lock the table, delte the rows, optimize the table, and unlock it.
> Is it too much difficult to add these new rows in this order?
If we tried this, we would have to move the file contents above the new
row up by the length of the row. In the average this means copying half
of the data file for each insert. We surely won't do that. (I assumed
you use the MyISAM storage engine.)
>
> My site makes thousands of querys asking for the last 15 rows inserted on
> this table, ordered by primary key desc, over 200.000 rows and it takes 0.68
> seconds on a dual XEON (4 processors), but it takes 0.08 seconds if no order
> is asked. It would be beatiful if you consider this option on future
> versions of mysql.
Do you use a LIMIT clause?
Anyway, for an educated guess it would be helpful to see the table
definition and the query.
Regards,
Ingo
--
Ingo Strüwing, Senior Software Developer
MySQL AB, www.mysql.com
Office: +49 30 43672407
Are you MySQL certified? www.mysql.com/certification
