Big Table, myisampack or merge?
Hi lets say I want to record transactions on my site. This transaction table becomes over 1GB in six months. Which makes it very hard to read. The reading and deleting process becomes very slow. Would it be wise to make a new transaction table every month? Then recreate the merge table? How long would a process like that take because my members are creating transactions every second, so wouldnt all their queries be locked up while it was creating it? Or would using myisampack be better to just compress all the data. I display about 50 rows of their transaction history based on the User ID index. So decompressing 30 rows is that a big deal? -- View this message in context: http://www.nabble.com/Big-Table%2C-myisampack-or-merge--tf3128016.html#a8666880 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup: Copy MYSQL Directory or use MysqlDump
I used mysqlhotcopy and all is fine. Daniel da Veiga wrote: On 1/23/07, Alex Arul [EMAIL PROTECTED] wrote: Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Alex is right. Depending on how you'll want to be able to restore, and how big your database is you may use different methods for backup. I usually use mysqldump along with bzip2 for maximum compression, but I have used tar in the past. cp -av retains permissions, making it easy for restore, but its not a good option, as you can't compress data nor restore pieces of information in an easy way. You'll have to test and choose from multiple options in this matter. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8522321 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help indexing this query.
--- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help indexing this query.
--- EXPLAIN --- 1 SIMPLE e system cid NULL NULL NULL 0 const row not found 1 SIMPLE s ref sid,status,timeframe status 4 const 20438 Using where; Using temporary; Using filesort 1 SIMPLE m eq_ref PRIMARY,status PRIMARY 4 sc_72.s.mid 1 Using where --- members --- id int(20) No first varchar(255) No last varchar(255) No username varchar(25) No email varchar(255) No password varchar(25) No PRIMARY id --- ptsContestExclude --- cid int(20) No 0 mid int(20) No 0 UNIQUE (cid,mid) --- ptsSignups (all indexes) --- PRIMARY id INDEX (mid,ptsID) INDEX (status,ptsID) INDEX timeframe (mid, status, time) Dan Buettner-2 wrote: Andrew, can you post the result of EXPLAIN query for your query? Minus the FORCE INDEX too. Also the structure of the other 2 tables would be helpful as well. Thanks, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505966 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help indexing this query.
Thanks for the casting tip. Dan Buettner-2 wrote: Andrew, couple of suggestions: 1 - where you use s.status='2' change it to s.status=2 otherwise MySQL is likely casting your data from int to string, which is slow and also precludes using an index. 2 - in this case, instead of using a left join, try using a subquery: WHERE ... AND s.mid NOT IN (SELECT mid FROM ptsContestExclude) - or - change your index around, from UNIQUE (cid,mid) to UNIQUE (mid,cid) due to the way MySQL uses indices you need the queried-upon column(s) listed first(earlier) in the index. These might speed things up HTH, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- EXPLAIN --- 1 SIMPLE e system cid NULL NULL NULL 0 const row not found 1 SIMPLE s ref sid,status,timeframe status 4 const 20438 Using where; Using temporary; Using filesort 1 SIMPLE m eq_ref PRIMARY,status PRIMARY 4 sc_72.s.mid 1 Using where --- members --- id int(20) No first varchar(255) No last varchar(255) No username varchar(25) No email varchar(255) No password varchar(25) No PRIMARY id --- ptsContestExclude --- cid int(20) No 0 mid int(20) No 0 UNIQUE (cid,mid) --- ptsSignups (all indexes) --- PRIMARY id INDEX (mid,ptsID) INDEX (status,ptsID) INDEX timeframe (mid, status, time) Dan Buettner-2 wrote: Andrew, can you post the result of EXPLAIN query for your query? Minus the FORCE INDEX too. Also the structure of the other 2 tables would be helpful as well. Thanks, Dan On 1/22/07, altendew [EMAIL PROTECTED] wrote: --- ptsSignups -- id int(20) No mid int(20) No 0 ptsID int(20) No 0 pps double No 0 points double No 0 loginID varchar(255) No emailConfirm longtext No time timestamp Yes CURRENT_TIMESTAMP reasonForDeny longtext No status int(1) No 1 --- index (timeframe) --- timeframe (mid,status,time) --- query --- SELECT SUM(s.pps) as earned,m.id,m.username FROM ptsSignups s FORCE INDEX(timeframe) JOIN members m ON s.mid=m.id AND m.status='Member' LEFT JOIN ptsContestExclude e ON e.cid=1 AND e.mid=m.id WHERE s.status='2' AND s.time=2004-06-08 AND s.time2008-06-08+INTERVAL 1 DAY AND e.mid IS NULL GROUP BY s.mid HAVING earned0 ORDER BY earned DESC --- problem --- `ptsSignups` is a table listing everything my members have completed. Sometimes I like to run contests to see who has earned the most. `members` is a table that contains all my users. `ptsContestExclude` is a table of members of whom I would like to exclude from the contest. What I do first is group the table `ptsSignups` by member id, and calculate a sum of how much they earned. Then I reorder that sum in Descending order so the highest earned is on top. This `ptsSignups` table contains 82752 rows and is 75KB big. It runs extremely slow. I tried to create an index for it but it failed to increase performance. Any help is appreciated. -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505966 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8513291 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup: Copy MYSQL Directory or use MysqlDump
I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting Different Tables Based on Value. Case seems to fail.
Im trying to create one query here. I know why its failing because CASE can not be used like this, but how could I ever do a query like this. [CODE] SELECT b.* FROM bonus b JOIN CASE b.type WHEN 'custom' THEN 'bonusCustom g' WHEN 'pts' THEN 'bonusPts g' WHEN 'ptc' THEN 'bonusPtc g' END ON g.bid=b.id WHERE (b.hits IS NULL || b.hits0) ORDER BY RAND() [/CODE] Anyway this is possible! -- View this message in context: http://www.nabble.com/Selecting-Different-Tables-Based-on-Value.-Case-seems-to-fail.-tf2943218.html#a8230082 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting Different Tables Based on Value. Case seems to fail.
What do you mean assemble the SQL in Code. I am using MYSQL 4.1 and PHP. Daniel Kasak wrote: altendew wrote: Im trying to create one query here. I know why its failing because CASE can not be used like this, but how could I ever do a query like this. [CODE] SELECT b.* FROM bonus b JOIN CASE b.type WHEN 'custom' THEN 'bonusCustom g' WHEN 'pts' THEN 'bonusPts g' WHEN 'ptc' THEN 'bonusPtc g' END ON g.bid=b.id WHERE (b.hits IS NULL || b.hits0) ORDER BY RAND() [/CODE] Anyway this is possible! Why not assemble the SQL in code and *then* send it to MySQL? Otherwise, a very dodgy 'solution' would be to left-join all the tables, and then put the case statement in the select statement. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Selecting-Different-Tables-Based-on-Value.-Case-seems-to-fail.-tf2943218.html#a8230152 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
