functions
Hi, I am developing applications using PHP and MySQL. There are various functions in MySQL that is also implemented in PHP. Such as date arithmetic, string, numeric, etc... Do I get better performance if I do them in MySQL (or PHP) and why? Doruk -- FISEK INSTITUTE - http://www.fisek.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up lookups on a table
By the way, I tried adding high_priority to the SELECT statement, which according to http://www.mysql.com/doc/en/Internal_locking.html gives it a higher priority, but that didn't fix the problem. This is a desperate, devastating problem that could simply destroy our business if we don't get it fixed soon -- any help is appreciated. -Bennett At 10:27 PM 7/26/2003 -0700, Bennett Haselton wrote: Jackson, Thanks for your help. Two follow-ups: a) Is there a command to show all the table locks, or locks on a particular table? I searched http://www.mysql.com/doc/en/index.html for locking and locks, but I couldn't find anything. b) I'm reading http://www.mysql.com/doc/en/Internal_locking.html and it sounds like if the table doesn't have any write locks, you can have as many concurrent reads on it as you want, right? I know that there were definitely not any write operations on that table going on during some of the times when the SELECT query would randomly run for 10-20 seconds instead of 1 second, so I'm not sure if that's causing it. There could be other read operations going on at that time, though. -Bennett At 11:24 PM 7/26/2003 -0500, Jackson Miller wrote: This could be caused by table locking. If another Mysql process ha a lock on the table then other processes start to queue up. Maybe this query is getting stuck behind other queries or a single slow query. Just a thought. -Jackson On Saturday 26 July 2003 11:00, Bennett Haselton wrote: I have a MySQL query running inside a CGI script on my site that, at random intervals, seems to take 10-20 seconds to complete instead of less than 1 second. I spent so much time trying to track this down that I wrote a script which runs once a minute on the site, which (a) captures the output of ps auwx (listing all processes) so I can see if that has anything to do with the slowdown; (b) times how long it takes to run the query, and; (c) times how long it takes to run a similar query on a much smaller table. (Part (c) is so that I can separate out whether it's the size of the table in part (b) that's making the difference, or the time taken to do something that's common to both queries, like getting a database handle.) 90% of the time, the large-table query takes less than 1 second, but 10% of the time, it takes 15-20 seconds. (The small-table query always takes less than 1 second.) I looked at the output of ps auwx to see if there seemed to be a relationship between the %CPU time used by other processes, or the number of other running processes, and the speed of the query, but there didn't seem to be. So, my questions are: (a) What is the usual cause of this type of problem? (b) The query I'm running is: SELECT * FROM news_feed_item WHERE news_feed_owner_userid = $my_id; on a table whose description is: ++--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+--- -+ | ID | int(10) unsigned | | PRI | NULL| auto_increment | | news_feed_owner_userid | int(10) unsigned | YES | MUL | NULL|| | URL| varchar(255) | YES | | NULL|| | title | varchar(255) | YES | | NULL|| | description| text | YES | | NULL|| | date_and_time | datetime | YES | | NULL|| | news_site_name | varchar(255) | YES | | NULL|| ++--+--+-+-+--- -+ (if it wraps, widen message window to see it all). Since I've already defined an index on news_feed_owner_userid, is there anything else I can do to make this kind of query run faster on this table? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: A little SQL query help
Hi Ivan, depending on mark's needs (orginal poster of the question), you might be right. So far I have not so much experience with PHP. Best regards Nils Valentin Tokyo/Japan 2003 7 27 15:43Ivan Cukic : Nils How about LIKE \$%searchdata%\ ? The % sign should be in front of $ LIKE \%$searchdata%\ Ivan __ One World, one Web, one Program -- Microsoft promotional ad Ein Volk, ein Reich, ein Fuhrer -- Adolf Hitler __ http://alas.matf.bg.ac.yu/~mr02014 ___ _ _ _ __ ___ _ / __/___ __ | __| _ _ ___ \ / _/ / . / _\/\ | _| \ \/ / ._\ Ivan Cukic, Form Eye 2003. \ /_/ /___/_/ /_/_/_/ |___|_\ /\___ web development and design / __ / _ _ __ ___ / -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little SQL query help
- Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Obantec Support [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 2:05 AM Subject: Re: A little SQL query help Hi Mark, 2003 7 27 06:09Obantec Support : Hi I have a database supplied to me that was excel but now uploaded to mysql. (the data is out of my hands.) i need to search for 1 of 2 categories against 3 fields with user inputted data. example Categories=Lessor , searchtype = BusinessCity searchdata = (user input) $sql = SELECT * FROM Contacts WHERE Categories=\$calltype\ and $searchtype LIKE \$searchdata%\; there are 2 Categories supplied via 2 different pages which load the same form and use call to determine Category. $searchtype is 1 of 3 text boxes against which searchdata is passed. I need to get all columns hence the * but need $searchdata to try and match all of text in the $searchtype column. using the LIKE \$searchdata%\ gets me only from start of test i.e. Fin will find Financial from Financial Text Widgets but not Financial from Widgets Text Finance How about LIKE \$%searchdata%\ ? I am not sure if you really need the $ there. If it is for php than it might be o.k there, but for MySQL I believe you don't need it in your case (if understood correctly what you want to do ;-) So please try also this : LIKE '%searchdata%' Best regards Nils Valentin Tokyo/Japan using php as my chosen language. Mark -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils Hi I was sure i tried the %string%. I need the $ since $searchdata is a php variable. \%$searchdata%\ works just fine. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with DELETE and a subquery
Try this ... Delete from clients where 0 = (select count(*) from branches where branches.cid = clients.cid) Cheers FattShin -Original Message- From: Vikram Vaswani [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 9:51 PM To: Nils Valentin; [EMAIL PROTECTED] Subject: Re: Help with DELETE and a subquery Hey, Thanks for the help. I dont think this is possible, because MySQL will not let you delete from the same table you are reading. Is there an alternative way to do this using a subquery, you think? ignore both previous posts. Both don't work as wanted. I just realized that and I will come back to you after I created the tables and made it sure. Sorry for the confusion. Best regards Nils Valentin Tokyo/Japan 2003 7 25 14:42Nils Valentin : Hi Vikram, just read the post once more. I made a mistake. You want to delete the clients with no branches you said, so the command should look like mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE ISNULL(clients.cid); Note that cid itself is ambigous, because in both tables. Best regards Nils Valentin 2003 7 25 14:31Nils Valentin : Hi Vikram, NULL is a special data type and requires special procedures. Try this: mysql delete from clients where cid = (select clients.cid from mysql clients left join branches using (cid) WHERE ISNULL(bid); Please make NO SPACE betwen ISNULL and (bid) as otherwise wit will give you an syntax error. Hope that ends the problems you had. Best regards Nils Valentin Tokyo/Japan 2003 7 25 12:58Vikram Vaswani : Hi all, I have the following two tables: mysql SELECT * FROM clients; +-+-+ | cid | cname | +-+-+ | 101 | JV Real Estate | | 102 | ABC Talent Agency | | 103 | DMW Trading | | 104 | Rabbit Foods Inc| | 110 | Sharp Eyes Detective Agency | +-+-+ 5 rows in set (0.00 sec) mysql SELECT * FROM branches; +--+-++--+ | bid | cid | bdesc | bloc | +--+-++--+ | 1011 | 101 | Corporate HQ | CA | | 1012 | 101 | Accounting Department | NY | | 1013 | 101 | Customer Grievances Department | KA | | 1041 | 104 | Branch Office (East) | MA | | 1042 | 104 | Branch Office (West) | CA | | 1101 | 110 | Head Office| CA | | 1031 | 103 | N Region HO| ME | | 1032 | 103 | NE Region HO | CT | | 1033 | 103 | NW Region HO | NY | +--+-++--+ 9 rows in set (0.01 sec) I need to delete all clients with no branches. I need to use a subquery to do this. Given these constraints, I came up with the following: mysql delete from clients where cid = (select clients.cid from mysql clients left join branches using (cid) WHERE bid is null); MySQL says: ERROR 1093: You can't specify target table 'clients' for update in FROM clause Does any one know why I am getting this error (MySQL 4.1)? Can you help me rewrite this operation *using a subquery only*? TIA, Vikram -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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: A little SQL query help
Hi Mark, Thanks for the reply. Looks like my next step should be to learn PhP ;-) Best regards Nils Valentin Tokyo/Japan 2003 7 27 16:35Obantec Support : - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Obantec Support [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 2:05 AM Subject: Re: A little SQL query help Hi Mark, 2003 7 27 06:09Obantec Support : Hi I have a database supplied to me that was excel but now uploaded to mysql. (the data is out of my hands.) i need to search for 1 of 2 categories against 3 fields with user inputted data. example Categories=Lessor , searchtype = BusinessCity searchdata = (user input) $sql = SELECT * FROM Contacts WHERE Categories=\$calltype\ and $searchtype LIKE \$searchdata%\; there are 2 Categories supplied via 2 different pages which load the same form and use call to determine Category. $searchtype is 1 of 3 text boxes against which searchdata is passed. I need to get all columns hence the * but need $searchdata to try and match all of text in the $searchtype column. using the LIKE \$searchdata%\ gets me only from start of test i.e. Fin will find Financial from Financial Text Widgets but not Financial from Widgets Text Finance How about LIKE \$%searchdata%\ ? I am not sure if you really need the $ there. If it is for php than it might be o.k there, but for MySQL I believe you don't need it in your case (if understood correctly what you want to do ;-) So please try also this : LIKE '%searchdata%' Best regards Nils Valentin Tokyo/Japan using php as my chosen language. Mark -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils Hi I was sure i tried the %string%. I need the $ since $searchdata is a php variable. \%$searchdata%\ works just fine. Mark -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mystry about mysqlcc
Andreas wrote: I got me a copy of mysqlcc 0.9.2-beta and installed it on my win 2000 box. No complaints so far. There is an icon on the desktop. I click it and for a really short moment there is a sandclock and then nothing happens. Mysqlcc just dosn't bother to appear. I went to the c:\programme\mysqlcc directory on the console and tried to start it on the command line to catch some error messages but still nothing happens. Then I copied the zip to my notebook which has more or less the same windows on it. There everything works fine. Any hints what there's wrong with my desktop-comp ? I had the same problem with mysqlcc working on a win2k desktop but not working on a win2k notebook, so probably the desktop isn't the problem. Using some trace utility, I found that mysqlcc.exe exited soon after reading C:\WINNT\my.ini on the bad computer (without the mysqlcc windows appearing). The good computer had no MySQL installed thus it had no C:\WINNT\my.ini file. Looking at the my.ini file for suspicious options, I found that changing the line socket = /tmp/mysql.sock to #socket = /tmp/mysql.sock (commmenting it out) makes mysqlcc work again. Renaming/removing my.ini works as well, but is probably not what you want. Does this help? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] about AUTO_INCREMENT...
Thanks, Carlos Write a Friend said: When using AUTO_INCREMENT, is there a way to set the starting value. Thanks, Carlos Alter Table TABLENAME AUTO_INCREMENT=1 where TABLENAME is your table. Of course this resets it so the next use of auto increment will generate a 1 for the table. I use is right after deleting the contents of the table and any related tables. (Its for a conversion program and I delete everything if the conversion fails). William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- 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: mystry about mysqlcc
Hans-Peter Grimm wrote: Looking at the my.ini file for suspicious options, I found that changing the line socket= /tmp/mysql.sock to #socket= /tmp/mysql.sock (commmenting it out) makes mysqlcc work again. Does this help? YES ! Perfectly ! :) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1040 error, too many connections?
I'm running a series of queries from a Python program using the MySQLdb module. The program opens a connection and then procedes to make two queries and then close the connection. It does this for each file provided as an argument to the program so there can be quite a few such cycles. I'm getting an 1040 error of 'Too many connections'. I can't figure out how this program would ever have more than one connection open at a time. Could anyone explain what I'm doing wrong? I've tried forcing the connection closed at the end of each cycle but that doesn't seem to help. Thanks. -- Peace, Love, Linux Michael [EMAIL PROTECTED] http://kavlon.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1040 error, too many connections?
What happens if you keep the original connection open and ALWAYS query with that connection -M - Original Message - From: Michael [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 6:41 AM Subject: 1040 error, too many connections? I'm running a series of queries from a Python program using the MySQLdb module. The program opens a connection and then procedes to make two queries and then close the connection. It does this for each file provided as an argument to the program so there can be quite a few such cycles. I'm getting an 1040 error of 'Too many connections'. I can't figure out how this program would ever have more than one connection open at a time. Could anyone explain what I'm doing wrong? I've tried forcing the connection closed at the end of each cycle but that doesn't seem to help. Thanks. -- Peace, Love, Linux Michael [EMAIL PROTECTED] http://kavlon.org -- 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: Selecting unique values
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 25 July 2003 17:38, Alec Smith wrote: But what I really need is a result like +--++ | name | domain | +--++ | hostdom1 | abc123.com | | hostdom2 | abc127.com | | hostdom3 | abc128.com | | hostdom4 | abc129.com | +--++ where only the t.name and d.domain pair with the highest domain_id for each type_id are given. Is there a way to do this without resulting to seperate SQL queries for each entry in the domain_types table? I'm using MySQL 4.0.14 on FreeBSD 5.1. Try: Select name, max(domain) From domain_types dt join domains d on dt.type_id = d.type_id group by name; -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/I9rMjeziQOokQnARAgCyAJ4ze6xcJXUqUqT3kUzoXNSLNq7hjACfTpgu g5Kk/tINNr0GIt5rIbaJX7k= =m37K -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with DELETE and a subquery
On 25-Jul-2003 Vikram Vaswani wrote: snip mysql SELECT * FROM branches; +--+-++--+ | bid | cid | bdesc | bloc | +--+-++--+ | 1011 | 101 | Corporate HQ | CA | | 1012 | 101 | Accounting Department | NY | | 1013 | 101 | Customer Grievances Department | KA | | 1041 | 104 | Branch Office (East) | MA | | 1042 | 104 | Branch Office (West) | CA | | 1101 | 110 | Head Office| CA | | 1031 | 103 | N Region HO| ME | | 1032 | 103 | NE Region HO | CT | | 1033 | 103 | NW Region HO | NY | +--+-++--+ 9 rows in set (0.01 sec) I need to delete all clients with no branches. I need to use a subquery to do this. Given these constraints, I came up with the following: mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE bid is null); snip Un-tested: DELETE FROM clients WHERE cid NOT IN (SELECT b1.cid FROM branches AS b1, branches AS b2 WHERE b1.cid=b2.cid and b1.bid != b2.bid); Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL multiple query in php script. (newbie)
On 25-Jul-2003 Barry Hayden wrote: snip $sql = SELECT Realtor, HouseAddress, Bathrooms, Bedrooms, YearBuilt, Garage, Location, Basement, Info FROM properties WHERE Bathrooms =='.$a.', Bedrooms =='.$b.', Garage =='.$c.', Basement =='.$d.'; Use '=' instead of '==' and why the commas in your WHERE clause ? $sql = SELECT Realtor, ... WHERE Bathrooms ='$a' AND Bedrooms ='$b' AND ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cant connect to mysql server through socket
Hi; I have installed suse 8.2 a short while ago and at my first attempt to connect mysql (via shell and a perl dbi script) i get following error: can't connect to mysql server through socjet '/var/lib/mysql/mysql.sock'. i know this looks like a common error and I am terribly sorry if this kind of subject has been posted before but I'll appreciate any urgent help. Thanks Devrim __ ComputerBild 15-03 bestaetigt: Den besten Spam-Schutz gibt es bei WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to find the closest result
On 25-Jul-2003 Brian Newsham wrote:
Hi,
I'm working on a PHP based website that loads custom pricing for users
where they logon.
random snipage ahead
majver and minver identify which price schedule to use. Each customer has
a different majver minver combination.
The price schedule where majver=0 and minver=0 is called list price, and
is the only schedule that has a price for every single item. All the
other combinations of majver and minver make up an sub-list of items that
sell at the same discount level. If there is no price for a majver,
minver combo, an attempt is made to use the price where minver=0,
otherwise list price is used.
To get the price of one item, I would do the following.
mysql SELECT * FROM item_price WHERE iid=3 AND majver IN (0,1) AND
minver IN (0,10) ORDER BY majver DESC, minver DESC;
+++-++
| majver | minver | iid | price |
+++-++
| 1 | 10 | 3 | 98.81 | = first choice
| 1 | 0 | 3 | 91.21 | = second choice
| 0 | 0 | 3 | 152.02 | = last choice
+++-++
3 rows in set (0.00 sec)
SELECT *, IF(majver=$major,IF(minver=$minor,2,1),0) AS sortfld
...
ORDER BY sortfld DESC, majver DESC, minver DESC;
Most of the time I want to get information about more than one item at
once. However this complicates things. I only want 1 row for each iid,
specifically the row with the highest majver and minver for a given iid.
EXAMPLES:
mysql SELECT * FROM item_price WHERE iid IN (3,4) AND majver IN (0,1)
AND minver IN (0,10) ORDER BY majver DESC, minver DESC;
+++-++
| majver | minver | iid | price |
+++-++
| 1 | 10 | 3 | 98.81 |
| 1 | 0 | 3 | 91.21 | = Need to eliminate these rows
| 0 | 0 | 3 | 152.02 | =
| 0 | 0 | 4 | 49.29 |
+++-++
4 rows in set (0.00 sec)
Order by iid and let PHP suppress the duplicates:
$sql= SELECT *, ...
ORDER BY iid, sortfld DESC, majver DESC, minver DESC;
$res= mysql_query($sql);
$previd= 0;
echo 'tablecaptionBest pricing/caption';
while ($row= mysql_fetch_array($res)) {
if ($previd != $row['iid']) {
echo 'trtd', implode('/tdtd', $row), '/td/tr';
$previd= $row['iid'];
}
}
echo '/table';
mysql SELECT * FROM item_price WHERE iid IN (3,5) AND majver IN (0,1)
AND minver IN (0,10) ORDER BY majver DESC, minver DESC;
+++-++
| majver | minver | iid | price |
+++-++
| 1 | 10 | 3 | 98.81 |
| 1 | 10 | 5 | 89.77 |
| 1 | 0 | 3 | 91.21 | = Need to eliminate these rows
| 1 | 0 | 5 | 82.87 | =
| 0 | 0 | 3 | 152.02 | =
| 0 | 0 | 5 | 138.11 | =
+++-++
6 rows in set (0.00 sec)
Is it possible to get a result set with 1 row for each iid specified, and
the price where the majver and minver are the largest combination for
each iid?
See above, --the problem solves itself.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to export db or table structure?
On 26-Jul-2003 Priscilla Walther wrote: snipage We have a very large MySQL database, and the designer of the database no longer works for us. I need to re-create the same table structure, but without the content, on a separate machine. Is there some kind of export command that will export that info to a file without the contents of the tables themselves? $ mysqldump -d dbname Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
no subject
Hi; I have installed suse 8.2 a short while ago and at my first attempt to connect mysql (via shell and a perl dbi script) i get following error: can't connect to mysql server through socjet '/var/lib/mysql/mysql.sock'. i know this looks like a common error and I am terribly sorry if this kind of subject has been posted before but I'll appreciate any urgent help. Thanks Devrim __ Spam-Filter fuer alle - bester Spam-Schutz laut ComputerBild 15-03 WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021120 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing Access databases
Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it.
Re: functions
In the last episode (Jul 27), Doruk Fisek said: I am developing applications using PHP and MySQL. There are various functions in MySQL that is also implemented in PHP. Such as date arithmetic, string, numeric, etc... Do I get better performance if I do them in MySQL (or PHP) and why? It probably depends on the function and what you do with it. Test it both ways, and use the one that's fastest/easiest. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
--- Robert Morgan [EMAIL PROTECTED] wrote: Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it. __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cant connect to mysql server through socket
What version of mysql are you using ? check the location of mysql.sock it probably is in /tmp/mysql/mysql.sock check the sock location in my.cnf --- D. K. [EMAIL PROTECTED] wrote: Hi; I have installed suse 8.2 a short while ago and at my first attempt to connect mysql (via shell and a perl dbi script) i get following error: can't connect to mysql server through socjet '/var/lib/mysql/mysql.sock'. i know this looks like a common error and I am terribly sorry if this kind of subject has been posted before but I'll appreciate any urgent help. Thanks Devrim __ ComputerBild 15-03 bestaetigt: Den besten Spam-Schutz gibt es bei WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: functions
Also keep in mind.. even if for example PHP was faster with certain functions.. Take that time + the time to pull the data from mysql and set it up for manipulation via PHP could be more than asking mysql to do all the work and just return a small resultset.. Just use a simple timer class to write 2 different scripts and time them.. On Sun, 27 Jul 2003, Doruk Fisek wrote: Hi, I am developing applications using PHP and MySQL. There are various functions in MySQL that is also implemented in PHP. Such as date arithmetic, string, numeric, etc... Do I get better performance if I do them in MySQL (or PHP) and why? Doruk -- FISEK INSTITUTE - http://www.fisek.org -- 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 : booting mysql on redhat
It is looking for the file libexec becoz the libexec contains the location of the mysqld process to start mysql. change the path of the mysqld -- if libexec doesnt exist chances are this file is located in /usr/yourmysqldirectorypath/bin/ __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
Why does the client doing the importing need to run on Linux? Don't you have access to a Windows machine anywhere on the network? It shouldn't matter where the target MySQL server is or what OS it's running on. - Original Message - From: Robert Morgan [EMAIL PROTECTED] To: mysqllist [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 1:14 PM Subject: importing Access databases Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
do long delete/update and view progress?
Is there any way to do a long update/delete on a table such that you can view the progress as the command runs, so that long before it's over, you have some idea of what the total running time will be? The way I did it was to write a perl script that takes the total range of values for the primary key field in the table, divides that range into chunks, and then runs the update/delete command on each chunk at a time, printing out when it's done that chunk. But that's a bit of a kludge. Is there a built-in way? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT problem
Hi, I'm new at complex SELECT statements, so any help would be appreciated. I need to create a summary table of counts from two tables in the database: year9 has a stack of variables including sex and favsub (favourite subject) year10 also has a stack of variables including sex and favsub I'd like to output a table with the following (column %'s if possible, otherwise counts): sex favsub malefemale 1 10% 5% 2 ... ... At the moment, I have this as my MYSQL query: SELECT favsub, sex, COUNT(favsub) FROM year9 GROUP BY favsub,sex ORDER BY favsub,sex * Problems: this is only for one of the tables, and also it's quite messy formatting it to a nice HTML layout as there are possibly two rows for each favourite subject, they aren't on the same row. It's also outputting the counts, not percentages so I need to do another query to figure out the total number of males/females. Again, any help appreciated. Kind regards Rachel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Depreciated the update log? No!!! Please don't do that.
http://www.mysql.com/doc/en/News-5.0.x.html The change log says it's no longer supported. Does that mean that it will be there, but there are no guarantees that it's accurate, or that it's being removed completely? The update log is very handy when doing development work - especially when I screw something up completely, and I have to restore from last night's backup and edit the update log to remove the mistake I made, then run the ammeded update log through the mysql client to apply today's updates. It's also just cool to watch when nothing else is happening. Anyone know why it's being depreciated? -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1040 error, too many connections?
Hi Michael, How many client connections does mysql SHOW PROCESSLIST show you ? Best regards Nils Valentin Tokyo/Japan 2003 7 27 22:41Michael : I'm running a series of queries from a Python program using the MySQLdb module. The program opens a connection and then procedes to make two queries and then close the connection. It does this for each file provided as an argument to the program so there can be quite a few such cycles. I'm getting an 1040 error of 'Too many connections'. I can't figure out how this program would ever have more than one connection open at a time. Could anyone explain what I'm doing wrong? I've tried forcing the connection closed at the end of each cycle but that doesn't seem to help. Thanks. -- Peace, Love, Linux Michael [EMAIL PROTECTED] http://kavlon.org -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexes to speed up a duplicates query.
OK, say you're trying to find all the non-exact duplicates in a table, and especially do it with a single query, so that the check can be part of a user interface. Then naturally you're going to have a slow query, because it has to compare every row with every other row. You ought to be able to cut out exactly half of the records in the second table, by just saying: SELECT * FROM TABLE LEFT JOIN users dups ON users.createddups.created But I can't get MySQL to use the index I have on 'created' (same results for integer-primary key, a string field). My best guess why not is that (according to the manual) MySQL will not use an index when ... the use of the index would require MySQL to access more than 30% of the rows in the table - is this the reason? Another option: say that I thought that my duplicates will always have the same letter in their surname, then I could make an index on the first letter of the surname, and make the join just on *that*. This should make the number of rows needed up to 26 times less, right? But exactly how is this done? MySQL won't use my index on the following query: SELECT * FROM TABLE LEFT JOIN users dups ON SUBSTRING(users.name,0,1)=SUBSTRING(dups.name,0,1) Thank you all in advance. Tom Cunningham. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL statement
HI,
I am using the latest MySql on a Windows 2000 platform and connecting to the
database using Perl's DBI. All works fine. My question is how best to
write a SQL SELECT statement that will extract the data in the format that I
want.
I have two tables one that contains all the customers information and the
second table contains the items that are ordered by the customer ( they both
have the order_numbre is each table). My select statement--shown
below--works fine for a customer that orders a single item, but fails when
more then one item is ordered. It will print the customer information
before each item ordered. What I want is to print just the customer
information once, and then print each item ordered by the customer after
that. Any help is appreciated.
TIA
Trevor
my $sth =$dbh-prepare(SELECT
miva_orders.order_number,DATE_FORMAT(miva_orders.date,'%c/%e/%Y'),miva_order
s.credit_card_type,miva_orders.shiptype,miva_orders.bill_name,miva_orders.bi
ll_company,miva_orders.bill_addr1,miva_orders.bill_addr2,miva_orders.bill_ci
ty,miva_orders.bill_state,miva_orders.bill_zip,miva_orders.ship_name,miva_or
ders.ship_addr1,miva_orders.ship_addr2,miva_orders.ship_city,miva_orders.shi
p_state,miva_orders.ship_zip,miva_orders.email,miva_orders.phone,miva_orders
.sales_tax,miva_orders.shipping_amount,miva_items_ordered.part_number,miva_i
tems_ordered.quantity FROM (miva_orders,miva_items_ordered) WHERE
miva_orders.order_number=miva_items_ordered.order_number);
$sth-execute();
my $i=0;
while (my @array = $sth-fetchrow_array()) {
for ( $i = 0; $i = $#array; $i++) {
print PARSED $array[$i] . ( ($i =$#array) ? \015 :
\t);
}
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexes to speed up a duplicates query.
Here's the full query I'm trying to do: explain select users.status, users.oid as oid, users.type as type, users.exclusive as exclusive, userse.o_initials as exclusive_initials, users.name_processed as name_processed, users.o_company as o_company, unix_timestamp(users.created) as created, count(distinct notes.oid) as notes_num, modifiers.o_initials as modified_by_initials, duplicateusers.oid as duplicate, duplicateusers.name_processed as duplicate_name, duplicateusers.o_company as duplicate_company, unix_timestamp(duplicateusers.created) as duplicate_created, duplicateusers.modified as duplicate_modified, count(distinct duplicatenotes.oid) as duplicate_num_notes fROM users left join users duplicateusers on (duplicateusers.created users.created) left join users userse on users.exclusive=userse.oid left join notes on notes.parent=users.oid left join users modifiers on users.modified_by=modifiers.oid left join notes duplicatenotes on duplicatenotes.parent=duplicateusers.oid where (users.status='O') and (((1=1 and users.type='USER'))) and (soundex(users.o_last_name)=soundex(duplicateusers.o_last_name) or (users.o_email_address=duplicateusers.o_email_address)) group by oid --- Plus a supplementary question: whether I have 'limit 1' or 'limit 100' doesn't seem to make any difference. I thought it must be my 'order by' clause, but I got rid of that no change. - this is Tom Cunningham. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL statement
Either do two queries or suppress the display of customer information within
your application code. I'd probably do two queries, especially if asking for a
lot of customer info or there were potentially a large number of orders for a
given customer. Doing a join in either case gives you a lot of extra data that
you don't need.
- Original Message -
From: Trevor Morrison [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, July 27, 2003 8:42 PM
Subject: SQL statement
HI,
I am using the latest MySql on a Windows 2000 platform and connecting to the
database using Perl's DBI. All works fine. My question is how best to
write a SQL SELECT statement that will extract the data in the format that I
want.
I have two tables one that contains all the customers information and the
second table contains the items that are ordered by the customer ( they both
have the order_numbre is each table). My select statement--shown
below--works fine for a customer that orders a single item, but fails when
more then one item is ordered. It will print the customer information
before each item ordered. What I want is to print just the customer
information once, and then print each item ordered by the customer after
that. Any help is appreciated.
TIA
Trevor
my $sth =$dbh-prepare(SELECT
miva_orders.order_number,DATE_FORMAT(miva_orders.date,'%c/%e/%Y'),miva_order
s.credit_card_type,miva_orders.shiptype,miva_orders.bill_name,miva_orders.bi
ll_company,miva_orders.bill_addr1,miva_orders.bill_addr2,miva_orders.bill_ci
ty,miva_orders.bill_state,miva_orders.bill_zip,miva_orders.ship_name,miva_or
ders.ship_addr1,miva_orders.ship_addr2,miva_orders.ship_city,miva_orders.shi
p_state,miva_orders.ship_zip,miva_orders.email,miva_orders.phone,miva_orders
.sales_tax,miva_orders.shipping_amount,miva_items_ordered.part_number,miva_i
tems_ordered.quantity FROM (miva_orders,miva_items_ordered) WHERE
miva_orders.order_number=miva_items_ordered.order_number);
$sth-execute();
my $i=0;
while (my @array = $sth-fetchrow_array()) {
for ( $i = 0; $i = $#array; $i++) {
print PARSED $array[$i] . ( ($i =$#array) ? \015 : \t);
}
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
