[PHP-DB] Bank Account Register
Hi all, Please CC me on any reply - I get the digest :) I am working on a project where I need to implement something that most resembles a checkbook register, where I am tracking pre-paid hours bought vs. hours used on-site. I am getting a bit stuck on the best structure for the mysql table, and would like some suggestions. In the end, I need to be able to run a report where by date it displays hours purchased, and hours used like in a check register: DATEDESC. BAL. 2/23/04 Purchased 10 hours 10hrs. remaining 2/24/04 Job 2 used 5 hours 5hrs. remaining 2/24/04 Purchased 20 hours 25hrs. remaining Like so... Would it be best to have 1 table with a field for transaction type and keep all the ins and outs in one table, or have two tables one where I track purchased hours and the other where I track used hours? Thanks, Rogue -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Alterations
Hello Pavel, Tuesday, March 2, 2004, 2:05:44 PM, you wrote: PL> Note! IMHO [snip] Wow. I'm speechless - thank you, that was probably the single most useful post I've ever read on this list. You sir are a genius. PL> Change `site_user_id` FROM VARCHAR(32) TO CHAR(32) It was a char(32) - it holds an MD5 key, for some reason MySQL-Front exported the description as a varchar, sorry for the confusion! -- 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
[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:
-
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 with the rows in a specific
> PL> order. Note that the table will not remain in this order after inserts
> PL> and deletes. In some cases, it might make sorting easier for MySQL if
>
> Figured as much, thanks for the reference. I hope one day MySQL will
> produce a manual that isn't one horrendous long document per section.
>
> PL> You need to split the table and move "post messages" to another table if
> PL> you want to get higher perfomance for (2).
>
> With regard to disk seeking, here is my table structure:
>
> 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 (`threadi
Re[2]: [PHP-DB] Optimising LIMITs - alter table order by...
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 with the rows in a specific
PL> order. Note that the table will not remain in this order after inserts
PL> and deletes. In some cases, it might make sorting easier for MySQL if
Figured as much, thanks for the reference. I hope one day MySQL will
produce a manual that isn't one horrendous long document per section.
PL> You need to split the table and move "post messages" to another table if
PL> you want to get higher perfomance for (2).
With regard to disk seeking, here is my table structure:
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;
As well as moving the subject field to another table (because it's the
only non-defined length field), would another way of speeding up the
disk seek be to turn it from a varchar(200) into a char(200)? I know
it means a larger table size, but MySQL should then be able to
calculate exactly where to jump to in the file?
PL> All MySQL functions should be very optimized, but there may be some
PL> exceptions. BENCHMARK(loop_count,expression) is a great tool to find out
PL> if this is a problem with your query.
I will look at this now.
I had been toying with the idea of creating a cache table that held
the 200 most recent threads, pre-sequenced so I just bring back that
instead of having to query the database unless they go beyond that 200
limit.
Your comments (and MySQL manual posts) have been very useful, thank
you.
--
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] 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] 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? For example if I view the data in my table I can see the IDs like this: 1 2 3 250 5 6 323 (etc) This is simply where MySQL has gone back and re-used an old slot for a new record. However if I use your query technique for bringing back a block of records knowing they're in the exact right sequence - will MySQL just fragment this again over time? 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? -- 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] "global" variables
Yes agreed - but you cannot store them in a variable because they will be lost each time the page reloads. Why not store them in a cookie? Hello Davey, thank you for help! I now don't use a cookie but added a column for this purpose in my usertable in the db. Torsten -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
