MYSQL DUMP FILES
Dear friends, I am using following command to dump mysql database files, mysql mysqldump b - c:/hdump/dump.sql where c:/hdump/dump.sql is the path where database files should be stored, however its not working. Any guidance, please.
Re: MYSQL DUMP FILES
On Saturday 14 August 2004 01:06 am, [EMAIL PROTECTED] wrote: mysql mysqldump b - c:/hdump/dump.sql Do it from the command line, not mysql command prompt. -- === Jabber: tradergt@(smelser.org|jabber.org) Quote: We don't make policy, we just clean up after it. -- Louis Mamakos === pgpof4lKY7bbt.pgp Description: PGP signature
Necessity of recreation of Index
Hi, Everybody. It's simple question. But i am in confusion. I have table with huge number of records but only 3 columns (suppose col1, col2, col3). col1 is primary key and I have created the Index on the column (i.e. col2) which is not a primary key. Problem 1) is that will it make any difference in performance while searching the record on col2 with above scenario. 2) other problem is that is it necessary to recreate the index on col2 whenever new records are added to table. is it true that mysql will recreate the index for that column by itself with a new records added or deleted. With Best Regards. From: Smitesh S. Damdoo
Re: GROUP BY optimization headscratcher
Matt ME CREATE TABLE `T1` ( ME `guid` smallint(5) unsigned NOT NULL default '0', ME `qid` smallint(5) unsigned NOT NULL default '0', ME `a` tinyint(2) NOT NULL default '-2', ME `d` tinyint(2) NOT NULL default '-2', ME KEY `IX_FW_qid` (`qid`), ME KEY `IX_FW_d` (`d`) ME ) TYPE=HEAP ME CREATE TABLE `T2` ( ME `guid` mediumint(8) unsigned NOT NULL default '0', ME `qid` tinyint(3) unsigned NOT NULL default '0', ME `a` tinyint(4) NOT NULL default '0', ME `d` decimal(1,0) unsigned NOT NULL default '0', ME PRIMARY KEY (`guid`,`qid`), ME KEY `IX_s23aw_d` (`d`), ME KEY `IX_s23aw_qid` (`qid`) ME ) TYPE=HEAP ME SELECT T1.guid, sum(T1.d + T2.d) as theSum ME FROM T1, T2 ME WHERE T1.qid=T2.qid ME GROUP BY T1.guid make key in T1: KEY `` (qid,guid ) and change table type to MyIsam for both table. Michael Monashev http://softsearch.ru/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL RECORDS AND ALPHABETICAL ORDER
Dear Friends, I have mysql table which retains names,Data is written to mysql table using php query, however when I check the records, I amn't able to do the same in alphabetical order. How do I make mysql to retain records in alphabetical order. Any guidance, please.
Re: MYSQL RECORDS AND ALPHABETICAL ORDER
Hi, I have mysql table which retains names,Data is written to mysql table using php query, however when I check the records, I amn't able to do the same in alphabetical order. How do I make mysql to retain records in alphabetical order. With an ORDER BY clause perhaps? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL RECORDS AND ALPHABETICAL ORDER
SELECT * FROM mytable ORDER BY alphabetical_field hth Peter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 14 August 2004 08:57 To: [EMAIL PROTECTED] Subject: MYSQL RECORDS AND ALPHABETICAL ORDER Dear Friends, I have mysql table which retains names,Data is written to mysql table using php query, however when I check the records, I amn't able to do the same in alphabetical order. How do I make mysql to retain records in alphabetical order. Any guidance, please. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL RECORDS AND ALPHABETICAL ORDER
A defining feature of a relational database is that its data storage is entirely independent of the physical order of rows. To specify row order in a query, use an ORDER BY clause. PB - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 14, 2004 2:56 AM Subject: MYSQL RECORDS AND ALPHABETICAL ORDER Dear Friends, I have mysql table which retains names,Data is written to mysql table using php query, however when I check the records, I amn't able to do the same in alphabetical order. How do I make mysql to retain records in alphabetical order. Any guidance, please.
SQL Query Question
Im not sure if this is possible or not. I have a Sales leads table. Part of the table has 2 employee_ids. 1. The Sales person the lead is assigned to. 2. The Marketing person that generated the lead. Then there is a employee table that has ids and names. When generating a report for leads I would like to lookup the name of the employee. I know I can do it with a seperate query, but I'm wondering if I can also do it in one query. Something like: SELECT employee.name as sales_name, employee.name as marketing_name, leads.id FROM leads, employee WHERE employee.id = leads.salesid AND employee.id = leads.marketingid Is there someway this can be done? Thanks for any assistance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael Michael J. Pawlowsky wrote: Im not sure if this is possible or not. I have a Sales leads table. Part of the table has 2 employee_ids. 1. The Sales person the lead is assigned to. 2. The Marketing person that generated the lead. Then there is a employee table that has ids and names. When generating a report for leads I would like to lookup the name of the employee. I know I can do it with a seperate query, but I'm wondering if I can also do it in one query. Something like: SELECT employee.name as sales_name, employee.name as marketing_name, leads.id FROM leads, employee WHERE employee.id = leads.salesid AND employee.id = leads.marketingid Is there someway this can be done? Thanks for any assistance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Right. If the employee ID in either the rep_no or entered_by columns does not have a corresponding row in the global_employee table, then the regular join won't match that row. In that case, as you found, you need a LEFT JOIN, which guarantees you get the rows from the table on the left, and auto-creates NULL fields for the table on the right when it has no matching row. For reference, this is mentioned in the manual http://dev.mysql.com/doc/mysql/en/JOIN.html. Michael Michael J. Pawlowsky wrote: Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL RECORDS AND ALPHABETICAL ORDER
Dear friends,
In this query what precisely needs to be added at alphabetical_field
following ORDER BY
SELECT * FROM mytable ORDER BY alphabetical_field
so that I get output in alphabetical order.
guidance, please.
--
My Php script
-
?php
include 'menufile.php';
?center
?php
// open the connection
$conn = mysql_connect(localhost, , );
// pick the database to use
mysql_select_db(b,$conn);
// create the SQL statement
$sql = SELECT * FROM euemails ORDER BY alphabetical_field ;
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data
while ($newArray = mysql_fetch_array($result)) {
// give a name to the fields
$id = $newArray['id'];
$email = $newArray['email'];
//echo the results onscreen between can also //type$id is and email is
echo $email br;
}
?/center
RE: MYSQL RECORDS AND ALPHABETICAL ORDER
Hi
replace alphabetical_field with the name of the field you are ordering by.
If you are not sure, post your table structure and I will add it for you :)
bfn
Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 14 August 2004 19:06
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: MYSQL RECORDS AND ALPHABETICAL ORDER
Dear friends,
In this query what precisely needs to be added at alphabetical_field
following ORDER BY
SELECT * FROM mytable ORDER BY alphabetical_field
so that I get output in alphabetical order.
guidance, please.
--
My Php script
-
?php
include 'menufile.php';
?center
?php
// open the connection
$conn = mysql_connect(localhost, , );
// pick the database to use
mysql_select_db(b,$conn);
// create the SQL statement
$sql = SELECT * FROM euemails ORDER BY alphabetical_field ;
// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//go through each row in the result set and display data
while ($newArray = mysql_fetch_array($result)) {
// give a name to the fields
$id = $newArray['id'];
$email = $newArray['email'];
//echo the results onscreen between can also //type$id is and email
is
echo $email br;
}
?/center
Re: MYSQL RECORDS AND ALPHABETICAL ORDER
Hi, Dear friends, In this query what precisely needs to be added at alphabetical_field following ORDER BY SELECT * FROM mytable ORDER BY alphabetical_field so that I get output in alphabetical order. guidance, please. I highly suggest reading a book (any book) on SQL (not MySQL) or take a good look here: http://www.w3schools.com/sql/default.asp With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GROUP BY optimization headscratcher
Michael, and . However, it didn't work. Whole thing still takes about 1 second. On the other hand, I realized I'm an idiot and that the reason it was running so fast when I handled temporary tables myself is that I was using mysqlcc, which truncated the first table to 1000 rows rather than 475,000, which--as one would imagine--sped things up considerably. However, I'm still looking for a way to make this fast. This is an integral part of my application, it'd be a big load off my mind ( my processesor) if I could get it under half a second on my box. I've made the changes Michael suggested, so I was wondering if anyone had suggestions on how to optimize this further. Below please find the query in question, a little background, the create statements and the output of explain: SELECT T2.guid, sum(T2.d+T1.d) AS theSum FROM T1, T2 WHERE T1.qid=T2.qid GROUP BY T2.guid; (I grouped by the wrong T last time, sorry). T1 contains one user, and their answers to various questions, so guid actually has only 1 value in this table, and qid has about 65, for a total of 65 rows. T2 contains about 15,000 users, so guid has 15,000 different values and qid has 34 possible values, and the total cardinality comes out to around 475,000. The Create Table statements look like: CREATE TABLE `T1` ( `guid` smallint(5) unsigned NOT NULL default '0', `qid` smallint(5) unsigned NOT NULL default '0', `a` tinyint(2) NOT NULL default '-2', `d` tinyint(2) NOT NULL default '-2', UNIQUE KEY `IX_T1_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM CREATE TABLE `T2` ( `guid` mediumint(8) unsigned NOT NULL default '0', `qid` tinyint(3) unsigned NOT NULL default '0', `a` tinyint(4) NOT NULL default '0', `d` decimal(1,0) unsigned NOT NULL default '0', UNIQUE KEY `IX_T2_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM And the explain is: +---+--+++-++--+-+ | table | type | possible_keys | key| key_len | ref| rows | Extra | +---+--+++-++--+-+ | T1| ALL | IX_T1_qid_guid | NULL |NULL | NULL | 65 | Using temporary; Using filesort | | T2| ref | IX_T2_qid_guid | IX_T2_qid_guid | 1 | T1.qid | 4979 | Using where | +---+--+++-++--+-+ Thanks so much! -Matt -Original Message- From: [mailto:[EMAIL PROTECTED] Sent: Saturday, August 14, 2004 3:46 AM To: Matt Eaton Subject: Re: GROUP BY optimization headscratcher Matt ME CREATE TABLE `T1` ( ME `guid` smallint(5) unsigned NOT NULL default '0', ME `qid` smallint(5) unsigned NOT NULL default '0', ME `a` tinyint(2) NOT NULL default '-2', ME `d` tinyint(2) NOT NULL default '-2', ME KEY `IX_FW_qid` (`qid`), ME KEY `IX_FW_d` (`d`) ME ) TYPE=HEAP ME CREATE TABLE `T2` ( ME `guid` mediumint(8) unsigned NOT NULL default '0', ME `qid` tinyint(3) unsigned NOT NULL default '0', ME `a` tinyint(4) NOT NULL default '0', ME `d` decimal(1,0) unsigned NOT NULL default '0', ME PRIMARY KEY (`guid`,`qid`), ME KEY `IX_s23aw_d` (`d`), ME KEY `IX_s23aw_qid` (`qid`) ME ) TYPE=HEAP ME SELECT T1.guid, sum(T1.d + T2.d) as theSum ME FROM T1, T2 ME WHERE T1.qid=T2.qid ME GROUP BY T1.guid make key in T1: KEY `` (qid,guid ) and change table type to MyIsam for both table. Michael Monashev http://softsearch.ru/ -- 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]
Kernel panic when mysql stop command issued
Kernel panic: Fatal exception in interrupt...In Interrupt handler - not syncing message appears when the mysql-max stop is issued. Other than that everything works. I changed hardware (everything new) and re-installed Linux and MySQL and upgraded to latest 2.6.3 from mdk (It was happening with the previous 2.6.3 also). Problem is repeatable 4 out of 5 tries. Linux 2.6.3-15mdkenterprise #1 SMP Fri Jul 2 20:07:05 mysql MySQL-Max-4.0.20-3mdk. Has any one heard or seen anything like it? __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Control Counters
The requirement is for a numeric counter which starts from a value, gets incremented by one to a max value and then resets to the start value. Currently, I keep these on a table of counters and do the lock, read, update, unlock. Is there a better way to implement this using mysql for keeping track of invoice numbers, customer numbers, receipt numbers etc? ___ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Language Searching
Hi, Does anyone how I could accomplish this: Basically, I have a MySQL database of names - some names have Spanish accents in them. I want to build a web interface in PHP to search this database. However, I want the names with Spanish accents to be shown in the search results, regardless if the search was spelled without the accents. For example: The database has Niño in it. I want it to be returned if the user searched by using nino or niño. Any ideas? Maybe somehow using regular expressions in PHP or in MySQL using Unicode somehow? It has to be fast and on the fly. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot be null problem
When sending form data via a form I get the following SQL error ERROR:-- SQLException - Message: The url cannot be null SQLState: 08001 ErrorCode :0 What does this mean? The row url, is not set to be not null. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Language Searching
KT The database has Niño in it. I want it to be returned if the user KT searched by using nino or niño. Normalize words before using mysql. Use regexp to convert 'n' - 'ñ'. 1.Create columns with normalized data. 2.Fill it. 3.Use columns with normalized data for search and use normalizing before start query. Sorry for my English. SoftSearch.ru Member of Independent Software Developers Forum (ISDEF) ICQ# 16629 http://softsearch.ru/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count of two fields
Wonder if you can help I have a table with a number of fields but the List Name Member Info Ever member in the table has a list and some members have something in the info field, Im after a count by list name So for instance the result im after would be something like List1 55 3 So for list1 there are 55 members associated with is and out of the 55 3 have something in the info field Help appreciated Regards John B [EMAIL PROTECTED]
Count even when empty
Further to my earlier query Im using this SELECT dbo.members_.List_, COUNT(dbo.members_.EmailAddr_) AS nojgid FROM dbo.members_ INNER JOIN dbo.lists_ ON dbo.members_.List_ = dbo.lists_.Name_ INNER JOIN dbo.topics_ ON dbo.lists_.Topic_ = dbo.topics_.Title_ WHERE (dbo.members_.jgid IS NULL) GROUP BY dbo.members_.List_ This works but I want to get a 0 when dbo.members_.jgid IS NULL so my output shows a 0 for a list, currently is does not show the list when its empty (obviously because I use is null) Pointers appreciated Regards John Berman [EMAIL PROTECTED]
