> Hello Pavel,
> Tuesday, March 2, 2004, 7:20:03 AM, you wrote:
> PL> 2. ALTER TABLE `board` ORDER BY `threadid` DESC;
> I never knew you could do this - it's quite fascinating this list
> sometimes :)
> I do have a question though - if I use this "order by" table
> alteration, does MySQL remember it, or will I need to keep doing it
> every now and again?

ORDER BY allows you to create the new table with the rows in a specific
order. Note that the table will not remain in this order after inserts
and deletes. In some cases, it might make sorting easier for MySQL if
the table is in order by the column that you want to order it by later.
This option is mainly useful when you know that you are mostly going to
query the rows in a certain order; by using this option after big
changes to the table, you might be able to get higher performance.

> PL> Split your table in two tables, like:
> I can't see a real benefit of doing this. In my mind 675 bytes per
> thread isn't really that much. The only thing I could split away would
> be the subject of the thread (associated messages are held elsewhere)
> but in making a smaller table I'd just have to join the data back in
> again, surely?

It's not normally useful to split a table into different tables just
because the rows get ``big.'' To access a row, the biggest performance
hit is the disk seek to find the first byte of the row. After finding
the data, most modern disks can read the whole row fast enough for most
applications. The only cases where it really matters to split up a table
is if it's a MyISAM table with dynamic record format (see above) that
you can change to a fixed record size, or if you very often need to scan
the table and don't need most of the columns. See section 14 MySQL
Storage Engines and Table Types.

So, you need to alter table after each INSERT, like that

(1) LOCK TABLE `board` WRITE;
(2) ALTER TABLE `board` ORDER BY `threadid`;

You need to split the table and move "post messages" to another table if
you want to get higher perfomance for (2).

If your problem is with some specific MySQL expression or function, you
can use the BENCHMARK() function from the mysql client program to
perform a timing test:

mysql> SELECT BENCHMARK(1000000,1+1);
| BENCHMARK(1000000,1+1) |
|                      0 |
1 row in set (0.12 sec)

All MySQL functions should be very optimized, but there may be some
exceptions. BENCHMARK(loop_count,expression) is a great tool to find out
if this is a problem with your query.

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to