Re: Innodb vs myisam
Thanks a lot On Mon, Apr 7, 2008 at 10:59 AM, Rob Wultsch [EMAIL PROTECTED] wrote: On Sun, Apr 6, 2008 at 9:57 PM, Eric Bergen [EMAIL PROTECTED] wrote: I don't see what the issue is. As Jay said the row counts in explain outputs are estimates. When running an explain query MySQL asks the storage engine how many rows it thinks are between a set of values for an index. Different storage engines use different methods to calculate row count. Both innodb and myisam estimate the row count based on statistics they keep on the distribution of keys in an index. MyISAM is more accurate than innodb with it's row count because of how it keeps statistics. Analyze table on a myisam table will count the number of unique values in an index (myisam/mi_check:update_key_parts). Innodb samples the key distribution in 8 different pages per index and does some calculations based on the tree structure of those pages (details innobase/btr/btr0cur.c:btr_estimate_number_of_different_key_vals). Thank you! I spent a bit trying to find details about how row counts were computed, but with no luck (I probably did not know how to know how to look...). Could the alternative I had proposed be accurate at all? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati
Multi Instances
Hi, I have two instances of Mysql 5.0 on a server Scientific Linux CERN SLC release 4.6 (Beryllium) and the instances work well, I turn on and turn off the instances by mysqld_multi. I would like to start automatically instances at the start of the server Linux. I took the script mysqld_multi.server and I added lines: # Comments to support chkconfig on RedHat Linux # chkconfig: 2345 64 36 # description: A very fast and reliable SQL database engine. and i copied it in the directory: /etc/init.d with the name mysqld then I typed the commands: chmod -x /etc/init.d/mysqld and chkconfig --add mysqld but if I start the service (service mysqld start), I have: WARNING: my_print_defaults command not found. Please make sure you have this command available and in your path. The command is available from the latest MySQL distribution. ABORT: Can't find command 'my_print_defaults'. This command is available from the latest MySQL distribution. Please make sure you have the command in your PATH. Ciao -- Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma tel:0657060500 email:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Instances
Am Dienstag, den 08.04.2008, 09:27 +0200 schrieb Stefano Elmopi: This command is available from the latest MySQL distribution. Please make sure you have the command in your PATH. Set the $PATH variable in the init script, and make sure it contains the path to the MySQL binaries. Norbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with ORDER BY using two colomns
Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with ORDER BY using two colomns
mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- 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]
Re: Help with ORDER BY using two colomns
Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- 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]
Re: Help with ORDER BY using two colomns
Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- 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] -- .:: Rafael Barbolo Lopes ::. http://barbolo.polinvencao.com/
Re: Help with ORDER BY using two colomns
Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- 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] -- .:: Rafael Barbolo Lopes ::. http://barbolo.polinvencao.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with ORDER BY using two colomns
A modification to my last email, try: SELECT *, IF(update != '', update + 10, date) AS o FROM my_table ORDER BY o DESC; +-+--++--+ | num | date | update | o| +-+--++--+ | 5 | 40 | 90 | 100 | | 2 | 10 | 60 | 70 | | 6 | 50 || 50 | | 4 | 30 || 30 | | 3 | 20 || 20 | | 1 |1 ||1 | +-+--++--+ Richard wrote: Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- 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] -- .:: Rafael Barbolo Lopes ::. http://barbolo.polinvencao.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with ORDER BY using two colomns
I think the easiest is to create a new logical column with the correct ordering, something like: SELECT *, IF(update != '', update, date) AS o FROM my_table ORDER BY o DESC; I note that both 'update' and 'date' are reserved works :) Also worth noting that this cannot be assigned an index and is therefore only good for small amounts of data. - If you have lots of data then you need a better solution. Set 'update' to NULL where there is no value and add the key: KEY(update,date) Then order by this key: ORDER BY update DESC, date DESC. Which when I try it gives: +-++--+ | num | date | update | +-++--+ | 5 | 40 | 90 | | 2 | 10 | 60 | | 6 | 50 | NULL | | 4 | 30 | NULL | | 3 | 20 | NULL | | 1 | 1 | NULL | +-++--+ Ben Richard wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num|date|update --- 1|1| 2|10|60 3|20| 4|30| 5|40|90 6|50| The required result would be : num|date|update --- 5|40|90 2|10|60 6|50| 4|30| 3|20| 1|1| Thanks in advance :) * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
Thanks, it works like a charm :) Ben Clewett a écrit : A modification to my last email, try: SELECT *, IF(update != '', update + 10, date) AS o FROM my_table ORDER BY o DESC; +-+--++--+ | num | date | update | o| +-+--++--+ | 5 | 40 | 90 | 100 | | 2 | 10 | 60 | 70 | | 6 | 50 || 50 | | 4 | 30 || 30 | | 3 | 20 || 20 | | 1 |1 ||1 | +-+--++--+ Richard wrote: Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- 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] -- .:: Rafael Barbolo Lopes ::. http://barbolo.polinvencao.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Relay_Master_Log_File question
Hi, I have a question regarding mysql master-slave replication's Relay_Master_Log_File. I hope someone can answer me. I have mysql setup to purge old logs that arent being used by master, and also the max log size is set as 500MB. I see that new logs are created with next number in order. What could happen when this numbering reaches the limit, say, mysql-bin.99. Will it break replication in anyway or will it just get reset, and the slave starts to pick up replication from the new log(which I presume to be mysql-bin.01) Thank you.
Re: [PHP] joins issues again
On Tue, Apr 8, 2008 at 7:28 AM, Steven Macintyre
[EMAIL PROTECTED] wrote:
Hi all,
I have the following SQL statement;
SELECT count( salesID ) AS count, branch_name, company_name, branch.branchID
FROM sales
LEFT JOIN IGuser ON sales.IGuid = IGuser.IGuid
LEFT JOIN branch ON IGuser.branchID = branch.branchID
LEFT JOIN company ON branch.companyID = '{$companyID}'
WHERE maincompanyid = '{$mcid}'
GROUP BY branch.branchID
ORDER BY branch_name ASC
However, i do not want those join records to be appended, only to return the
count of records from sales.
Can someone assist me with this? I have tried differance variants of joins
and none of the results are correct.
Sales tbl doesnt have the companyID, nor does IGuser
Steven,
Since this isn't a PHP-specific question, you'll probably receive
better responses on either the MySQL list (you didn't mention which
database system you're using, but I'll blindly and ignorantly assume
that's it), or at least the PHP-DB list. I'm CC'ing both of those for
you.
--
/Daniel P. Brown
Ask me about:
Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo.,
and shared hosting starting @ $2.50/mo.
Unmanaged, managed, and fully-managed!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
Richard, No problem, glad it works. But note: this is not scalable. If you have more than a few hundred rows, you may want to think about a better solution, like storing the order field permanetly and giving it an index :) Ben Richard wrote: Thanks, it works like a charm :) Ben Clewett a écrit : A modification to my last email, try: SELECT *, IF(update != '', update + 10, date) AS o FROM my_table ORDER BY o DESC; +-+--++--+ | num | date | update | o| +-+--++--+ | 5 | 40 | 90 | 100 | | 2 | 10 | 60 | 70 | | 6 | 50 || 50 | | 4 | 30 || 30 | | 3 | 20 || 20 | | 1 |1 ||1 | +-+--++--+ Richard wrote: Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks in advance :) -- 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] -- .:: Rafael Barbolo Lopes ::. http://barbolo.polinvencao.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
Thanks, This is for the unanswered list of questions, so the output list (not the list stored in the mysql database) should never go over 100. by scalable, do you mean alot of ressources being used or a long wait for the answer? Because I belive I Could just use a simple limit if I needed to have a limited number of results on one page. Every time a question is answered the update date will change, and the status could also change. So I don't see how to easily do this by creating another table. Ben Clewett a écrit : Richard, No problem, glad it works. But note: this is not scalable. If you have more than a few hundred rows, you may want to think about a better solution, like storing the order field permanetly and giving it an index :) Ben Richard wrote: Thanks, it works like a charm :) Ben Clewett a écrit : A modification to my last email, try: SELECT *, IF(update != '', update + 10, date) AS o FROM my_table ORDER BY o DESC; +-+--++--+ | num | date | update | o| +-+--++--+ | 5 | 40 | 90 | 100 | | 2 | 10 | 60 | 70 | | 6 | 50 || 50 | | 4 | 30 || 30 | | 3 | 20 || 20 | | 1 |1 ||1 | +-+--++--+ Richard wrote: Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90 6 | 50 | The required result would be : num | date| update --- 5 | 40 | 90 2 | 10 | 60 6 | 50 | 4 | 30 | 3 | 20 | 1 | 1 | Thanks
Re: Replication Relay_Master_Log_File question
On Tue, Apr 8, 2008 at 7:44 AM, Chanchal James [EMAIL PROTECTED] wrote: What could happen when this numbering reaches the limit, say, mysql-bin.99. Will it break replication in anyway or will it just get reset, and the slave starts to pick up replication from the new log(which I presume to be mysql-bin.01) Thank you. This came up a bit ago http://lists.mysql.com/mysql/211880 -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
Re: Help with ORDER BY using two colomns
On Tue, 8 Apr 2008, Richard [EMAIL PROTECTED] wrote: Kristian Myllym?ki a ?crit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? If you go to that URL, it's a section of the manual for MySQL 5.0. However, a feature of their on-line manuals, incredibly clever and useful for those of us with older or multiple versions, is near the upper left: * MySQL 6.0 Reference Manual * MySQL 5.1 Reference Manual * MySQL 5.0 Reference Manual * MySQL 3.23/4.0/4.1 Manual What's so incredibly clever and useful is that it takes you to *the corresponding section of that version's manual*, if it exists. However, in this case the MySQL 3.23/4.0/4.1 Manual link points to the 5.0 page, which is, I think, how it indicates that a feature was not in a previous version. And, indeed, a bit of Googling says MySQL 5.0 finally introduces functionality for Stored Procedures. HOWEVER. Kristian Myllym?ki gave the wrong URL. That was for the CASE *statement in stored procedures*. As it says near the bottom, The syntax of the CASE *statement* used inside stored routines differs slightly from that of the SQL CASE *expression* described in Section 11.3, Control Flow Functions. The CASE expression is what you can use in a WHERE, et cetera. The last bit is a link. If you follow it, to http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html, it describes the CASE expression for 5.0, and the link at the top points to the slightly different 4.1 version, http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Relay_Master_Log_File question
I searched for a few combination of words, and didnt reach anywhere. Thats how I ended up posting here. From this thread it looks like it never gets reset and keeps increasing the numbering; I would still wish if it reset someday without affecting the replication! Thanks for the link, Rob. On Tue, Apr 8, 2008 at 11:09 AM, Rob Wultsch [EMAIL PROTECTED] wrote: On Tue, Apr 8, 2008 at 7:44 AM, Chanchal James [EMAIL PROTECTED] wrote: What could happen when this numbering reaches the limit, say, mysql-bin.99. Will it break replication in anyway or will it just get reset, and the slave starts to pick up replication from the new log(which I presume to be mysql-bin.01) Thank you. This came up a bit ago http://lists.mysql.com/mysql/211880 -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
Not sure, but perhaps an even simpler method would be to consider the initial insert an update as well... so the update column would always have a value. Then the sort would (I believe) always be in the order you want, and if you need to differentiate between rows that are new vs rows that are updated, (date = update) = new. You can put an index on this field and not have the performance issue to worry about. Just a thought. andy Richard wrote: Thanks, This is for the unanswered list of questions, so the output list (not the list stored in the mysql database) should never go over 100. by scalable, do you mean alot of ressources being used or a long wait for the answer? Because I belive I Could just use a simple limit if I needed to have a limited number of results on one page. Every time a question is answered the update date will change, and the status could also change. So I don't see how to easily do this by creating another table. Ben Clewett a écrit : Richard, No problem, glad it works. But note: this is not scalable. If you have more than a few hundred rows, you may want to think about a better solution, like storing the order field permanetly and giving it an index :) Ben Richard wrote: Thanks, it works like a charm :) Ben Clewett a écrit : A modification to my last email, try: SELECT *, IF(update != '', update + 10, date) AS o FROM my_table ORDER BY o DESC; +-+--++--+ | num | date | update | o| +-+--++--+ | 5 | 40 | 90 | 100 | | 2 | 10 | 60 | 70 | | 6 | 50 || 50 | | 4 | 30 || 30 | | 3 | 20 || 20 | | 1 |1 ||1 | +-+--++--+ Richard wrote: Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY `date` DESC and I would like to replace it by something like this : ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE) I know this code is completly wrong, just to try and show you what I need ... Here is an example of what I want to achieve num | date| update --- 1 | 1 | 2 | 10 | 60 3 | 20 | 4 | 30 | 5 | 40 | 90
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
Richard, The query I gave you required the column 'o' to be calculated for each row at the time of gathering the data. When all rows have been gathered, the data will be stored and sorted in a temporary table. This temporary table will be in memory or on disk depending on the setting of the variable 'tmp_table_size'. Then the result will be pruned to the LIMIT and sent. You can see that the LIMIT does not help, MySql needs to know what the top rows will be, before it discards the bottom ones. It can't know this until all data has been gathered and sorted. This is all quite a lot of work for your MySql. Hence it is not regarded as scalable. Alternatively, store the order permanently on the table as a new field and give it an index: ALTER TABLE ADD o INT NOT NULL, ADD KEY(o); Now the job of MySql is easy, the query just follows the index. No temporary tables, no scanning all rows and no sorting before sending, and the LIMIT now works as you would hope. This is highly scalable. But you have to maintain the ordering field. If your brave, do it with a trigger :) Ben Richard wrote: Thanks, This is for the unanswered list of questions, so the output list (not the list stored in the mysql database) should never go over 100. by scalable, do you mean alot of ressources being used or a long wait for the answer? Because I belive I Could just use a simple limit if I needed to have a limited number of results on one page. Every time a question is answered the update date will change, and the status could also change. So I don't see how to easily do this by creating another table. Ben Clewett a écrit : Richard, No problem, glad it works. But note: this is not scalable. If you have more than a few hundred rows, you may want to think about a better solution, like storing the order field permanetly and giving it an index :) Ben Richard wrote: Thanks, it works like a charm :) Ben Clewett a écrit : A modification to my last email, try: SELECT *, IF(update != '', update + 10, date) AS o FROM my_table ORDER BY o DESC; +-+--++--+ | num | date | update | o| +-+--++--+ | 5 | 40 | 90 | 100 | | 2 | 10 | 60 | 70 | | 6 | 50 || 50 | | 4 | 30 || 30 | | 3 | 20 || 20 | | 1 |1 ||1 | +-+--++--+ Richard wrote: Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value.
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
Yes that would be easier, except that I would still have to create a tempory table to add 10 days onto the ones which have a status waiting for answer from customer and have not been answered for more than 10 days. This system is for customers who do not have an account yet to contact me. And will only be used by me an my team. In normal usage I will not be expecting the table of unanswered messages to be any longer than 10 or 20 lines, So I will leave it be for the moment as it works exactly as I want it to and as it will be on a server with alot of free ressources. Thanks for all your suggestions ! :) Andy Wallace a écrit : Not sure, but perhaps an even simpler method would be to consider the initial insert an update as well... so the update column would always have a value. Then the sort would (I believe) always be in the order you want, and if you need to differentiate between rows that are new vs rows that are updated, (date = update) = new. You can put an index on this field and not have the performance issue to worry about. Just a thought. andy Richard wrote: Thanks, This is for the unanswered list of questions, so the output list (not the list stored in the mysql database) should never go over 100. by scalable, do you mean alot of ressources being used or a long wait for the answer? Because I belive I Could just use a simple limit if I needed to have a limited number of results on one page. Every time a question is answered the update date will change, and the status could also change. So I don't see how to easily do this by creating another table. Ben Clewett a écrit : Richard, No problem, glad it works. But note: this is not scalable. If you have more than a few hundred rows, you may want to think about a better solution, like storing the order field permanetly and giving it an index :) Ben Richard wrote: Thanks, it works like a charm :) Ben Clewett a écrit : A modification to my last email, try: SELECT *, IF(update != '', update + 10, date) AS o FROM my_table ORDER BY o DESC; +-+--++--+ | num | date | update | o| +-+--++--+ | 5 | 40 | 90 | 100 | | 2 | 10 | 60 | 70 | | 6 | 50 || 50 | | 4 | 30 || 30 | | 3 | 20 || 20 | | 1 |1 ||1 | +-+--++--+ Richard wrote: Thanks, I think that your solution will be sufficient for my needs, however I would still like to know for my personal knowledge how to manage correctly this kind of need. And to make it more complicated I've just rearlised that there is another element to take into account, I would need to add 10 days to the update dates so they would place themselves in the correct position. This is how I need the system to work : Any new requests (without an update value) are ordered by date I want to be able to answer these requests (adding a time stamp to the update field and if the customer does not answer within 10 days, to re insert them into the list. But as the update timestamp will be 10 days old, I would like to add 10 days to the update while inserting them to the list (not changing the actual value inserted in the database just add 10 days during the reordering process.). I hope my explanation in understadable ... :) Rafael Barbolo Lopes a écrit : Can't you do Something like: ORDER BY (update,date) The major column of ordering would be update and the second date. I'm not sure about this solution On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello I've tried the following with mysql 4.1.11 SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' AND `update` '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE; It does not work but, is it my code that is wrong or is it just that case does not work with mysql 4.1.11 ? Thanks :) Kristian Myllymäki a écrit : mysql version? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html order by case when updated is not null then updated else created end desc; /Kristian On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello, I've got a table which containes two date colomns. The first one is called `date` and the second `update` In the first one I put the ticket creation date, and on update I add or change the update value. So the update colomn does not contain a value until the first update has been done. I would like to order the tickets by their last update value. And if this value does not exist use the date value. at the moment I use this : ORDER BY
Database Synchronisation Methods
Hi All, I am trying to figure out the best way to do database synchronisation. I have my database at the head office and the application runs, partly at remote/branch office. I am considering having a copy of the database there but how can I have it synchronise in real time so that the information is consistent when viewed from either side of the link at all times? Paul. o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o DISCLAIMER THE INFORMATION CONTAINED IN THIS COMMUNICATION IS CONFIDENTIAL AND MAY BE LEGALLY PRIVILEGED.IT IS INTENDED SOLELY FOR USE OF THE INDIVIDUAL OR ENTITY TO WHOM IT IS ADDRESSED AND OTHERS AUTHORIZED TO RECEIVE IT. IF YOU ARE NOT THE INTENDED RECIPIENT YOU ARE HEREBY NOTIFIED THAT ANY DISCLOSURE, COPYING, DISTRIBUTION OR TAKING ACTION IN RELIANCE OF THE CONTENTS OF THIS INFORMATION IS STRICTLY PROHIBITED AND MAY BE UNLAWFUL . NEW VISION PRINTING AND PUBLISHING LIMITED IS NEITHER LIABLE FOR THE PROPER, COMPLETE TRANSMISSION OF THE INFORMATION CONTAINED IN THIS COMMUNICATION, ANY DELAY IN ITS RECEIPT OR THAT THE MAIL IS VIRUS-FREE
Array as Image?
Hi:
I have successfully loaded an image into my database (I believe). When I go
to retrieve it, I get this...
array('c', '\xff\xd8\xff\xe0\\0...
How do I convert that into an image.
TIA,
Victor
RE: Database Synchronisation Methods
Hi Paul, You need to set up replication. Check http://dev.mysql.com/doc/refman/5.0/en/replication.html -Raj. -Original Message- From: Paul Ikanza [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 08, 2008 1:05 PM To: 'mysql@lists.mysql.com' Subject: Database Synchronisation Methods Hi All, I am trying to figure out the best way to do database synchronisation. I have my database at the head office and the application runs, partly at remote/branch office. I am considering having a copy of the database there but how can I have it synchronise in real time so that the information is consistent when viewed from either side of the link at all times? Paul. o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~o~ o DISCLAIMER THE INFORMATION CONTAINED IN THIS COMMUNICATION IS CONFIDENTIAL AND MAY BE LEGALLY PRIVILEGED.IT IS INTENDED SOLELY FOR USE OF THE INDIVIDUAL OR ENTITY TO WHOM IT IS ADDRESSED AND OTHERS AUTHORIZED TO RECEIVE IT. IF YOU ARE NOT THE INTENDED RECIPIENT YOU ARE HEREBY NOTIFIED THAT ANY DISCLOSURE, COPYING, DISTRIBUTION OR TAKING ACTION IN RELIANCE OF THE CONTENTS OF THIS INFORMATION IS STRICTLY PROHIBITED AND MAY BE UNLAWFUL . NEW VISION PRINTING AND PUBLISHING LIMITED IS NEITHER LIABLE FOR THE PROPER, COMPLETE TRANSMISSION OF THE INFORMATION CONTAINED IN THIS COMMUNICATION, ANY DELAY IN ITS RECEIPT OR THAT THE MAIL IS VIRUS-FREE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge Tables and Replication
The recent thread about merge tables led me to explore using them with replication. I see bug reports for the 4.0.** series regarding replication and merge tables, and I've read the manual about merge table problems in the 5.0.** series ( we are using 5.0.22 ), but I'm curious if anyone has any experiences to share? We've had replication working well now for almost 4 years, and as many tables are approaching 2 million records, it would appear that using a merge table design might be one way to accomplish a type of incremental backup. By this I mean, since all our data are static ( only inserts and selects ), the older data could be moved to a separate DB and only the active DB with the newest tables/records and the merge tables could be mysqldumped each night. Does this sound about right? Anybody see any road hazards? If not, and this line of thinking is reasonable, should the DB with the older records also be replicated so that when a new old records table needs to be created, I don't have to repeat everything on the slave? It always makes me uneasy, fiddling with something that is working... Thanks, --David.
Re: Array as Image?
What is your column's type? Is it blob?
See this, might help:
http://www.phpriot.com/articles/images-in-mysql
On Tue, Apr 8, 2008 at 2:18 PM, Victor Subervi [EMAIL PROTECTED]
wrote:
Hi:
I have successfully loaded an image into my database (I believe). When I
go
to retrieve it, I get this...
array('c', '\xff\xd8\xff\xe0\\0...
How do I convert that into an image.
TIA,
Victor
--
.:: Rafael Barbolo Lopes ::.
http://barbolo.polinvencao.com/
help with DB design / query please !
hello, im trying to make a DB for a message system. the best way i have made is this: *TABLE conversations* (informacion de cada conversacion) . i*d_conversation (bigint) count(smallint) updated every time a new message is added to this conversation lastmessagetime (timestamp) **updated every time a new message is added to this conversation** * *INDEX (id_conversation,lastmessage)** * * **TABLE user_conversations* (relacion usuario-conversacion) .. *id_user (int) id_conversation (bigint) **read (bool) ** **INDEX (id_conversation) INDEX (id_user)* *TABLE messages * (mensajes on every conversation) ... id_conversation (bigint) id_writer (int) message(varchar) time (timestamp) INDEX (id_conversation,time) my problem is that i need to query those things and i dont know how to do that. *Conversation list of a given user with `read`, number of messages (`count`) , date of last message (i store this value on `conversation` table) and who did it Order by time of the last message of each conversation* i have tried everything i could but im not getting a good solution: SELECT UC.id_conversation, UC.read, C.count, UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( lastmessage ) , M.message, M.id_usr FROM user_conversations UC, conversations C, messages M WHERE UC.id_usr = 1 AND C.id_conversation = UC.id_conversation AND M.id_conversation = UC.id_conversation AND M.time = C.lastmessage ORDER BY C.lastmessage DESC LIMIT 0,10 thats giving me an ALL in `conversations` as result id select_typetable type possible_keys key key_len ref rows Extra 1 SIMPLE UCrefid_conversation,id_usr id_usr 4 const 3 Using temporary; Using filesort 1 SIMPLE M reforderbytime orderbytime 8 netlivin3.UC.id_conversation1 1 SIMPLE C ALL id_conversation NULL NULL NULL3 Using where im completely lost here, ill really appreciate any help. thanks.
