Re: [PHP-DB] Optimising LIMITs - alter table order by...
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(100,1+1); ++ | BENCHMARK(100,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
[PHP-DB] Re: Alterations
Note! IMHO
Creating test table:
---
CREATE TABLE `thread` (
`threadid` int(10) unsigned NOT NULL auto_increment,
`site_user_id` varchar(32) NOT NULL default '',
`boardid` tinyint(3) unsigned NOT NULL default '0',
`subject` varchar(200) NOT NULL default '',
`modified` timestamp(14) NOT NULL,
`created` timestamp(14) NOT NULL,
`status` enum('L','P','H','D') NOT NULL default 'L',
PRIMARY KEY (`threadid`),
FULLTEXT KEY `subject` (`subject`),
KEY `boardid` (`boardid`),
KEY `site_user_id` (`site_user_id`),
KEY `created` (`created`),
KEY `status` (`status`)
) TYPE=MyISAM;
Insert data:
-
?php
set_time_limit( 6000 );
error_reporting( E_ALL );
define ( NUM, '20' );
mysql_pconnect( 'localhost', '---', '--' );
mysql_selectdb( 'richardboard' );
$name = Pavel Lobovich, Belarus;
$text = Smarty is a template engine for PHP. More specifically, it
facilitates a manageable way to separate application logic and content
from its presentation. This is best described in a situation where the
application programmer and the template designer play different roles,
or in most cases are not the same person. For example, let's say you are
creating a web page that is displaying a newspaper article. ;
$count = NUM;
$status = array( 'L', 'P', 'H', 'D' );
while ( $count-- ) {
$sql = 'INSERT INTO `thread` VALUES(NULL, ' . substr( $name, 0,
rand(6,24) ) . ', ' . rand( 1, 100) .', ' . substr( $text, 0, rand(
1, 255 ) ) . ', NOW(), NOW(), ' . $status[ rand(0,3) ] . ' )';
mysql_query( $sql );
}
?
Stats:
--
Data: ~27MB
Index: ~34MB
Total: ~62MB
Post action:
--
OPTIMIZE TABLE `thread`;
Query:
--
SELECT * FROM `thread` WHERE `boardid` = 10 AND `status` = L ORDER
BY `created` DESC
Explain:
--
thread ref boardid,status boardid 1 const
2182(Hm...)
where used; Using filesort
2182(rows) * 650(average) = 1.4MB
Alteration (1):
--
ALTER TABLE `thread` DROP INDEX `boardid`;
ALTER TABLE `thread` DROP INDEX `status`;
ALTER TABLE `thread` ADD INDEX `new_index` ( `boardid`, `status` );
Query:
--
SELECT * FROM `thread` WHERE `boardid` = 10 AND `status` = L ORDER
BY `threadid` DESC
Explain:
--
thread ref new_index new_index 2 const,const
360(Good)
where used; Using filesort
360(rows) * 650(average) = 234KB
Benckmark script:
--
...
function get_timestamp() {
list ($sec, $usec) = explode( ' ', microtime() );
return (float)$sec + (float)$usec;
}
$start = get_timestamp();
$sql = [TEST_QUERY];
$res = mysql_query( $sql );
$end = get_timestamp();
echo $end - $start;
...
Benchmark (Duron 1400, 512MB DDR333 PC2700, HD 5400RPM):
--
SELECT * FROM `thread` WHERE `boardid` = 10 AND `status` = L ORDER
BY `created` DESC
0.012056972503662 s
SELECT * FROM `thread` WHERE `boardid` = 10 AND `status` = L ORDER
BY `threadid` DESC
0.0099239349365234 s
Alteration (2):
--
ALTER TABLE `thread` CHANGE `threadid` `threadid` INT( 4 ) UNSIGNED NOT
NULL AUTO_INCREMENT
Main goals:
1. Less data size
2. `threadid` is now 32bit value, so comparation of two `threadid`
values is equal CMP EAX, EBX Assembler code ( 1 CPU tick ).
Alteration (3)
--
CREATE TABLE thread_subjects (
thread_ref int(4) unsigned NOT NULL default '0',
subject varchar(200) NOT NULL default '',
PRIMARY KEY (thread_ref)
) TYPE=MyISAM;
INSERT INTO `thread_subjects` SELECT `threadid`, `subject` FROM
`thread`;
Query:
--
SELECT * FROM `thread` LEFT JOIN `thread_subject` ON `thread_ref` =
`thread_id` WHERE `boardid` = 10 AND `status` = L ORDER BY
`threadid` DESC
Explain:
thread ref new_index new_index 2 const,const 398
where
used; Using filesort
thread_subjects eq_ref PRIMARY PRIMARY 4
thread.threadid 1
Time
--
0.015573978424072, but:
398(rows) * 63(average) = 25KB
I think the filesort will be executed more faster
Alteration (4)
--
Change `site_user_id` FROM VARCHAR(32) TO CHAR(32)
Query:
--
SELECT * FROM `thread` LEFT JOIN `thread_subject` ON `thread_ref` =
`thread_id` WHERE `boardid` = 10 AND `status` = L ORDER BY
`threadid` DESC
Time:
--
0.013375043869019 =)
Alteration (5):
--
Move `site_user_id` to `thread_subjects`;
398(rows) * 37(average) = 14KB, sorting is more faster
etc...
Best regards, Pavel
Hello Pavel,
Tuesday, March 2, 2004, 12:33:17 PM, you wrote:
PL
http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#ALTER_TABLE
PL ORDER BY allows you to create the new table
Re: [PHP-DB] Optimising LIMITs
Hi Richard! Step 1 --- Is it right? 1. `threadid` value is unique. 2. `threadid` value is auto_increment. 3. if `threadid`(2) `threadid`(1) = `created`(2) `created`(1). You can do the following: 1. Create the index on two fields: (boardid, status) 2. ALTER TABLE `board` ORDER BY `threadid` DESC; Here is new query to fetch a block of records: SELECT * FROM `board` WHERE `boardid` = 1 AND `status` = L LIMIT 100,50 Step 2 The average size of row in your table is 128,000,000 / 190,000 ~= 675 bytes. Split your table in two tables, like: CREATE TABLE `boardinfo` ( `threadid` INT(4) UNSIGNED AUTO_INCREMENT, PRIMARY KEY( `threadid` ), `boardid` INT(4) UNSIGNED, `status` ENUM( 'L', '' ), `created` TIMESTAMP ... (all fixed fields ) ) ALTER TABLE `boardinfo` CREATE INDEX `main` (`boardid,`status`) ALTER TABLE `boardinfo` ORDER BY `threadid` DESC CREATE TABLE `boarddata` ( `threadid_ref` INT(4) UNSIGNED, PRIMARY KEY ( `threadid_ref` ), `threaddata` LONGTEXT ) Here is your query after: SELECT * FROM `boardinfo` LEFT JOIN `boarddata` ON `threadid` = `threadid_ref` WHERE `boardid` = 1 AND `status` = L LIMIT 100,50 Sorry for my englist. Best regards, Pavel Hi all, I have what is probably a quite standard question and would love to know how you would all approach this scenario: I have a table in a database that has approx. 190,000 records in it. The table is currently 128MB in size and I'm happy that it is well constructed with no data duplication and sensible indexes. I'm using MySQL 3.28.58. and my question is about querying this volume of data efficiently. The table holds forum threads (several years worth) so a common query running on the table is to bring back the top 50 or 100 threads from a board within the forum. To do this I'm using a LIMIT on my query and for the paging through the data (i.e. the first 100 threads, the next 100, etc) I use the LIMIT n,x syntax. Threads are sorted by date (most recent to the top). This is fine and it works well but I'm concerned it's not the most efficient way to do this because the use of LIMIT is causing the whole table to be scanned each time. Here is a typical (simplified) query: SELECT * FROM thread WHERE thread.status='L' AND thread.boardid=1 ORDER BY created DESC LIMIT 100,50 This takes over 1.02 seconds to process. Running an EXPLAIN on my query shows that it's using one key (boardid), but in the Extra field it shows it is having to use a filesort on the data. 5701 rows were used in order to bring back the final 50 - that's every single thread for this board. What I'm trying to figure out is a more efficient way of selecting a block of 50 or 100 records from any point in my table without MySQL needing to sort/check them all first. One thought I did have was that the Primary Key on my table is called threadid - and I thought that instead of bring back the data in my original query, I could collect nothing but the thread IDs and then use a separate query that does something like: SELECT * FROM thread WHERE threadid IN (...) (where ... = all of the IDs previously selected). Would the fact that threadid is my primary key make the original LIMIT/sort faster? Any thoughts appreciated. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Uploading files
Try to change the following limits in your php.ini file: max_input_time = 120 ; Maximum amount of time each script may spend parsing request data memory_limit = 10M ; Maximum amount of memory a script may consume (8MB) ; Maximum size of POST data that PHP will accept. post_max_size = 10M ; Whether to allow HTTP file uploads. file_uploads = On ; Maximum allowed size for uploaded files. upload_max_filesize = 10M Pavel my form works fine with files 1M -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] INSERT dynamic values problem - Need help urgently!
Hi all:
I am trying to create a registration form whereby one of the user input is as
follows: (a drop down menu with values retrieved from DB):
-
td class=lighter width=350Class Code:/td
td class=lighter width=450
select class=textarea name=class_code
?
$sql = mysql_query(SELECT DISTINCT class_code FROM class);
while ($row = mysql_fetch_array($sql))
{
print OPTION VALUE=\$class_code\ .$row[class_code]. /option;
}
$result = $db-query($sql);
?
/select
1. The variable $class_code is undefined. Use:
print OPTION VALUE=\. $row[ class_code ] .\
.$row[class_code]. /option;
2. Use mysql_fetch_assoc() instead mysql_fetch_array() if you want to
fetch hashed array ONLY.
3. After the form submitted the variable $_REQUEST[ 'class_code' ]
contains selected item value, for example 1234. You can insert it into
database using the following SQL query:
$sql = 'INSERT INTO `class` (`class_code`, `another_field`, `third_etc`
) VALUES( ' . mysql_escape_string( $_REQUEST[ 'class_code' ] ) . ',
value, value_etc )';
Sorry for my english.
Pavel.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Uploading files
If your on a slow connection, it might be timing out. Try putting: set_time_limit(0); At the top of the page where the file is being uploaded to (not from), and see if that helps. You dont have to do it. Script execution will not start before all posted files are uploaded. If your browser refuses a connection and sends Server not found then check your form (especially action attribute ). Sorry for my english. Pavel. On Fri, 2004-02-27 at 10:29, nikos wrote: It is allready 8M - Original Message - From: Adam Voigt [EMAIL PROTECTED] To: nikos [EMAIL PROTECTED] Cc: PHP-mailist [EMAIL PROTECTED] Sent: Friday, February 27, 2004 5:13 PM Subject: Re: [PHP-DB] Uploading files Your max post size needs to be upped to, try making it 6M and see if that makes a difference. On Fri, 2004-02-27 at 10:05, nikos wrote: Hello list Allthough I set my PHP.in upload_max_file=4M my system refused to upload files bigger than 1M and the browser send an server not found error. Does anybody know anything about that? Thank you -- Adam Voigt [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Passing the value of a variable from PHP to JavaScript.
Here is the working code:
?php
echo
html
script language='JavaScript'
function tes(){
document.write('pJavaScript/p');
o=document.getElementById('myselect');
window.location.replace('http://192.168.23.1/coba/coba.php?vtes='+o.value);
}
/script
body
;
echo diforward ke javascript;
echo
select onchange='tes()' name='vtes' id=myselect
option value=''/option
option value='1'1/option
option value='2'2/option
/select
;
?
/body
/html
==
That's all
Best regards, Pavel.
I expect this result on url column of my internet browser:
http://192.168.23.1/coba/coba.php?vtes='1'
or
http://192.168.23.1/coba/coba.php?vtes='1'
But I only get this unexpected result:
http://192.168.23.1/coba/coba.php?vtes=
Lookslike the value of $vtes was not passed to JavaScript
interpreter.
Anybody of you have a solution for me?
Please teach me.
Thank you very much.
--
_
Web-based SMS services available at http://www.operamail.com.
From your mailbox to local or overseas cell phones.
Powered by Outblaze
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
