> 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? http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#ALTER_TABLE 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? > http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Optimise_Overview 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`; (3) UNLOCK TABLES; 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
