HELP!
Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :)
Re: HELP!
On Tuesday 22 August 2006 10:29, Peter Lauri wrote: Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :) Backup? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!
On Tue, 22 Aug 2006, Peter Lauri wrote: Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :) Recover from your backup. -- patrik_wallstrom-foodfight-[EMAIL PROTECTED]+46-733173956 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HELP!
Don't have any recent, or actually I do not know, because I am not in charge of the hosting part of this, only access to upload scripts and control MySQL via phpMyAdmin. :( -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 22, 2006 3:33 PM To: mysql@lists.mysql.com Subject: Re: HELP! On Tuesday 22 August 2006 10:29, Peter Lauri wrote: Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :) Backup? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- 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!
Not the best start to the day, if you have a fairly recent backup and have enabled binary logging then you can recover up to the point before you screwed the data. Peter Lauri wrote: Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!
On Tuesday 22 August 2006 10:37, Peter Lauri wrote: Don't have any recent, or actually I do not know, because I am not in charge of the hosting part of this, only access to upload scripts and control MySQL via phpMyAdmin. :( A good backup is always a good thing. I dump my databases every night so if I screw up, I can always go back without loosing more than 24 hours of updates. But then, my databases are not that big. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search Engine type search
On my website I'm looking to add a search box. I have a number of different database fields. Does anyone have any recommendations about how I can perform a search engine type search including the text fields. Regards Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: Search Engine type search
Neil Tompkins wrote: I have a number of different database fields. Does anyone have any recommendations about how I can perform a search engine type search including the text fields. Full-Text Search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
limitations of mySQL i.e. number of rows
Hi everyone I have a requirement to store in the region of 3 million entries per month (30 ish days) of data in a mySQL database totalling a maximum of 2 months (6 Million entries). The database will be 'probably' reside on a Sun V240. I have a similar size database running on an old Sun E450 and it runs like a dog (with three legs). I'll need to access the data and plot the output almost real time, or as close to it as possible. I may have to go down the route of buying another database like DB2, Informix or Oracle but I have no idea of the costs involved or whether I will get improved performance form these compared to mySQL. Anyone have experience of large datasets within mySQL. Thanks Regards Andy This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. 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 network is reserved by us.
RE: Search Engine type search
I followed the instructions, but when doing a search I get no results returned. here is my table CREATE TABLE /*!32300 IF NOT EXISTS*/ MyTest ( id int(10) unsigned NOT NULL auto_increment, title varchar(200) , body text , PRIMARY KEY (id), INDEX title (title,body)); I created FULL INDEX, but for some reason it is not showing above ? Any ideas Neil Date: Tue, 22 Aug 2006 11:54:43 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Search Engine type search Neil Tompkins wrote: I have a number of different database fields. Does anyone have any recommendations about how I can perform a search engine type search including the text fields. Full-Text Search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
mysql program wont work after mysqlserver update
Hi all, i developed a c+ programm which uses dbx to get access to my mysqlserver. Now I needed to change provider and so I have my own server here :-) the old server was a 4.0.24 and now I have a 4.1.21. the problem is: when I connect and want to access any data, I get the error message, that the translation is illegal I really dont know what this means! Collation is set to latin1_general_ci, as on the old server :-( do you have any ideas? Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___
Some questions on Storage engine
Hello all, I have a couple of questions on storage engine types wrt performance 1.. Will there be any performance degrade when we do joins with tables having different storage engines ? 2.. Where are the temporary tables created? (by default why not memory storage engine?) will it be helpful if all the temp tables are created with storage engine :MEMORY Thanks, Ratheesh Bhat K J
Re: limitations of mySQL i.e. number of rows
Andy, performance will generally depend on several factors: - size of each record - amount of RAM - speed of disks, for when RAM isn't enough - concurrent inserts/writes (using InnoDB or MyISAM tables?) At one level, 6 million records is no problem, and really not even very many. If every record is several MB in size though then it becomes another story. One system I designed has collected some 15 million records, and performance is still good, though records are fairly small (a few hundred bytes). Real-time reporting on old data in a Web browser is no longer feasible though, so I started generating summary data for live reporting (see below). I would suggest taking some time to architect it well, using archival tables for old data, possibly with the MERGE engine. If you need to plot data in close to real time with a large recordset, a good approach may be to automatically generate summary data into a side table periodically (every 3 minutes, every 30 minutes, whatever) off the last x minutes. If your summary process can run off a smallish live data table and key off an indexed field, it will hopefully run quickly each time. Then you plot off that summary table while the live processes go about their business, largely unaffected by your reporting. Read up on MySQL performance - there's a good section (optimization) in the online manual, and also I recommend Jeremy Zawodny's book High Performance MySQL from O'Reilly as a good source of information. Within reason, a well-architected database on modest hardware can often perform better than a poorly-architected one on a superfancy box. As an aside, I ran Sybase on both E420R's (very similar to the 450) and V240's at my last job, and the V240 ran circles around the 420. The V240's are nice little boxes (though I had no real complaints about the 420's, for what they were - cheap, reliable data storage). Loading the thing up on RAM should help. Best, Dan On 8/22/06, Andy Ford [EMAIL PROTECTED] wrote: Hi everyone I have a requirement to store in the region of 3 million entries per month (30 ish days) of data in a mySQL database totalling a maximum of 2 months (6 Million entries). The database will be 'probably' reside on a Sun V240. I have a similar size database running on an old Sun E450 and it runs like a dog (with three legs). I'll need to access the data and plot the output almost real time, or as close to it as possible. I may have to go down the route of buying another database like DB2, Informix or Oracle but I have no idea of the costs involved or whether I will get improved performance form these compared to mySQL. Anyone have experience of large datasets within mySQL. Thanks Regards Andy This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. 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 network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search Engine type search
On Tue, Aug 22, 2006 at 10:42:52AM +, Neil Tompkins wrote: On my website I'm looking to add a search box. I have a number of different database fields. Does anyone have any recommendations about how I can perform a search engine type search including the text fields. Have you looked at for exampole mnogosearch? Regards Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: limitations of mySQL i.e. number of rows
Sorry for the double post - I intended to mention this, but forgot. For your automatic summary generation you could consider a cron job piping in SQL, a la */3 * * * * mysql -D databaseName -e insert into summaryTable select now(), sum(numberColumn) from liveTable where datestamp date_sub( now(), interval 3 minute) though a more reliable approach in one of my own applications was to key off maximum ID found each time, rather than a date field, since data arrived from different sources asynchronously. More like (in pseudo SQL): select MAXID as max(id) from liveTable select MINID as minid from statusTable insert into summaryTable select now(), sum(numberColumn) from liveTable where id MINID and id = MAXID update statusTable set minid = MAXID so you're examining a moving window of ID numbers rather than a window of date/timestamps. Works for the odd case where an early datestamp slips into your table, or for when the database server is down for a period of time. As an alternative to cron, MySQL 5.1 has a new feature for scheduled events: http://dev.mysql.com/doc/refman/5.1/en/events-syntax.html 5.1 isn't production just yet but depending on your timeframe it might be worth a look. HTH, Dan On 8/22/06, Dan Buettner [EMAIL PROTECTED] wrote: Andy, performance will generally depend on several factors: - size of each record - amount of RAM - speed of disks, for when RAM isn't enough - concurrent inserts/writes (using InnoDB or MyISAM tables?) At one level, 6 million records is no problem, and really not even very many. If every record is several MB in size though then it becomes another story. One system I designed has collected some 15 million records, and performance is still good, though records are fairly small (a few hundred bytes). Real-time reporting on old data in a Web browser is no longer feasible though, so I started generating summary data for live reporting (see below). I would suggest taking some time to architect it well, using archival tables for old data, possibly with the MERGE engine. If you need to plot data in close to real time with a large recordset, a good approach may be to automatically generate summary data into a side table periodically (every 3 minutes, every 30 minutes, whatever) off the last x minutes. If your summary process can run off a smallish live data table and key off an indexed field, it will hopefully run quickly each time. Then you plot off that summary table while the live processes go about their business, largely unaffected by your reporting. Read up on MySQL performance - there's a good section (optimization) in the online manual, and also I recommend Jeremy Zawodny's book High Performance MySQL from O'Reilly as a good source of information. Within reason, a well-architected database on modest hardware can often perform better than a poorly-architected one on a superfancy box. As an aside, I ran Sybase on both E420R's (very similar to the 450) and V240's at my last job, and the V240 ran circles around the 420. The V240's are nice little boxes (though I had no real complaints about the 420's, for what they were - cheap, reliable data storage). Loading the thing up on RAM should help. Best, Dan On 8/22/06, Andy Ford [EMAIL PROTECTED] wrote: Hi everyone I have a requirement to store in the region of 3 million entries per month (30 ish days) of data in a mySQL database totalling a maximum of 2 months (6 Million entries). The database will be 'probably' reside on a Sun V240. I have a similar size database running on an old Sun E450 and it runs like a dog (with three legs). I'll need to access the data and plot the output almost real time, or as close to it as possible. I may have to go down the route of buying another database like DB2, Informix or Oracle but I have no idea of the costs involved or whether I will get improved performance form these compared to mySQL. Anyone have experience of large datasets within mySQL. Thanks Regards Andy This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. 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 network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql program wont work after mysqlserver update
Jürgen, not sure if this would be it, but password handling changed somewhat with MySQL 4.1. From your error msg it seems doubtful, but thought I'd mention it. http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html Dan On 8/22/06, Jürgen Ladstätter [EMAIL PROTECTED] wrote: Hi all, i developed a c+ programm which uses dbx to get access to my mysqlserver. Now I needed to change provider and so I have my own server here :-) the old server was a 4.0.24 and now I have a 4.1.21. the problem is: when I connect and want to access any data, I get the error message, that the translation is illegal … I really don't know what this means! Collation is set to latin1_general_ci, as on the old server :-( do you have any ideas? Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: mysql program wont work after mysqlserver update
Hi Dan, first of all thanks for your reply, but i already changed the password to the old_style password, otherwise i wouldnt be able to connect and authenticate at all! But there must be anything else :-/ I dont think that I need to write a new MySQL component to talk with my server, or let the 4.0 version run foever :-/ I know that there is a solution - but where :) Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___ -Ursprüngliche Nachricht- Von: Dan Buettner [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 22. August 2006 14:52 An: Jürgen Ladstätter Cc: mysql@lists.mysql.com Betreff: Re: mysql program wont work after mysqlserver update Jürgen, not sure if this would be it, but password handling changed somewhat with MySQL 4.1. From your error msg it seems doubtful, but thought I'd mention it. http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html Dan On 8/22/06, Jürgen Ladstätter [EMAIL PROTECTED] wrote: Hi all, i developed a c+ programm which uses dbx to get access to my mysqlserver. Now I needed to change provider and so I have my own server here :-) the old server was a 4.0.24 and now I have a 4.1.21. the problem is: when I connect and want to access any data, I get the error message, that the translation is illegal I really don't know what this means! Collation is set to latin1_general_ci, as on the old server :-( do you have any ideas? Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need your Help : Degrees in network between members/friends
Hey everybody,
i´m becoming desperate about the following problem.
We´ve an online-community / network.
There´s a network table ( sql create below ), in which we store the
relationships between the member.
I need a SQL-statement or a PHP-Function to calculate the 2nd, 3rd and
4th degrees between the members.
So, the friends of my friends, the friends of them and so on.
I would really appreciate any help and would be so happy if you can help
me with this problem :
Here is the function i´ve right now :
function getFriends($uid,$userself=0,$test=0){
$uid = mysql_real_escape_string(strip_tags($uid));
// Liefert alle ersten grad Freunde eines Members zurück
$res = mysql_query( (
SELECT bid AS frd_id FROM network n
WHERE ( n.mid IN (.$uid.) AND n.bid NOT IN (.$uid.,$userself) )
)
UNION
(
SELECT mid AS frd_id FROM network n
WHERE ( n.mid NOT IN (.$uid.,$userself) AND n.bid IN (.$uid.) )
),_CON);
$this-mySQLOptimizer(get friends);
if ($test==1)
{
die( (
SELECT bid AS frd_id FROM network n
WHERE ( n.mid IN (.$uid.) AND n.bid NOT IN (.$uid.,$userself) )
)
UNION
(
SELECT mid AS frd_id FROM network n
WHERE ( n.mid NOT IN (.$uid.,$userself) AND n.bid IN (.$uid.) )
));
}
$friends = array();
if ( mysql_num_rows($res) = 1 )
{
while ($data = mysql_fetch_array($res))
$friends[] = $data[frd_id];
}
else
{
$friends=0;
}
return $friends;
}
function getFriensdegrees($uid){
$uid = mysql_real_escape_string(strip_tags($uid));
// level 1 ermitteln
$retarry[1] = $this-getFriends($uid);
if (is_array($retarry[1]))
{
// level 2 ermitteln
$retarry[2] = $this-getFriends(explode(',',$retarry[1]),$uid,1);
if (is_array($retarry[2]))
{
$retarry[3] = $this-getFriends(explode(',',$retarry[2]),$uid);
}
else
{
$retarry[3] = 0;
}
if (is_array($retarry[3]))
{
$retarry[4] = $this-getFriends(explode(',',$retarry[3]),$uid);
}
else
{
$retarry[4] = 0;
}
$returnarray = array(1 = $retarry[1], 2 = $retarry[2], 3 =
$retarry[3], 4 = $retarry[4]);
}
else
{
$returnarray = ;
}
return $returnarray;
}
function countfriensdegrees($uid){
$uid = mysql_real_escape_string(strip_tags($uid));
// level 1 ermitteln
$retarry[1] = $this-getFriends($uid);
if (count($retarry[1]) = 1 is_array($retarry[1]))
{
// level 2 ermitteln
$retarry[2] = $this-getFriends(implode(,,$retarry[1]),$uid);
if (is_array($retarry[2]))
{
$retarry[3] = $this-getFriends(implode(,,$retarry[2]),$uid);
}
else
{
$retarry[3] = 0;
}
if (is_array($retarry[3]))
{
$retarry[4] = $this-getFriends(implode(,,$retarry[3]),$uid);
}
else
{
$retarry[4] = 0;
}
/*
// level 3 ermitteln
foreach($members_lev2 as $value){
$res = gForum::return_friends($value);
foreach($res as $val) $members_lev3d[] = $val;
unset($res);
}
$members_lev3 = array_unique($members_lev3d);
$members_lev3 = array_diff($members_lev3, $members_lev2);
$members_lev3 = array_diff($members_lev3, $members_lev1);
$members_lev3 = array_diff($members_lev3, array(0 = $member));
// level 4 ermitteln
foreach($members_lev3 as $value){
$res = gForum::return_friends($value);
foreach($res as $val) $members_lev4d[] = $val;
unset($res);
}
$members_lev4 = array_unique($members_lev4d);
$members_lev4 = array_diff($members_lev4, $members_lev3);
$members_lev4 = array_diff($members_lev4, $members_lev2);
$members_lev4 = array_diff($members_lev4, $members_lev1);
$members_lev4 = array_diff($members_lev4, array(0 = $member));
*/
$retarray = array(1 = sizeof($retarry[1]), 2 = sizeof($retarry[2]));
}
else
{
$retarray = array(1 = 0, 2 = 0);
}
return $retarray;
}
Network Table :
bid is the id of the person i´ve connected.
DROP TABLE IF EXISTS `db80049_36`.`network`;
CREATE TABLE `db80049_36`.`network` (
`id` int(11) NOT NULL auto_increment,
`mid` int(11) NOT NULL default '0',
`bid` int(11) NOT NULL default '0',
`reason` longtext NOT NULL,
`action` int(1) NOT NULL default '0',
`sent` datetime NOT NULL default '-00-00 00:00:00',
`action_on` datetime NOT NULL default '-00-00 00:00:00',
`reason_decline` longtext NOT NULL,
PRIMARY KEY (`id`),
KEY `mid` (`mid`),
KEY `bid` (`bid`),
KEY `accepted` (`action`),
KEY `mid_bid_together` (`mid`,`bid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need your Help : Degrees in network between members/friends
I use something like this in my e-commerce for degrees of my category of
products. I think it´s the same thing you want.
I get everything to an array and then use it to work.
By this follow function, i get all options for my select field, it´s all in
portuguese but i think it can help you.
function select_categorias(){
global $con;
$sql = SELECT
*
FROM
categoria
ORDER BY
cat_id, nome;
$con-Query($sql);
$counter = $con-count;
$categorias=array();
for($z=0;$z$con-count;$z++){
$con-Seek($z);
list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con-result;
$categorias[$id_cat]=array(id_cat = $id_cat, cat_id = $cat_id,
nome_cat = $nome_cat, icone_cat = $icone_cat, familia = $id_cat, );
$continua=$cat_id!=null $cat_id0;
if ($continua) {
$qual=$cat_id;
$categorias[$id_cat][indice]=;
while ($continua) {
$categorias[$id_cat][indice]=$categorias[$qual][nome_cat].$categorias[$id_cat][indice];
$continua=$categorias[$qual][cat_id]!=null
$categorias[$qual][cat_id]0;
$tem_pai=$cat_id!=null $cat_id0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
if (!strpos($categorias[$pai][familia],,
.$categorias[$id_cat][id_cat]))
$categorias[$pai][familia].=, .$categorias[$id_cat][id_cat];
$tem_pai=$categorias[$pai][cat_id]!=null
$categorias[$pai][cat_id]0;
$pai=$categorias[$pai][cat_id];
}
if ($continua) {
$qual=$categorias[$qual][cat_id];
}
}
}
}
reset($categorias);
$linhas=array();
foreach ($categorias as $categoria) {
$linhas[$categoria[indice].$categoria[nome_cat]]=array(id_cat =
$categoria[id_cat], nome_cat = $categoria[nome_cat], icone_cat =
$categoria[icone_cat], indice = $categoria[indice], familia =
$categoria[familia]);
}
ksort($linhas);
reset($linhas);
$espaco=nbsp;;
$espacos=0;
$anterior=;
$categorias=option value='0'Todo o site/option\n;
foreach ($linhas as $linha) {
if ($linha[indice]!=) {
if (substr($linha[indice],0,strlen($anterior))==$anterior
(strlen($linha[indice])!=strlen($anterior) || $espacos==0)) $espacos+=4;
if (substr($linha[indice],0,strlen($anterior))!=$anterior) $espacos-=4;
} else {
$espacos=0;
}
$anterior=$linha[indice];
$categorias.=option
value=\.$linha[familia].\.($espacos0?str_repeat($espaco,$espacos):).$linha[nome_cat]./option\n;
}
return $categorias;
}
ESV Media GmbH [EMAIL PROTECTED] escreveu na mensagem
news:[EMAIL PROTECTED]
Hey everybody,
i´m becoming desperate about the following problem.
We´ve an online-community / network.
There´s a network table ( sql create below ), in which we store the
relationships between the member.
I need a SQL-statement or a PHP-Function to calculate the 2nd, 3rd and 4th
degrees between the members.
So, the friends of my friends, the friends of them and so on.
I would really appreciate any help and would be so happy if you can help
me with this problem :
Here is the function i´ve right now :
function getFriends($uid,$userself=0,$test=0){
$uid = mysql_real_escape_string(strip_tags($uid));
// Liefert alle ersten grad Freunde eines Members zurück
$res = mysql_query( (
SELECT bid AS frd_id FROM network n
WHERE ( n.mid IN (.$uid.) AND n.bid NOT IN (.$uid.,$userself) )
)
UNION
(
SELECT mid AS frd_id FROM network n
WHERE ( n.mid NOT IN (.$uid.,$userself) AND n.bid IN (.$uid.) )
),_CON);
$this-mySQLOptimizer(get friends);
if ($test==1)
{
die( (
SELECT bid AS frd_id FROM network n
WHERE ( n.mid IN (.$uid.) AND n.bid NOT IN (.$uid.,$userself) )
)
UNION
(
SELECT mid AS frd_id FROM network n
WHERE ( n.mid NOT IN (.$uid.,$userself) AND n.bid IN (.$uid.) )
));
}
$friends = array();
if ( mysql_num_rows($res) = 1 )
{
while ($data = mysql_fetch_array($res))
$friends[] = $data[frd_id];
}
else
{
$friends=0;
}
return $friends;
}
function getFriensdegrees($uid){
$uid = mysql_real_escape_string(strip_tags($uid));
// level 1 ermitteln
$retarry[1] = $this-getFriends($uid);
if (is_array($retarry[1]))
{
// level 2 ermitteln
$retarry[2] = $this-getFriends(explode(',',$retarry[1]),$uid,1);
if (is_array($retarry[2]))
{
$retarry[3] = $this-getFriends(explode(',',$retarry[2]),$uid);
}
else
{
$retarry[3] = 0;
}
if (is_array($retarry[3]))
{
$retarry[4] = $this-getFriends(explode(',',$retarry[3]),$uid);
}
else
{
$retarry[4] = 0;
}
$returnarray = array(1 = $retarry[1], 2 = $retarry[2], 3 =
$retarry[3], 4 = $retarry[4]);
}
else
{
$returnarray = ;
}
return $returnarray;
}
function countfriensdegrees($uid){
$uid = mysql_real_escape_string(strip_tags($uid));
// level 1 ermitteln
$retarry[1] = $this-getFriends($uid);
if (count($retarry[1]) = 1 is_array($retarry[1]))
{
// level 2 ermitteln
$retarry[2] = $this-getFriends(implode(,,$retarry[1]),$uid);
if (is_array($retarry[2]))
{
$retarry[3] = $this-getFriends(implode(,,$retarry[2]),$uid);
}
else
{
$retarry[3] = 0;
}
if (is_array($retarry[3]))
{
$retarry[4] =
How to utilize 16 logical CPUs
Hello I have a server with the following specs but fear that the currently running MySQL-4.1 does not completely utilize it as the database feels to slow for the webservers although the system load is always only at about 10%: CPU: Quad Dualcore Xeon with Hyperthreading (4*2*2=16 logical cpus) Kernel: 2.6.17.6 (- NPTL threading) RAM: 16 GB OS: Debian GNU/Linux 3.1 sarge with i386 architecture Can it be that MySQL-4.1 does not share the load across all CPUs? Has MySQL-5.0 any improvements for this hardware? (I know that poor performance can result from inefficient queries and wrong configuration settings, too, but as this would get quite complex then I first want to rule out this cause) thanks, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to utilize 16 logical CPUs
Turn off the hyperthreading. You're not going to see superior performance with hyperthreading enabled, you're just going to create a massive thread-thrash with 16 logical cpus running 16 threads with not nearly enough resources to cover them. - Original Message - From: Christian Hammers [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 22, 2006 6:47 AM Subject: How to utilize 16 logical CPUs Hello I have a server with the following specs but fear that the currently running MySQL-4.1 does not completely utilize it as the database feels to slow for the webservers although the system load is always only at about 10%: CPU: Quad Dualcore Xeon with Hyperthreading (4*2*2=16 logical cpus) Kernel: 2.6.17.6 (- NPTL threading) RAM: 16 GB OS: Debian GNU/Linux 3.1 sarge with i386 architecture Can it be that MySQL-4.1 does not share the load across all CPUs? Has MySQL-5.0 any improvements for this hardware? (I know that poor performance can result from inefficient queries and wrong configuration settings, too, but as this would get quite complex then I first want to rule out this cause) thanks, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- 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: How to utilize 16 logical CPUs
Christian, can you post the output of SHOW STATUS and SHOW VARIABLES please? This will tell us how your server is configured, and how it has been running. It's possible you're not set up to best utilize your CPU power or memory. Dan On 8/22/06, Christian Hammers [EMAIL PROTECTED] wrote: Hello I have a server with the following specs but fear that the currently running MySQL-4.1 does not completely utilize it as the database feels to slow for the webservers although the system load is always only at about 10%: CPU: Quad Dualcore Xeon with Hyperthreading (4*2*2=16 logical cpus) Kernel: 2.6.17.6 (- NPTL threading) RAM: 16 GB OS: Debian GNU/Linux 3.1 sarge with i386 architecture Can it be that MySQL-4.1 does not share the load across all CPUs? Has MySQL-5.0 any improvements for this hardware? (I know that poor performance can result from inefficient queries and wrong configuration settings, too, but as this would get quite complex then I first want to rule out this cause) thanks, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- 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: mysql program wont work after mysqlserver update
Jürgen, I wonder if you have properly set collations in just some areas? show variables like collation%; show create table tablename; (for each table involved) I would think you'd want this all to match ... though I am not an expert in this area. Dan SHOW COLLATION On 8/22/06, Jürgen Ladstätter [EMAIL PROTECTED] wrote: Hi Dan, first of all thanks for your reply, but i already changed the password to the old_style password, otherwise i wouldnt be able to connect and authenticate at all! But there must be anything else :-/ I don't think that I need to write a new MySQL component to talk with my server, or let the 4.0 version run foever :-/ I know that there is a solution - but where :) Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___ -Ursprüngliche Nachricht- Von: Dan Buettner [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 22. August 2006 14:52 An: Jürgen Ladstätter Cc: mysql@lists.mysql.com Betreff: Re: mysql program wont work after mysqlserver update Jürgen, not sure if this would be it, but password handling changed somewhat with MySQL 4.1. From your error msg it seems doubtful, but thought I'd mention it. http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html Dan On 8/22/06, Jürgen Ladstätter [EMAIL PROTECTED] wrote: Hi all, i developed a c+ programm which uses dbx to get access to my mysqlserver. Now I needed to change provider and so I have my own server here :-) the old server was a 4.0.24 and now I have a 4.1.21. the problem is: when I connect and want to access any data, I get the error message, that the translation is illegal … I really don't know what this means! Collation is set to latin1_general_ci, as on the old server :-( do you have any ideas? Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___ -- 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: How to utilize 16 logical CPUs
Hallo On Tue, Aug 22, 2006 at 09:25:09AM -0500, Dan Buettner wrote: Christian, can you post the output of SHOW STATUS and SHOW VARIABLES please? This will tell us how your server is configured, and how it has been running. It's possible you're not set up to best utilize your CPU power or memory. Dan Ok, is below... bye, -christian- ql SHOW STATUS; ++-+ | Variable_name | Value | ++-+ | Aborted_clients| 14213 | | Aborted_connects | 40917 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 1368241082 | | Bytes_sent | 707048892 | | Com_admin_commands | 72 | | Com_alter_db | 0 | | Com_alter_table| 105 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 55932940| | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 6 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 295408 | | Com_dealloc_sql| 0 | | Com_delete | 1836616 | | Com_delete_multi | 26 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 12 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 211 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 931845 | | Com_insert_select | 1711| | Com_kill | 1 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 105666 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 441063 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 6736766 | | Com_savepoint | 0 | | Com_select | 775425766 | | Com_set_option | 14218713| | Com_show_binlog_events | 0 | | Com_show_binlogs | 35 | | Com_show_charsets | 0 | | Com_show_collations| 177479 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 107234 | | Com_show_databases | 186 | | Com_show_errors| 0 | | Com_show_fields| 243305 | | Com_show_grants| 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 478 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_ndb_status| 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_privileges| 0 | | Com_show_processlist | 162274 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status| 87704 | | Com_show_storage_engines | 0 | | Com_show_tables| 214232 | | Com_show_variables | 177525 | | Com_show_warnings | 0 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data| 0 | | Com_truncate | 0 | | Com_unlock_tables | 105666 | | Com_update | 4112919 | | Com_update_multi | 0 | | Connections|
AW: mysql program wont work after mysqlserver update
The output of the cmds is this: mysql show variables like collation%; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_general_cs | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql show create table admin; +---+--- + | Table | Create Table | +---+--- + | admin | CREATE TABLE `admin` ( `u_id` int(10) unsigned NOT NULL auto_increment, `member_id` varchar(13) collate latin1_general_ci NOT NULL default '', `password` text collate latin1_general_ci NOT NULL, PRIMARY KEY (`u_id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='admin identification' | +---+--- + 1 row in set (0.00 sec) mysql Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___ -Ursprüngliche Nachricht- Von: Dan Buettner [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 22. August 2006 16:33 An: Jürgen Ladstätter Cc: mysql@lists.mysql.com Betreff: Re: mysql program wont work after mysqlserver update Jürgen, I wonder if you have properly set collations in just some areas? show variables like collation%; show create table tablename; (for each table involved) I would think you'd want this all to match ... though I am not an expert in this area. Dan SHOW COLLATION On 8/22/06, Jürgen Ladstätter [EMAIL PROTECTED] wrote: Hi Dan, first of all thanks for your reply, but i already changed the password to the old_style password, otherwise i wouldnt be able to connect and authenticate at all! But there must be anything else :-/ I don't think that I need to write a new MySQL component to talk with my server, or let the 4.0 version run foever :-/ I know that there is a solution - but where :) Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___ -Ursprüngliche Nachricht- Von: Dan Buettner [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 22. August 2006 14:52 An: Jürgen Ladstätter Cc: mysql@lists.mysql.com Betreff: Re: mysql program wont work after mysqlserver update Jürgen, not sure if this would be it, but password handling changed somewhat with MySQL 4.1. From your error msg it seems doubtful, but thought I'd mention it. http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html Dan On 8/22/06, Jürgen Ladstätter [EMAIL PROTECTED] wrote: Hi all, i developed a c+ programm which uses dbx to get access to my mysqlserver. Now I needed to change provider and so I have my own server here :-) the old server was a 4.0.24 and now I have a 4.1.21. the problem is: when I connect and want to access any data, I get the error message, that the translation is illegal I really don't know what this means! Collation is set to latin1_general_ci, as on the old server :-( do you have any ideas? Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software
Re: Help Thanks!
On 8/22/06, Karl Larsen [EMAIL PROTECTED] wrote: $ mysqladmin -U make new password for root doesn't work. can you copy the error? if you want to set the root password you can use mysqladmin -u root password newpwd -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to utilize 16 logical CPUs
Hi Christian - thanks for posting that. I see you are making excellent use of the query cache; also your thread_cache is working well, seems that 30 is about the right number. You have a high number of connections, by my calculations about 15 per second. (connection / uptime, or 46944092/3052131 = 15.38) I would think that is introducing a fair bit of overhead for you - you might consider persistent connections as a possible improvement. Other than that, are your using InnoDB or MyISAM tables, or a mix? Your key_buffer_size is just 512 MB, and your innodb_buffer_pool is just 400 MB. I'd suggest reading the optimization portions of the online manual, and/or Jeremy Zawodny's High Performance MySQL, and start adjusting these cache settings. Seems like you're likely just not using all your RAM. If you have a large dataset, then you may well be disk-bound right now, which explains your low CPU utilization. I think Miles' comment was spot-on as well - without superfast subsystems (disk, network) and lots of RAM (and MySQL configured to use it), 16 logical CPUs or even 8 real CPU cores will be hard to keep busy. Dan On 8/22/06, Christian Hammers [EMAIL PROTECTED] wrote: Hallo On Tue, Aug 22, 2006 at 09:25:09AM -0500, Dan Buettner wrote: Christian, can you post the output of SHOW STATUS and SHOW VARIABLES please? This will tell us how your server is configured, and how it has been running. It's possible you're not set up to best utilize your CPU power or memory. Dan Ok, is below... bye, -christian- ql SHOW STATUS; ++-+ | Variable_name | Value | ++-+ | Aborted_clients| 14213 | | Aborted_connects | 40917 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 1368241082 | | Bytes_sent | 707048892 | | Com_admin_commands | 72 | | Com_alter_db | 0 | | Com_alter_table| 105 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 55932940| | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 6 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 295408 | | Com_dealloc_sql| 0 | | Com_delete | 1836616 | | Com_delete_multi | 26 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 12 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 211 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 931845 | | Com_insert_select | 1711| | Com_kill | 1 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 105666 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 441063 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 6736766 | | Com_savepoint | 0 | | Com_select | 775425766 | | Com_set_option | 14218713| | Com_show_binlog_events | 0 | | Com_show_binlogs | 35 | | Com_show_charsets | 0 | | Com_show_collations| 177479 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 107234 | | Com_show_databases | 186 | | Com_show_errors| 0 | | Com_show_fields| 243305 | | Com_show_grants| 0 | | Com_show_innodb_status | 0 | | Com_show_keys
What to index?
Hi,
I got 4 relatively big (for me at least) queries. At the moment, the data
in the tables are merely test data, but once the system goes into
production, I'm expecting millions of records in most of the tables. I'm
trying very hard thus to optimise my queries and tables to ensure I get a
reasonably good throughput once these tables start filling up, as altering
the tables for indexes once populated is obviously something I want to try
and avoid.
Having looked at one of the four queries (they're very similar), EXPLAIN
jeilds the following:
mysql EXPLAIN SELECT `UserChecks`.EntryID,
-`UserChecks`.Attribute,
-`UserChecks`.op,
-`UserChecks`.Value
- FROM `UserChecks`
- LEFT JOIN `User` ON `UserChecks`.UserID=`User`.EntryID
- LEFT JOIN `Group` ON `User`.GroupID=`Group`.EntryID
- LEFT JOIN `GroupTimes` ON `GroupTimes`.GroupID=`Group`.EntryID
- LEFT JOIN `Realm` ON `Group`.RealmID=`Realm`.EntryID
- LEFT JOIN `Client` ON `Realm`.ClientID=`Client`.EntryID
- WHERE `User`.isActive='y' AND
-`Group`.isActive='y' AND
-`Realm`.isActive='y' AND
-`Client`.isActive='y' AND
-`GroupTimes`.DOW=DAYOFWEEK(DATE_ADD('1970-01-01', INTERVAL
UNIX_TIMESTAMP() SECOND)) AND
-`GroupTimes`.StartTime DATE_FORMAT(DATE_ADD('1970-01-01',
INTERVAL UNIX_TIMESTAMP() SECOND), '%H:%i:%S') AND
-`GroupTimes`.StopTime DATE_FORMAT(DATE_ADD('1970-01-01',
INTERVAL UNIX_TIMESTAMP() SECOND), '%H:%i:%S') AND
-`Realm`.Realmname=SUBSTRING(TRIM(LOWER('[EMAIL PROTECTED]')),
(LOCATE('@', TRIM(LOWER('[EMAIL PROTECTED]'))) +1)) AND
-`User`.Username=LEFT(TRIM(LOWER('[EMAIL PROTECTED]')),
(LOCATE('@', TRIM(LOWER('[EMAIL PROTECTED]'))) -1))
- GROUP BY `UserChecks`.Attribute
- ORDER BY `UserChecks`.Attribute;
++-+++---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-+++---+--+-+--+--+-+
| 1 | SIMPLE | User | system | PRIMARY | NULL | NULL|
NULL |1 | Using temporary; Using filesort |
| 1 | SIMPLE | Group | system | PRIMARY | NULL | NULL|
NULL |1 | |
| 1 | SIMPLE | Realm | system | PRIMARY | NULL | NULL|
NULL |1 | |
| 1 | SIMPLE | Client | system | PRIMARY | NULL | NULL|
NULL |1 | |
| 1 | SIMPLE | UserChecks | ALL| NULL | NULL | NULL|
NULL |3 | Using where |
| 1 | SIMPLE | GroupTimes | ALL| NULL | NULL | NULL|
NULL |5 | Using where |
++-+++---+--+-+--+--+-+
6 rows in set (0.02 sec)
There are two items in my WHERE clause that does not use indexes. One on
the UserCheck table, and another on the GroupTimes table.
On the GroupTimes, I have DOW as a ENUM([1..7]) - I can more than likely add
a index, but with a cardinality of 7, I doubt it will be worth it.
StartTime and StopTime is a TIME field, I can add indexes on those. I'm
thus pretty sorted on the GroupTimes table (I think).
I have NO idea where in the WHERE clause I am referencing the UserChecks
tables (except for the GROUP BY and ORDER BY). I've added an index on the
Attribute column (VARCHAR(32)) but the EXPLAIN still shows that it is not
using the index
Is the above optimised? What can be done here to improve things
Thanks allot,
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot open dir '/database/information_schema'
I recently upgraded from MySql 4.0.x to 5.0.x and now my nightly backups are giving me this error (from mysqlhotcopy). Cannot open dir '/database/information_schema': No such file or directory at /usr/bin/mysqlhotcopy line 283. I don't know where it's getting that information_schema thing. That's definitely NOT in my /database directory. How do I get it to NOT error out on that? -- Scott Baker - RHCE Canby Telcom System Administrator 503.266.8253 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: limitations of mySQL i.e. number of rows
Thanks for the info Dan. I'll check the resources you mentioned. Regards Andy From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tue 8/22/2006 1:38 PM To: Andy Ford Cc: mysql@lists.mysql.com Subject: Re: limitations of mySQL i.e. number of rows Andy, performance will generally depend on several factors: - size of each record - amount of RAM - speed of disks, for when RAM isn't enough - concurrent inserts/writes (using InnoDB or MyISAM tables?) At one level, 6 million records is no problem, and really not even very many. If every record is several MB in size though then it becomes another story. One system I designed has collected some 15 million records, and performance is still good, though records are fairly small (a few hundred bytes). Real-time reporting on old data in a Web browser is no longer feasible though, so I started generating summary data for live reporting (see below). I would suggest taking some time to architect it well, using archival tables for old data, possibly with the MERGE engine. If you need to plot data in close to real time with a large recordset, a good approach may be to automatically generate summary data into a side table periodically (every 3 minutes, every 30 minutes, whatever) off the last x minutes. If your summary process can run off a smallish live data table and key off an indexed field, it will hopefully run quickly each time. Then you plot off that summary table while the live processes go about their business, largely unaffected by your reporting. Read up on MySQL performance - there's a good section (optimization) in the online manual, and also I recommend Jeremy Zawodny's book High Performance MySQL from O'Reilly as a good source of information. Within reason, a well-architected database on modest hardware can often perform better than a poorly-architected one on a superfancy box. As an aside, I ran Sybase on both E420R's (very similar to the 450) and V240's at my last job, and the V240 ran circles around the 420. The V240's are nice little boxes (though I had no real complaints about the 420's, for what they were - cheap, reliable data storage). Loading the thing up on RAM should help. Best, Dan On 8/22/06, Andy Ford [EMAIL PROTECTED] wrote: Hi everyone I have a requirement to store in the region of 3 million entries per month (30 ish days) of data in a mySQL database totalling a maximum of 2 months (6 Million entries). The database will be 'probably' reside on a Sun V240. I have a similar size database running on an old Sun E450 and it runs like a dog (with three legs). I'll need to access the data and plot the output almost real time, or as close to it as possible. I may have to go down the route of buying another database like DB2, Informix or Oracle but I have no idea of the costs involved or whether I will get improved performance form these compared to mySQL. Anyone have experience of large datasets within mySQL. Thanks Regards Andy This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. 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 network is reserved by us. This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. 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 network is reserved by us.
Replication shattered
We had to rebuild the slave on our slave DB. After the raid got rebuilt replication broke. We tried to rebuild it from scratch by doing the following: - RESET MASTER (on master) - mysqldump -e --master-data --single-transaction --databases db1 db2 dbout - on the slave: STOP SLAVE, RESET SLAVE, DROP DATABASE db1 and db2 - mysql dbout - CHANGE MASTER TO... master info - SLAVE START The problem is now the slave is saying there are duplicate key entries. Im not sure how this is possible. Any thoughts ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Windows Server Configuration
We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
David Lazo wrote: We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
I second what James recommends re: spindles and RAID 10. Better than RAID 5 for live data in my opinion; RAID 5 is decent for archival storage. You've got a pretty decent setup there otherwise - 4 CPU cores, 8 GB RAM - and you want to make sure your disks can keep things fed. As far as splitting things up: a general recommendation is to put logging (replication logging that is, not the error log necessarily) onto its own partition, ideally its own disks. Also consider putting MySQL's temp space on its own partition, ideally its own disks. Of course suddenly you're looking at a lot of disks if you really go whole-hog... The optimization section in the online manual is pretty decent, though some of the numbers are a bit dated (I saw one note this morning that said if you have at least 256 MB RAM...) Also Jeremy Zawodny's book High Performance MySQL is a good read, both in terms of optimizing your SQL/data strcuture and in choosing abnd setting up your hardware. (Third time today I've plugged that book - I don't own stock or anything, really) Dan On 8/22/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. -- Thanks, James -- 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: How to utilize 16 logical CPUs
On Tue, Aug 22, 2006 at 10:04:06AM -0500, Dan Buettner wrote: You have a high number of connections, by my calculations about 15 per second. (connection / uptime, or 46944092/3052131 = 15.38) I would think that is introducing a fair bit of overhead for you - you might consider persistent connections as a possible improvement. I'll ask the developers for this but AFAIK there's one connection for every access to the index.php and some other pages on one of three web servers. Other than that, are your using InnoDB or MyISAM tables, or a mix? It's only MyISAM at the moment. Your key_buffer_size is just 512 MB, and your innodb_buffer_pool is just 400 MB. Is increasing key_buffer to 768MB sufficient? I'd suggest reading the optimization portions of the online manual, and/or Jeremy Zawodny's High Performance MySQL, and start adjusting these cache settings. Seems like you're likely just not using all your RAM. If you have a large dataset, then you may well be disk-bound right now, which explains your low CPU utilization. There is very few to no I/O according to vmstat 1. The datadir is only 4GB in total so that I even guess that almost all tables will fit into the process memory. bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to utilize 16 logical CPUs
On Tue, Aug 22, 2006 at 10:20:47AM -0600, Michael Loftis wrote: One other quick ? -- Are you using a 64 bit (x86_64/EMT64) or 32-bit? If you're still in 32-bit mode the extra memory over 4Gb can actually slow the system down since it has to page between memory zones. It was choosen to run on i386 i.e. 32-bit mode for compatibility to Java and other servers in the farm. But as you can see not even the 3GB border is touched: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 5076 mysql 15 0 1434m 496m 4992 S 99.9 6.1 54931:46 mysqld But despite the 99% CPU, vmstat thinks the system is idle. procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 1 0 0 2959212 139684 383155600 1 01 1 6 3 88 3 0 0 0 2959584 139684 383051600 0 0 4785 0 7 4 89 0 1 0 0 2959212 139692 383154800 0 100 3453 0 7 4 89 0 6 0 0 2957104 139700 383076000 8 0 3696 0 7 5 87 0 2 0 0 2958592 139700 38310200032 0 4230 0 7 5 88 0 1 0 0 2958220 139712 383074800 0 596 3904 0 8 5 87 0 bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
Thanks for all the recommendations. On 8/22/06 1:11 PM, Dan Buettner [EMAIL PROTECTED] wrote: I second what James recommends re: spindles and RAID 10. Better than RAID 5 for live data in my opinion; RAID 5 is decent for archival storage. You've got a pretty decent setup there otherwise - 4 CPU cores, 8 GB RAM - and you want to make sure your disks can keep things fed. As far as splitting things up: a general recommendation is to put logging (replication logging that is, not the error log necessarily) onto its own partition, ideally its own disks. Also consider putting MySQL's temp space on its own partition, ideally its own disks. Of course suddenly you're looking at a lot of disks if you really go whole-hog... The optimization section in the online manual is pretty decent, though some of the numbers are a bit dated (I saw one note this morning that said if you have at least 256 MB RAM...) Also Jeremy Zawodny's book High Performance MySQL is a good read, both in terms of optimizing your SQL/data strcuture and in choosing abnd setting up your hardware. (Third time today I've plugged that book - I don't own stock or anything, really) Dan On 8/22/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. -- Thanks, James -- 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]
MySQL 4.0.27-client and MySQL-4.1.21 server
hi Why is it that I cannot connect to mysql-4.1 server using mysql-4.0.27 client? ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client Where is the RTFM on this? I just want to read it. -Wash http://www.netmeister.org/news/learn2quote.html DISCLAIMER: See http://www.wananchi.com/bms/terms.php -- +==+ |\ _,,,---,,_ | Odhiambo Washington[EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +==+ Any fool can paint a picture, but it takes a wise person to be able to sell it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.27-client and MySQL-4.1.21 server
http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html enable old-passwords in my.cnf Hope that helps, Ken - Original Message - From: Odhiambo Washington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 22, 2006 1:38 PM Subject: MySQL 4.0.27-client and MySQL-4.1.21 server hi Why is it that I cannot connect to mysql-4.1 server using mysql-4.0.27 client? ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client Where is the RTFM on this? I just want to read it. -Wash http://www.netmeister.org/news/learn2quote.html DISCLAIMER: See http://www.wananchi.com/bms/terms.php -- +==+ |\ _,,,---,,_ | Odhiambo Washington [EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +==+ Any fool can paint a picture, but it takes a wise person to be able to sell it. -- 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: Cannot open dir '/database/information_schema'
Disregard this question, it was a problem with a script I wrote, not a problem with mysqlhotcopy. My bad! Scott Scott Baker wrote: I recently upgraded from MySql 4.0.x to 5.0.x and now my nightly backups are giving me this error (from mysqlhotcopy). Cannot open dir '/database/information_schema': No such file or directory at /usr/bin/mysqlhotcopy line 283. I don't know where it's getting that information_schema thing. That's definitely NOT in my /database directory. How do I get it to NOT error out on that? -- Scott Baker - RHCE Canby Telcom System Administrator 503.266.8253 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding field in all tables
Is there any way to find a column name (or, better yet, a partial column name) in all tables within a data base? I inherited a complex and totally undocumented data base, and need to find out (for example) which tables have a column name like xxx_exported. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding field in all tables
Love it when that happens :) Fastest way I can think of is dumping out the structure of the database with mysqldump -d database.sql and then searching the output to see where those columns appear -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 22, 2006 2:22 PM To: mysql@lists.mysql.com Subject: Finding field in all tables Is there any way to find a column name (or, better yet, a partial column name) in all tables within a data base? I inherited a complex and totally undocumented data base, and need to find out (for example) which tables have a column name like xxx_exported. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- 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: MySQL 4.0.27-client and MySQL-4.1.21 server
http://dev.mysql.com/doc/mysql/en/old-client.html Regards, Chris. - Original Message - From: Odhiambo Washington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 22, 2006 7:38 PM Subject: MySQL 4.0.27-client and MySQL-4.1.21 server hi Why is it that I cannot connect to mysql-4.1 server using mysql-4.0.27 client? ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client Where is the RTFM on this? I just want to read it. -Wash http://www.netmeister.org/news/learn2quote.html DISCLAIMER: See http://www.wananchi.com/bms/terms.php -- +==+ |\ _,,,---,,_ | Odhiambo Washington[EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +==+ Any fool can paint a picture, but it takes a wise person to be able to sell it. -- 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: Finding field in all tables
:) I went a similar way, actually. I pushed the output of a SHOW TABLES into a text file, sucked it into Excel, and used a formula to make the necessary SHOW COLUMNS FROM `tbl_name` LIKE expression. It took me a minute or two to figure out I needed the back-ticks - the very first table is named ORDER, and I thought there was something wrong with my plan. Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 22, 2006 2:41 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: RE: Finding field in all tables Love it when that happens :) Fastest way I can think of is dumping out the structure of the database with mysqldump -d database.sql and then searching the output to see where those columns appear -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 22, 2006 2:22 PM To: mysql@lists.mysql.com Subject: Finding field in all tables Is there any way to find a column name (or, better yet, a partial column name) in all tables within a data base? I inherited a complex and totally undocumented data base, and need to find out (for example) which tables have a column name like xxx_exported. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- 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: Finding field in all tables
Is there any way to find a column name (or, better yet, a partial column name) in all tables within a data base? 1. If the db is not in 5.0, mysqldump the DML to a file, run that file in an instance of MySQL 5.x. 2. Query information_schema. PB - Jerry Schwartz wrote: Is there any way to find a column name (or, better yet, a partial column name) in all tables within a data base? I inherited a complex and totally undocumented data base, and need to find out (for example) which tables have a column name like xxx_exported. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 8/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding field in all tables
I haven't ventured into MySQL 5.x, I have enough trouble working with this house of cards I was handed. I'll try to remember this for the future, though. Thanks to all. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Прошу помощи
Здравствуйте, Гордеев. Вы писали 20 августа 2006 г., 12:58:35: Здравствуйте! Интересует такой момент. Мой сайт хостится у местного провайдера. Они создали базу mySQL где у меня и лежат таблицы для моего портала. Возникла необходимость перенести модули (гостевая книга и фотогалерея) на новый хост. Файлы модулей я скопировал, проблем с этим не возникло. Однако, после импорта таблиц этих модулей, все русские буквы превратились в знаки вопросов. Как я понял дело в кодировке. Кодировка базы - Юникод UTF-8. Импортируемые таблицы находятся к Windows-1251. Но что интересно... После импорта, базы сохраняют свои истинные буквы (нормальные русские буквы). Видимо дело тут на выходе с таблицы. Как можно исправит такой глюк? Заранее Вас благодарю! _ С уважением, Гордеев Константин E-Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] Web: www.gymnasium.ru Если уже решил траблу, то на всякий случай прочитай эту статью: http://phpclub.ru/faq/wakka.php?wakka=Mysql41Rus Она мне помогла в свое время. -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding field in all tables
Jerry I haven't ventured into MySQL 5.x, I have enough trouble working with this house of cards I was handed. I'll try to remember this for the future, though. All you need to do is install mysql 5 in any box, run the dump script, then execute one query: SELECT LOWER(table_name) AS 'Table', LOWER(column_name) AS 'Column', ordinal_position As 'Ordinal Position', column_default AS 'Default', UPPER(data_type) AS 'Type', character_maximum_length AS 'Max Len', character_octet_length AS 'Octet Len', numeric_precision AS 'Precision', numeric_scale AS 'Numeric Scale', is_nullable AS 'Null', character_set_name AS 'Character Set', collation_name AS 'Collation' FROM information_schema.columns WHERE table_schema = 'yourdbname' ORDER BY table_name,'Ordinal Position'; and there are all your tables with all their columns. Absent a modelling tool, there's not a quicker way to get that info. PB - Jerry Schwartz wrote: I haven't ventured into MySQL 5.x, I have enough trouble working with this house of cards I was handed. I'll try to remember this for the future, though. Thanks to all. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 8/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding field in all tables
Unfortunately, I have no experience running two versions of MySQL on the same box, and we have no development environment. That contributes to my timidity. By the way, I thought I read that the list wouldn't accept HTML-coded messages; but yours came through that way. Did I misread something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: limitations of mySQL i.e. number of rows
If you are going to be storing only 2 months of data at a time, I would suggest using merge tables. Use one table per month. This makes it very easy to delete data, simple redeclare what the union is. Then you can also retain previous months without affecting performance. One of the databases I manage adds up to 2.5 million records per day. Like you, I only need about 3 months worth (currently 165 million records). Deleting millions of records at a time would take quite a while and slow everything down. Redeclaring a merge table is instantaneous. I don't need to plot output, but I do need to check for duplicate informatio within a 24 hour period when adding the batch of daily records. Joining 2.5 million records with 165 million and checking for duplicates within a range I discovery MySQL just can't do, at least not in a single query. It would just never finish. By processing in small batches, the joining takes about 30 minutes, but that is essentially hitting the entire database. This is on a dual Xeon (not dual core), Debian Linux. So if you have performance issues, try processing in increments. Or, the free versions of DB2, Oracle, SQL Server may suite your needs. I have seen three legged dogs run pretty fast. - Original Message - From: Andy Ford [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 22, 2006 7:56 AM Subject: limitations of mySQL i.e. number of rows Hi everyone I have a requirement to store in the region of 3 million entries per month (30 ish days) of data in a mySQL database totalling a maximum of 2 months (6 Million entries). The database will be 'probably' reside on a Sun V240. I have a similar size database running on an old Sun E450 and it runs like a dog (with three legs). I'll need to access the data and plot the output almost real time, or as close to it as possible. I may have to go down the route of buying another database like DB2, Informix or Oracle but I have no idea of the costs involved or whether I will get improved performance form these compared to mySQL. Anyone have experience of large datasets within mySQL. Thanks Regards Andy This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. 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 network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding field in all tables
Jerry Schwartz wrote: Unfortunately, I have no experience running two versions of MySQL on the same box, and we have no development environment. That contributes to my timidity. Just one 'puter? Yikes. By the way, I thought I read that the list wouldn't accept HTML-coded messages; but yours came through that way. Did I misread something? I think Thunderbird sends msgs in both formats. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 8/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to utilize 16 logical CPUs
Your mysql db is only using 496Mb of ram? i have a server with 1GB ram where mysql uses more mem than yours does - you should probably tune your server system variables a bit to utilize your memory better. Christian Hammers wrote: On Tue, Aug 22, 2006 at 10:20:47AM -0600, Michael Loftis wrote: One other quick ? -- Are you using a 64 bit (x86_64/EMT64) or 32-bit? If you're still in 32-bit mode the extra memory over 4Gb can actually slow the system down since it has to page between memory zones. It was choosen to run on i386 i.e. 32-bit mode for compatibility to Java and other servers in the farm. But as you can see not even the 3GB border is touched: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 5076 mysql 15 0 1434m 496m 4992 S 99.9 6.1 54931:46 mysqld But despite the 99% CPU, vmstat thinks the system is idle. procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 1 0 0 2959212 139684 383155600 1 01 1 6 3 88 3 0 0 0 2959584 139684 383051600 0 0 4785 0 7 4 89 0 1 0 0 2959212 139692 383154800 0 100 3453 0 7 4 89 0 6 0 0 2957104 139700 383076000 8 0 3696 0 7 5 87 0 2 0 0 2958592 139700 38310200032 0 4230 0 7 5 88 0 1 0 0 2958220 139712 383074800 0 596 3904 0 8 5 87 0 bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some questions on Storage engine
Ratheesh K J wrote: Hello all, I have a couple of questions on storage engine types wrt performance 1.. Will there be any performance degrade when we do joins with tables having different storage engines ? Depends on the type of queries I think. There could be something here http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html or even http://dev.mysql.com/doc/refman/5.1/en/select.html 2.. Where are the temporary tables created? (by default why not memory storage engine?) will it be helpful if all the temp tables are created with storage engine :MEMORY You can't store them in memory. http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What to index?
Chris Knipe wrote:
Hi,
I got 4 relatively big (for me at least) queries. At the moment, the
data in the tables are merely test data, but once the system goes into
production, I'm expecting millions of records in most of the tables.
I'm trying very hard thus to optimise my queries and tables to ensure I
get a reasonably good throughput once these tables start filling up, as
altering the tables for indexes once populated is obviously something I
want to try and avoid.
It's the best way unfortunately because then you can see exactly how the
database will react with the amount of data you're using.
Is the above optimised? What can be done here to improve things
None of that is using indexes.
The possible keys column means I have these indexes and I may use them.
The keys index shows which one it's actually using (and in your case
they are all null which means none are chosen).
First rule:
any joins should be indexed, so index these columns (some may be primary
keys and they are already indexed):
`UserChecks`.UserID
`User`.EntryID
`User`.GroupID
`Group`.EntryID
`GroupTimes`.GroupID
`Group`.EntryID
`Group`.RealmID
`Realm`.EntryID
`Realm`.ClientID
`Client`.EntryID
Are you expecting lots of people to be active or inactive?
If you're expecting a lot of people to be inactive, you could index the
columns in this part:
- WHERE `User`.isActive='y' AND
-`Group`.isActive='y' AND
-`Realm`.isActive='y' AND
-`Client`.isActive='y' AND
but if a lot (more than 30%) are going to be active, then it's not worth
it (mysql will ignore the index because it returns too many results).
I'm not sure whether the rest of your query can be optimized because of
the function calls:
-`GroupTimes`.DOW=DAYOFWEEK(DATE_ADD('1970-01-01', INTERVAL
UNIX_TIMESTAMP() SECOND)) AND
-`GroupTimes`.StartTime
DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL UNIX_TIMESTAMP() SECOND),
'%H:%i:%S') AND
-`GroupTimes`.StopTime DATE_FORMAT(DATE_ADD('1970-01-01',
INTERVAL UNIX_TIMESTAMP() SECOND), '%H:%i:%S') AND
What language are you using? PHP?
You could work out part of this in php:
$dayofweek = date('w'); (see http://www.php.net/date)
$starttime = date('H:i:s');
etc.
I'm sure other languages have similar ways of doing this.
and that would give mysql a constant to work with (which should be
indexable) instead of having to work it out for each row it finds.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search Engine type search
Neil Tompkins wrote: I followed the instructions, but when doing a search I get no results returned. here is my table CREATE TABLE /*!32300 IF NOT EXISTS*/ MyTest ( id int(10) unsigned NOT NULL auto_increment, title varchar(200) , body text , PRIMARY KEY (id), INDEX title (title,body)); I created FULL INDEX, but for some reason it is not showing above ? Do a show create table tablename; does it show up then? A full text index will only be used once a table is populated (less than 3 or 4 rows it won't use it at all). See http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html and http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html and finally some words will not be included in a full text search automatically: http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dates in mysql
Hi all, Does anyone know how mysql stores dates? I'm wondering whether it converts it back to UTC before storing it (and back to the client timezone setting when you select) or whether it leaves it as is with the timezone information. So to change to a different timezone I'd have to convert it back to UTC and so on manually.. I've looked through the manual a few times but can't find an answer either way :( Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some questions on Storage engine
At 10:20 PM 8/22/2006, Chris wrote: You can't store them in memory. http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html Despite what the doc says, I posted a working script here a couple of weeks ago which creates temporary tables with engine=Memory. Either my specification was being ignored in favor of some default--with no error indication, or somebody forgot to document a new feature. This has only been tried by me on Win XP. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
