summing of my distance query
Mysql 4.0.18 ++---+--+-+--++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--++ | id | int(11) | | PRI | NULL | auto_increment | | zipcode| char(5) | | MUL | || | inc_level1 | int(11) | | | 0|| | inc_level2 | int(11) | | | 0|| | inc_level3 | int(11) | | | 0|| | inc_level4 | int(11) | | | 0|| | inc_level5 | int(11) | | | 0|| | inc_level6 | int(11) | | | 0|| | inc_level7 | int(11) | | | 0|| | latitude | double(12,6) | | | 0.00 || | longitude | double(12,6) | | | 0.00 || | created| timestamp(14) | YES | | NULL || ++---+--+-+--++ My query is this: SELECT b.zipcode, b.inc_level1, b.inc_level2, b.inc_level3, b.inc_level4, b.inc_level5, b.inc_level6, b.inc_level7, (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) AS distance FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 GROUP BY distance having distance = 10 Describe yields: +---+--+---+--+-+---+---+--- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+--- + | a | ref | position | position | 5 | const | 1 | Using where; Using index; Using temporary; Using filesort | | b | ALL | NULL | NULL |NULL | NULL | 38623 | | +---+--+---+--+-+---+---+--- + Its pretty quick, even 500 miles is under half a second, if there is anything I can do to add indexes and such, let me know, the main issue is, in a 500 mile search, I don't need back 1839 rows as in this case, I need just one, where each of the b.inc_level1-7 are added up, just adding in SUM(b.inc_level7) still yields me 1839 rows in this case. To do this at the application level is painfully slow, I can shove the data into a temp table at get it pretty easy, but I thought there would be a simple way. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoindexing
Under mysql 5.0.18 you can do something like: // get the current value of the auto_increment counter mysql select @@auto_increment_offset; +-+ | @@auto_increment_offset | +-+ |1105 | +-+ 1 row in set (0.00 sec) // set it to your required value mysql set @@auto_increment_offset = 201; Query OK, 0 rows affected (0.01 sec) // make sure it is set to what you want it to be mysql select @@auto_increment_offset; +-+ | @@auto_increment_offset | +-+ | 201 | +-+ 1 row in set (0.02 sec) mysql HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 23 Jun 2006, Tom Ray [Lists] wrote: To: mysql@lists.mysql.com From: Tom Ray [Lists] [EMAIL PROTECTED] Subject: Autoindexing Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- 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: Math problem
Hi Karl, Your question: can I add a $ when you select a view. I suggest to include $ sign in the field alias, like: Select title_id, ytd_sales * price AS `Turnover $` From titles; HTH, Cor - Original Message - From: Karl Larsen [EMAIL PROTECTED] To: Chris W [EMAIL PROTECTED] Cc: MYSQL General List mysql@lists.mysql.com Sent: Thursday, June 22, 2006 10:04 PM Subject: Re: Math problem Chris W wrote: Karl Larsen wrote: I'm trying to multiply numbers one of which is money. The money looks like this: SELECT price FROM titles; | price | ++ | $20.00 | | $19.99 | | $7.99 | | $19.99 | | $11.95 | | $19.99 | | $14.99 | | $11.95 | | $22.95 | | $2.99 | | $10.95 | | $7.00 | | $2.99 | | $20.95 | | NULL | | $19.99 | | $21.59 | | NULL | ++ 18 rows in set (0.01 sec) When I use SELECT title_id, ytd_sales * price From titles; I get: | title_id | ytd_sales | price * ytd_sales | +--+---+---+ | PC | 4095 | 0 | | BU1032 | 4095 | 0 | | PS | 3336 | 0 | | PS | 4072 | 0 | | BU | 3876 | 0 | | MC | 2032 | 0 | | TC | 4095 | 0 | | TC4203 | 15096 | 0 | | PC1035 | 8780 | 0 | | BU2075 | 18722 | 0 | | PS2091 | 2045 | 0 | | PS2106 | 111 | 0 | | MC3021 | 22246 | 0 | | TC3218 | 375 | 0 | | MC3026 | NULL | NULL | | BU7832 | 4095 | 0 | | PS1372 | 375 | 0 | | PC | NULL | NULL | +--+---+---+ 18 rows in set (0.04 sec) It appears that mysys 4.1 does not know how to multiply a dollar amount to another number. Has anyone else seen this problem? What does a show create table give for the price column? I bet it is varchar. The only way to make it work then would be to trim off the dollar sign and cast it to a float or double. It's a char(20) and NULL in the table titles. I removed the $ and reloaded and it now works properly. I suspect an ealier version of mysql had some way to do this. I'm learning that you store a simple number. But you can add a $ when you select a view. Karl -- 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]
pls click this link
pls click this link www.venadsolutions.com and find new things -- Thanks Regards, veerabhadra rao narra, +91-988-556-5556
Re: Autoindexing
If you are using autoincrement filed you could try this: ALTER TABLE `my_database`.`my_table` AUTO_INCREMENT = 201; ...or else if it is some stored proc you should find and edit table where it sotres index/counter data.. Tom Ray [Lists] wrote: Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dont click this link
On Friday 23 June 2006 09:09, Veerabhadra rao Narra wrote: pls click this link www.venadsolutions.com and find new things This is what I consider as SPAM... So do not click the link... -- 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: Autoindexing
Hi Remo, your method works fine on version 4.1 and the one shown for version 5 does not work here. Nice to know there is a SQL word AUTO_INCREMENT to do the job. Karl Remo Tex wrote: If you are using autoincrement filed you could try this: ALTER TABLE `my_database`.`my_table` AUTO_INCREMENT = 201; ...or else if it is some stored proc you should find and edit table where it sotres index/counter data.. Tom Ray [Lists] wrote: Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Math problem
Well that works fine, but I want to put a $ sign in front of every dollar amount. I will do that but I'm not there yet. Karl C.R.Vegelin wrote: Hi Karl, Your question: can I add a $ when you select a view. I suggest to include $ sign in the field alias, like: Select title_id, ytd_sales * price AS `Turnover $` From titles; HTH, Cor - Original Message - From: Karl Larsen [EMAIL PROTECTED] To: Chris W [EMAIL PROTECTED] Cc: MYSQL General List mysql@lists.mysql.com Sent: Thursday, June 22, 2006 10:04 PM Subject: Re: Math problem Chris W wrote: Karl Larsen wrote: I'm trying to multiply numbers one of which is money. The money looks like this: SELECT price FROM titles; | price | ++ | $20.00 | | $19.99 | | $7.99 | | $19.99 | | $11.95 | | $19.99 | | $14.99 | | $11.95 | | $22.95 | | $2.99 | | $10.95 | | $7.00 | | $2.99 | | $20.95 | | NULL | | $19.99 | | $21.59 | | NULL | ++ 18 rows in set (0.01 sec) When I use SELECT title_id, ytd_sales * price From titles; I get: | title_id | ytd_sales | price * ytd_sales | +--+---+---+ | PC | 4095 | 0 | | BU1032 | 4095 | 0 | | PS | 3336 | 0 | | PS | 4072 | 0 | | BU | 3876 | 0 | | MC | 2032 | 0 | | TC | 4095 | 0 | | TC4203 | 15096 | 0 | | PC1035 | 8780 | 0 | | BU2075 | 18722 | 0 | | PS2091 | 2045 | 0 | | PS2106 | 111 | 0 | | MC3021 | 22246 | 0 | | TC3218 | 375 | 0 | | MC3026 | NULL | NULL | | BU7832 | 4095 | 0 | | PS1372 | 375 | 0 | | PC | NULL | NULL | +--+---+---+ 18 rows in set (0.04 sec) It appears that mysys 4.1 does not know how to multiply a dollar amount to another number. Has anyone else seen this problem? What does a show create table give for the price column? I bet it is varchar. The only way to make it work then would be to trim off the dollar sign and cast it to a float or double. It's a char(20) and NULL in the table titles. I removed the $ and reloaded and it now works properly. I suspect an ealier version of mysql had some way to do this. I'm learning that you store a simple number. But you can add a $ when you select a view. Karl -- 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: summing of my distance query
Scott, can you expound on what 1 row would be returned, ideally? The one with the shortest distance? Or a row with the sums of inc_level1 ... inc_level7 ? Looks to me like you're trying to locate all the ZIP codes within a given radius of (in this case) ZIP 94949 with the query below. Yes/no? Dan Scott Haneda wrote: Mysql 4.0.18 ++---+--+-+--++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--++ | id | int(11) | | PRI | NULL | auto_increment | | zipcode| char(5) | | MUL | || | inc_level1 | int(11) | | | 0|| | inc_level2 | int(11) | | | 0|| | inc_level3 | int(11) | | | 0|| | inc_level4 | int(11) | | | 0|| | inc_level5 | int(11) | | | 0|| | inc_level6 | int(11) | | | 0|| | inc_level7 | int(11) | | | 0|| | latitude | double(12,6) | | | 0.00 || | longitude | double(12,6) | | | 0.00 || | created| timestamp(14) | YES | | NULL || ++---+--+-+--++ My query is this: SELECT b.zipcode, b.inc_level1, b.inc_level2, b.inc_level3, b.inc_level4, b.inc_level5, b.inc_level6, b.inc_level7, (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) AS distance FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 GROUP BY distance having distance = 10 Describe yields: +---+--+---+--+-+---+---+--- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+--- + | a | ref | position | position | 5 | const | 1 | Using where; Using index; Using temporary; Using filesort | | b | ALL | NULL | NULL |NULL | NULL | 38623 | | +---+--+---+--+-+---+---+--- + Its pretty quick, even 500 miles is under half a second, if there is anything I can do to add indexes and such, let me know, the main issue is, in a 500 mile search, I don't need back 1839 rows as in this case, I need just one, where each of the b.inc_level1-7 are added up, just adding in SUM(b.inc_level7) still yields me 1839 rows in this case. To do this at the application level is painfully slow, I can shove the data into a temp table at get it pretty easy, but I thought there would be a simple way. -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedded MySQL
Hi All, Can some kind soul tell me from where I can download the Embedded Library version of MySQL? -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP mysql_connect
I got a strange problem. I run a test-webserver and a MySQL server on the same machine. The code to connect to the database is: $db_link = mysql_connect (sql2.dahl-stamnes.net,stmbk,); This gives me the error: Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in /home/dahls/Sykkel/Karusellen/Homepage/functions.inc on line 46 But if I, on the same machine, use the mysql command, it works: mysql -h sql2.dahl-stamnes.net -u webuser -p mysql What makes this even more strange, is that I have other pages on the same server, which connect to a different sql-server (a different machine running the same version of MySQL) without problems. What can cause this problems? -- 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: PHP mysql_connect
I assume you are using php. It has to do with how the password in mysql is encrypted. On some accounts, the ones that work, it's encrypted in the old way that php can use. The default new, php 4 can't use. Here's the part of the manual that explains it and how to fix it: http://dev.mysql.com/doc/refman/4.1/en/old-client.html - Original Message - From: Jørn Dahl-Stamnes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 23, 2006 9:21 AM Subject: PHP mysql_connect I got a strange problem. I run a test-webserver and a MySQL server on the same machine. The code to connect to the database is: $db_link = mysql_connect (sql2.dahl-stamnes.net,stmbk,); This gives me the error: Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in /home/dahls/Sykkel/Karusellen/Homepage/functions.inc on line 46 But if I, on the same machine, use the mysql command, it works: mysql -h sql2.dahl-stamnes.net -u webuser -p mysql What makes this even more strange, is that I have other pages on the same server, which connect to a different sql-server (a different machine running the same version of MySQL) without problems. What can cause this problems? -- 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: PHP mysql_connect
On Friday 23 June 2006 15:30, Brent Baisley wrote: I assume you are using php. It has to do with how the password in mysql is encrypted. On some accounts, the ones that work, it's encrypted in the old way that php can use. The default new, php 4 can't use. Here's the part of the manual that explains it and how to fix it: http://dev.mysql.com/doc/refman/4.1/en/old-client.html Yes, I forgot to say that I was using PHP. However, using mysql does not work eigher. Thanks for the tip. I guess I just drop the password... -- 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]
Upgrading and table engine change advise
Hello all, Just looking for some advice from any of you that have done what I'm about to do. I'm being forced by management to make a whole lot of changes to our current MySQL db at one time. Something I'm personnaly not thrilled with. Current config: Redhat 9 MySQL ver 4.0.16 DB Engine MyISAM for all tables. 48G total space 1G ram New config: RH ES3 MySQL ver 5.x (latest) Mix of MyISAM and InnoDB ~140gig total space 1G ram Current my.cnf # The MySQL server [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin log-slave-updates server-id=1 port = 3306 skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 set-variable= max_connections=500 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout I know I'll need to use a different my.cnf to set variables for using InnoDB. I'd prefer not to do all this at one time, too many changes, but I'm not being given a choice. I've never used InnoDB before. We're switching to it to eliminate long table locks caused by reads from large tables. Any advise about pitfalls/potential problems I need to be aware of? Thanks in advance. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld refuses to run on boot
Hi Fredrik, all! Fredrik Andersson wrote: Hi all I have problems getting MySQL autoboot on my RedHat installation. [[...]] In addition to permissions (see the other posts), there is another possible problem: Depending on how your environment is set up, the MySQL server may need some other services (NIS and related) to run which were originally not listed in the server start file. Please see bug#18810 for details. Try this patch to /etc/init.d/mysql: --- /etc/init.d/mysql-OLD +++ /etc/init.d/mysql @@ -17,6 +17,7 @@ ### BEGIN INIT INFO # Provides: mysql # Required-Start: $local_fs $network $remote_fs +# Should-Start: ypbind nscd ldap ntpd xntpd # Required-Stop: $local_fs $network $remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 This fix will appear in 5.0.23. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to the group
At 02:43 PM 6/22/2006, Bartis, Robert M (Bob) wrote: If you will excuse my ignorance. I have no immediate need for this, but have often asked what the pros/cons there are writing a WEB based interface in PHP vs. say Perl. Do you have any insight into that? Thanks Bob Bob, Ok, so you don't want info on databases, but on which language to use to build a web site? You must sit down and determine what the site will be used for and what features you *must have* in your website. Find a website out there that has the features and style you're looking for. For example, do you want to build something like a Yahoo (page mode), or a store front like an Amazon (web application)? Once you know what type of web site you want to build, you can better determine what tools to use. For web applications (Amazon) take a look at Ajax applications. For plain page driven web sites look at PHP. Perl is more of a do everything type of language that can be used for writing applications as well as web pages, whereas PHP is more stripped down for speed and is intended for building web pages. You will get faster performance from PHP and is quite popular for building web sites. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to the group
At 02:43 PM 6/22/2006, Bartis, Robert M (Bob) wrote: If you will excuse my ignorance. I have no immediate need for this, but have often asked what the pros/cons there are writing a WEB based interface in PHP vs. say Perl. Do you have any insight into that? Thanks Bob Something else I should have mentioned, there are products out there like CodeCharge from YesSoftware.com that will generate the PHP/ASP/JSP code for you. It uses templates and will interface with MySQL and several other databases. It develops great looking applications but tends to use a lot more code that what you'd use if you wrote it manually. But it will get you up and running quite fast. They have a 30 day eval that you can try. There are also Ajax type development systems like Morfix (www.morfik.com) and Ruby On Rails http://www.rubyonrails.org/ and Lazslo on Rails http://wiki.openlaszlo.org/Laszlo_on_Rails that offers cutting edge development tools (that latter two are open source). These tools will deliver rich internet applications. Try some of the demos and see if you like it. :) Mike -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, June 22, 2006 3:39 PM To: mysql@lists.mysql.com Subject: Re: New to the group At 08:46 AM 6/22/2006, Nicholas Vettese wrote: Hello, My name is Nick, and I am a new MySQL user. My hope is not to become a PITA, so I will make sure that any question is straight and to the point with the information needed to answer the question. My skill in MySQL is pretty low, and I am looking to build a website for myself that will take information and save it to a database. At this time, I have a login, registration, change/lost password functionality working from a book that I read, but I am looking to expand my knowledge into more robust site. I am not looking to become the master programmer, just someone with enough knowledge and skill to accomplish his goals. Thanks, Nick Welcome Nick, You've come to the right place. There are a couple of books on MySQL that are quite good and I'd like to recommend. MySQL 3rd Edition by Paul Dubois and MySQL Cookbook by Paul Dubois (I think these guys are relatedvbg) If you are using PHP to build your website I found PHP and MySQL for Dynamic Web Sites : Visual QuickPro Guide (2nd Edition) (Visual Quickpro Guide) to be quite good and gets you going quite fast. There's not a lot of reading to do and they have you writing PHP code the first day. If you want a more thorough book on PHP MySQL there is: PHP and MySQL Web Development (3rd Edition) (Developer's Library) (Paperback) by Luke Welling, Laura Thomson There are also PHP/Mysql tutorials on the web but I don't know how good they are. You'll get up to speed faster by getting some of these books. Of course if you're not using PHP, then someone else can jump in with some reading suggestions. Mike -- 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: PHP mysql_connect
At 15:47 +0200 23/6/06, Jørn Dahl-Stamnes wrote: Yes, I forgot to say that I was using PHP... Oh, I think the clue was in the subject line. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Revolution: an abrupt change in the form of misgovernment. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect
Am Freitag, 23. Juni 2006 16:27 schrieb Chris Sansom: At 15:47 +0200 23/6/06, Jørn Dahl-Stamnes wrote: Yes, I forgot to say that I was using PHP... Oh, I think the clue was in the subject line. :-) ever tried localhost as hostname? -- Jochen Kaechelin, fvgi242ss, wlanhacking.de http://mail.wlanhacking.de/cgi-bin/mailman/listinfo - Frauen sind die einzigsten Opfer die auf ihren Jäger lauern! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedded MySQL
On Friday 23 June 2006 06:18 am, Asif Lodhi wrote: Hi All, Can some kind soul tell me from where I can download the Embedded Library version of MySQL? I don't see a binary version avaliable, but I know the following configure option exists: --with-embedded-server Build the embedded server (libmysqld). -- I'm assuming this is what you want -- TIA, Asif -- Chris White PHP Programmer/DB Jaguar Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect
On 6/23/06, Jørn Dahl-Stamnes [EMAIL PROTECTED] wrote: On Friday 23 June 2006 15:30, Brent Baisley wrote: I assume you are using php. It has to do with how the password in mysql is encrypted. On some accounts, the ones that work, it's encrypted in the old way that php can use. The default new, php 4 can't use. Here's the part of the manual that explains it and how to fix it: http://dev.mysql.com/doc/refman/4.1/en/old-client.html Yes, I forgot to say that I was using PHP. However, using mysql does not work eigher. Thanks for the tip. I guess I just drop the password... You're probably using an old MySQL library, locate it and get the newer version. If you don't want or can't do that, start your server with --old-passwords option and you'll be fine (till you go to production state). -- 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] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedded MySQL
Hi, I haven't tried this one, but I think this might help http://mysql-je.sourceforge.net Asif Lodhi [EMAIL PROTECTED] wrote: Hi All, Can some kind soul tell me from where I can download the Embedded Library version of MySQL? -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Embedded MySQL
I am sorry, this in case if your programming in java. Melvin Zamora [EMAIL PROTECTED] wrote: Hi, I haven't tried this one, but I think this might help http://mysql-je.sourceforge.net Asif Lodhi wrote: Hi All, Can some kind soul tell me from where I can download the Embedded Library version of MySQL? -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta.
Re: Autoindexing
Well I believe I'll need to update mysql since I just realized this server is using 3.23. Gotta love taking something over from someone and finding out they weren't very good at the job to begin with. Karl Larsen wrote: Hi Remo, your method works fine on version 4.1 and the one shown for version 5 does not work here. Nice to know there is a SQL word AUTO_INCREMENT to do the job. Karl Remo Tex wrote: If you are using autoincrement filed you could try this: ALTER TABLE `my_database`.`my_table` AUTO_INCREMENT = 201; ...or else if it is some stored proc you should find and edit table where it sotres index/counter data.. Tom Ray [Lists] wrote: Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to the group
[This is really OT for a MySQL list - sorry folks.] Forgive me if I'm telling you what you already know, but IMO the most importing thing to do when getting into web development is to learn how to build web sites securely. This might be a good starting point: http://www.owasp.org/index.php/OWASP_Top_Ten_Project Also I would recommend finding reading an overview of how web pages are requested via HTTP, so you have a clear idea of server-side versus client-side and understand the relationship between server-side code, HTML output and what is actually displayed in the browser window. Again I apologise if this obvious to you, but often people have difficulty programming and especially debugging web apps because they are not sure about this. There are probably at least a dozen fairly popular programming / scripting languages for building web sites, and dozens of others besides. Please don't ask which is best, otherwise this will probably end up being The Longest, Most Bitter Thread In History! Most people do not have extensive experience with multiple langauges and there's a lot of personal preference involved. If someone says that language X is best then what they really mean is that, of the languages they have used, they think X best suits the needs of their work and their personal preference. I'm afraid I don't really agree with the advice below about PHP's speed, because speed is affected by many variables other than just language choice (hardware, configuration, coding, database calls etc). Would a PHP solution be faster than a mod_perl solution? I don't know, but I doubt any difference would be worth worrying about. IMO the first consideration should be about what makes you most productive as a developer. (BTW, Ajax is a development technique, not a language. You still need a middleware language to do the server-side programming. To start trying to build sites using Ajax would be jumping in at the deep end, I think.) Personally I use something called Lasso (one of the 'dozens of others besides'). I've played with PHP a bit and with Perl a bit more, but have no intention of switching. Perl is cool and powerful, but I think if I was new to web development I would find PHP easier to learn. I think I would find Lasso easier still - I find its 'natural language' function names easier to remember. YMMV. Lasso is a commercial product, but with the latest version there is a free developer version. The biggest disadvantage IMO is that Lasso hosting is not that easy to find. http://www.omnipilot.com/index.html?section=Products%2fLasso%2fFree%20Trial Hmm - didn't intend to write such an essay on this! Hope it is of some use. James Harvard Ok, so you don't want info on databases, but on which language to use to build a web site? You must sit down and determine what the site will be used for and what features you *must have* in your website. Find a website out there that has the features and style you're looking for. For example, do you want to build something like a Yahoo (page mode), or a store front like an Amazon (web application)? Once you know what type of web site you want to build, you can better determine what tools to use. For web applications (Amazon) take a look at Ajax applications. For plain page driven web sites look at PHP. Perl is more of a do everything type of language that can be used for writing applications as well as web pages, whereas PHP is more stripped down for speed and is intended for building web pages. You will get faster performance from PHP and is quite popular for building web sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what it is about the file that's stopping it from being loaded. -- justferindo
Left Join Help
Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Paul Nowosielski Webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
Ferindo Middleton wrote: I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what it is about the file that's stopping it from being loaded. Since you did not show us the data, nor the command you used to load the data, we can't tell you what is wrong. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join Help
Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage These 5 tables are not joined on anything. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join Help
Here is your query rephrased a bit. I find this query structure easier to debug, especially when their are lots of joins. This is also the preferred structure in mysql 5 as I recall. Notice the ON ? part of the join. You didn't specify anything join condition so your doing a full join, very very bad. Fill in the question marks and your query should run fairly quick. SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td JOIN tbl_ARTST as art ON ? JOIN artist_tourdate artd ON ? JOIN tbl_VENUES tv ON ? JOIN tbl_VENUE_CAPACITY tvc ON ? JOIN tbl_VENUE_AGE_XREF tvax ON ? JOIN tbl_VENUE_AGES tvage ON ? LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 - Original Message - From: Paul Nowosielski [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 23, 2006 3:27 PM Subject: Left Join Help Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Paul Nowosielski Webmaster -- 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: Left Join Help
I ammend my previous post. Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage Use INNER_JOIN and use ON clauses. tvc, tvax, and tvage are not joined at all, producing Cartesian Products. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) This on condition does not include the table being joined. WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Gerald L. Clark Supplier Systems Corporation -- 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: Left Join Help
Paul, SELECT ... FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc , tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 FROM ... tbl_VENUE_CAPACITY tvc, ... LEFT JOIN tbl_VENUE_CAPACITY ON ... cross-joins four previous tables with tbl_venu-capacity, then left joins seven tables including tbl_VENUE_CAPACITY with tbl_VENUE_CAPACITY! FROM tbl_VENUE_AGE_XREF tvax, ... LEFT JOIN tbl_VENUE_AGE_XREF ON ... cross-joins five previous tables with tbl_VENUE_AGE_XREF, then left joins seven tables including tbl_VENUE_AGE_XREF with tbl_VENUE_AGE_XREF! FROM tbl_VENUE_AGES tvage ... LEFT JOIN ... tbl_VENUE_AGES ... cross-joins six previous tables with tbl_VENUE_AGES, then left joins seven tables including tbl_VENUE_AGES with tbl_VENUE_AGES! The double joins and cross joins will drive the server crazy. It's incoherent---the query makes no use of the double/cross/self-joins. Strong suggestion: lose the comma joins entirely, lose the duplicate joins, and write the join logic as explicit joins, for example SELECT ... FROM tourdates td INNER JOIN artist_tourdate AS artd USING (td_id) INNER JOIN tbl_artst AS art ON artd.artist_id = art.pkey INNER JOIN tbl_venues AS tv ON td.venue_id=tv.ID LEFT JOIN tbl_venue_capacity AS tvc ON tv.ID=tvc.venue_id LEFT JOIN tbl_venue_age_xref AS tvax ON tv.ID=tvax.Venue_id LEFT JOIN tbl_venue_ages AS tvage ON tvax.VENUE_ID = tvage.pkey WHERE td_date NOW() LIMIT 500 PB - Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
I guess my general reason for posting this was to ask: Are there any known issues with the LOAD DATA INFILE comand in MySQL? However, I stripped all the data in the file (test.tab) down to one record which still wouldn't load. Here is the command: mysql LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie; The fields inside the file are tab-delimited and look like this: 16411 5 Rupert Settles Settles Settles 1 esunindyo \N 207 \N 12 \N 2005-01-03 2005-01-07 1 1 1 0 \N Deobligation 1 2 \N fmiddleton 0 0 \N \N 11:00:00 \N \N \N 0 0 4 0 0 \N \N \N 1 The structure of the TABLE reggie is this: CREATE TABLE `reggie ` ( `id` bigint(20) unsigned NOT NULL auto_increment, `title_salutation_id` bigint(20) unsigned NOT NULL, `firstname` varchar(128) NOT NULL, `middlename` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `suffix` varchar(128) NOT NULL default '', `paper_received` tinyint(1) NOT NULL default '1', `addr` text, `cc_email_list` text, `fortran_id` bigint(20) default NULL, `office` text, `class_id` bigint(20) unsigned NOT NULL, `schedule_id` bigint(20) default NULL, `start_date` date NOT NULL default '-00-00', `end_date` date NOT NULL default '-00-00', `enrolled` tinyint(1) default NULL, `attended` tinyint(1) default NULL, `completed` tinyint(1) default NULL, `cancelled` tinyint(1) default '0', `cancelled_comments` text, `comments` text, `email_confirmation_sent` tinyint(1) NOT NULL, `employment_status_id` bigint(20) unsigned NOT NULL default '0', `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_user_updated` text, `waitlisted` tinyint(1) default '0', `overflow_registrant` tinyint(1) default '0', `attach_hotel_listing_directions` tinyint(1) default NULL, `instructor_legacy` text, `time_legacy` time default NULL, `ssn_legacy` text, `position_grade_title` text, `office_phone_legacy` text, `contractor_legacy` tinyint(1) default NULL, `no_show` tinyint(1) default NULL, `funding_id` bigint(20) unsigned NOT NULL default '0', `incomplete` tinyint(1) default NULL, `prerequisites_completed` tinyint(1) default NULL, `score` smallint(5) unsigned default NULL, `per_diem_cost` decimal(10,0) default NULL, `travel_cost` decimal(10,0) default NULL, `first_migration` tinyint(1) unsigned default NULL, PRIMARY KEY (`firstname`,`middlename`,`lastname`,`suffix`,`class_id`,`start_date`,`end_date`), UNIQUE KEY `id` (`id`), KEY `fk_registration_class_id_must_always_match_a_classes_id` (`class_id`), KEY `fk_registration_title_id_must_always_match_title_salutations_id` (`title_salutation_id`), KEY `fk_registration_bureau_id_must_always_match_a_bureaus_id` (`bureau_id`), KEY `fk_funding_id_check_for_registration_and_attendance` (`funding_id`), KEY `fk_employment_status_id_check_for_registration_and_attendance` (`employment_status_id`), CONSTRAINT `fk_employment_status_id_check_for_registration_and_attendance` FOREIGN KEY (`employment_status_id`) REFERENCES `employment_statuses` (`id`), CONSTRAINT `fk_funding_id_check_for_registration_and_attendance` FOREIGN KEY (`funding_id`) REFERENCES `funding_types` (`id`), CONSTRAINT `fk_registration_bureau_id_must_always_match_a_bureaus_id` FOREIGN KEY (`bureau_id`) REFERENCES `bureaus` (`id`), CONSTRAINT `fk_registration_class_id_must_always_match_a_classes_id` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`), CONSTRAINT `fk_registration_title_id_must_always_match_title_salutations_id` FOREIGN KEY (`title_salutation_id`) REFERENCES `title_salutations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Do you know why this this one data record won't load?... why MySQL says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Gerald L. Clark [EMAIL PROTECTED] wrote: Ferindo Middleton wrote: I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what it is about the file that's stopping it from being loaded. Since you did not show us the data, nor the command you used to load the data, we can't tell you what is wrong. -- Gerald L. Clark Supplier Systems Corporation -- Ferindo Middleton Technical Lead - Research and AUI Infrastructure Development Sleekcollar Software
Re: Left Join Help SOLVED
Thank you all so much for your help, here is my solution: (I'm sure I can do a little more optimization) SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td JOIN tbl_ARTST as art ON (art.PKEY = artd.artist_id) JOIN artist_tourdate artd ON (artd.artist_id = art.PKEY) JOIN tbl_VENUES tv ON (td.venue_id = tv.ID) LEFT JOIN tbl_VENUE_CAPACITY tvc ON (tvc.VENUE_ID = tv.ID) LEFT JOIN tbl_VENUE_AGE_XREF tvax ON (tvax.VENUE_ID = tv.ID) LEFT JOIN tbl_VENUE_AGES tvage ON (tvage.PKEY = tvax.VENUE_ID) LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) 45929 rows in set (3 min 11.75 sec) Best Regards, -- Paul Nowosielski Webmaster On Friday 23 June 2006 14:10, Gerald L. Clark wrote: I ammend my previous post. Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage Use INNER_JOIN and use ON clauses. tvc, tvax, and tvage are not joined at all, producing Cartesian Products. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) This on condition does not include the table being joined. WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+ -++---+--+ | table | type | possible_keys | key | key_len | | ref| rows | Extra| +++---+-+ -++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | | NULL | 4 | Using index; Distinct| +++---+-+ -++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system
Re: summing of my distance query
Scott, can you expound on what 1 row would be returned, ideally? The one with the shortest distance? Or a row with the sums of inc_level1 ... inc_level7 ? Looks to me like you're trying to locate all the ZIP codes within a given radius of (in this case) ZIP 94949 with the query below. Yes/no? Dan This is some older code, that did just that, find all zip codes in a certain radius. I need to modify it somewhat. You are correct, I want the sum of Let me show you... SELECT b.zipcode, b.inc_level1, b.inc_level2, (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) AS distance FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 GROUP BY distance having distance = 10 +-+++--+ | zipcode | inc_level1 | inc_level2 | distance | +-+++--+ | 94949 |164 |156 | 0.00 | | 94903 |227 |231 | 3.241369 | | 94947 |268 |323 | 3.393376 | | 94945 |132 |152 | 4.120687 | | 94960 | 60 | 77 | 5.588795 | | 94930 | 55 | 62 | 5.847434 | | 94973 | 88 | 70 | 6.533081 | | 94901 |339 |317 | 6.603527 | | 94904 | 51 | 68 | 7.685091 | | 94963 | 22 | 19 | 8.085156 | | 94946 | 21 | 20 | 8.495255 | | 94939 | 38 | 49 | 8.640175 | | 94933 | 48 | 28 | 8.865090 | | 94938 | 30 | 16 | 9.367796 | | 94925 | 46 | 65 | 9.750440 | +-+++--+ That gives me 15 rows, but I only need one, which would be the total of each of the income level columns, distance is not important to me, that's the one row I want back -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Python program for data entry
I'm looking for a simple python program which offers a graphical interface for entering data into a simple MySQL table. Is there a standard, or semi-standard, program for this? Or can anyone offer a sample program? If relevant, I'm running Fedora-5 with KDE. I read of datakiosk, but couldn't find a Fedora version. Any suggestions gratefully received. -- Timothy Murphy e-mail (80k only): tim /at/ birdsnest.maths.tcd.ie tel: +353-86-2336090, +353-1-2842366 s-mail: School of Mathematics, Trinity College, Dublin 2, Ireland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what is selectivity?
Reading about DBs I am seeing the term selectivity. What does it mean? Seems like it has something to do with the distribution or pattern of data in tables(?) It's coming up in discussions about optimization... Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3 Table Join question
I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: summing of my distance query
Scott, I think you want something like this, then, assuming you still want to limit by radius from a given ZIP. SELECT b.zipcode, sum( b.inc_level1 ), sum( b.inc_level2 ), FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 AND (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) = 10 Scott Haneda wrote: Scott, can you expound on what 1 row would be returned, ideally? The one with the shortest distance? Or a row with the sums of inc_level1 ... inc_level7 ? Looks to me like you're trying to locate all the ZIP codes within a given radius of (in this case) ZIP 94949 with the query below. Yes/no? Dan This is some older code, that did just that, find all zip codes in a certain radius. I need to modify it somewhat. You are correct, I want the sum of Let me show you... SELECT b.zipcode, b.inc_level1, b.inc_level2, (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2) AS distance FROM zipcodes a, zipcodes b WHERE a.zipcode = 94949 GROUP BY distance having distance = 10 +-+++--+ | zipcode | inc_level1 | inc_level2 | distance | +-+++--+ | 94949 |164 |156 | 0.00 | | 94903 |227 |231 | 3.241369 | | 94947 |268 |323 | 3.393376 | | 94945 |132 |152 | 4.120687 | | 94960 | 60 | 77 | 5.588795 | | 94930 | 55 | 62 | 5.847434 | | 94973 | 88 | 70 | 6.533081 | | 94901 |339 |317 | 6.603527 | | 94904 | 51 | 68 | 7.685091 | | 94963 | 22 | 19 | 8.085156 | | 94946 | 21 | 20 | 8.495255 | | 94939 | 38 | 49 | 8.640175 | | 94933 | 48 | 28 | 8.865090 | | 94938 | 30 | 16 | 9.367796 | | 94925 | 46 | 65 | 9.750440 | +-+++--+ That gives me 15 rows, but I only need one, which would be the total of each of the income level columns, distance is not important to me, that's the one row I want back -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with locate() function and index
Hi, I am trying to use locate() function to get position of a substring in a text field which on average has 2000 characters and the search substring is 30 -50 characters long. The table has around 2 million records and looking for a efficient way to do the search. I tried fulltext index but dint see any difference between using it or not. The text field is random characters with space or sometimes no space, does this make the fulltext index not much use? I also wonder about character set and collation, the text field will contain only english alphabets and no special characters so is there a special character set to use rather than the default? Also since the search string will be between 30-50 characters is there any parameters to make the index effective? If my questions are not clear please let me know I will try to explain better, thanks, Kevin
Re: Embedded MySQL
Hi Chris and Melvin, Thanks a lot for the quick responses. I have found it. Actually, it comes bundled with the main distribution. The documentation is in the main reference manual and the header can be found in the include directory. -- Thanks again. Asif I don't see a binary version avaliable, but I know the following configure option exists: --with-embedded-server Build the embedded server (libmysqld). -- I'm assuming this is what you want .. I haven't tried this one, but I think this might help http://mysql-je.sourceforge.net Can some kind soul tell me from where I can download the Embedded Library version of MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with locate() function and index
kevin vicky wrote: Hi, I am trying to use locate() function to get position of a substring in a text field which on average has 2000 characters and the search substring is 30 -50 characters long. The table has around 2 million records and looking for a efficient way to do the search. I tried fulltext index but dint see any difference between using it or not. The text field is random characters with space or sometimes no space, does this make the fulltext index not much use? I also wonder about character set and collation, the text field will contain only english alphabets and no special characters so is there a special character set to use rather than the default? What is the problem you are trying to solve? What problem do you have when you use locate()? Are you running locate on all 2 million records? Also since the search string will be between 30-50 characters is there any parameters to make the index effective? I believe locate operates on the text field argument. So it doesn't use an index. It sounds like you are confused (or one of us is confused :) so why don't you just back up and tell us what you are trying to accomplish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
