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 I have been bashing my head on that a bit, right now I get ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause -- - 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: mysqld refuses to run on boot
Joerg Bruehe wrote: 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 Hi Joerg, I'm using Ferdora Core 4 of Red Hat and I have in /etc/rc.d/init/ a file mysqld which when I use ./mysqld start does start the system. But it has to be done every time I turn on the computer. I do not understand your patch. I can't find what I think is where you want to put one line of code. The other lines do not exist. Or I don't know what I.m doing :-) Karl This fix will appear in 5.0.23. HTH, Jörg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Version Numbers - Precedence
Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. Is that correct? Or 5.0.5 came first and then came 5.0.22? -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Version Numbers - Precedence
Hi, I am replying to my own post to clarify my query. Actually, I have 5.0.22 installed and I want to use the BIT data-type on InnoDB Engine. Can I do it or do I have to install 5.0.5 version (which is not stable) ? -- TIA Asif On 6/24/06, Asif Lodhi [EMAIL PROTECTED] wrote: Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. Is that correct? Or 5.0.5 came first and then came 5.0.22? -- Asif -- 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
On Saturday 24 June 2006 12:49, Karl Larsen wrote: Hi Joerg, I'm using Ferdora Core 4 of Red Hat and I have in /etc/rc.d/init/ a file mysqld which when I use ./mysqld start does start the system. But it has to be done every time I turn on the computer. I do not understand your patch. I can't find what I think is where you want to put one line of code. The other lines do not exist. Or I don't know what I.m doing :-) chkconfig mysqld on -- Duncan Hill - Developer Critical Software +44 (0)870 770 8190 -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Version Numbers - Precedence
On Saturday 24 June 2006 12:50, Asif Lodhi wrote: Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. Is that correct? Or 5.0.5 came first and then came 5.0.22? After. 5 22. Major.Minor.Release -- Scanned by iCritical. -- 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
Duncan Hill wrote: On Saturday 24 June 2006 12:49, Karl Larsen wrote: Hi Joerg, I'm using Ferdora Core 4 of Red Hat and I have in /etc/rc.d/init/ a file mysqld which when I use ./mysqld start does start the system. But it has to be done every time I turn on the computer. I do not understand your patch. I can't find what I think is where you want to put one line of code. The other lines do not exist. Or I don't know what I.m doing :-) chkconfig mysqld on I am really sorry. I had to read man chkconfig and got really confused but did it because it appeared to do something to the mysqld file. Maybe it will work. I will see soon. Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join Help
the thing with JOINs are you gotta JOIN a table ON another table where something matches something else (in most cases). I tried to clean this up a bit but im rather new to mysql. 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 as ven_city, tv.STATE ven_state, tv.ZIPCODE as 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 INNER JOIN tbl_ARTST as art ON? INNER JOIN artist_tourdate artd ON? INNER JOIN tbl_VENUES tv ON? INNER JOIN tbl_VENUE_CAPACITY tvc ON? INNER JOIN tbl_VENUE_AGE_XREF tvax ON? INNER 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 iHTH, On 6/23/06, Peter Brawley [EMAIL PROTECTED] wrote: 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.WEBSITEas 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.PKEYAND 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
Re: QUERY
Karl Larsen wrote: I and a friend have written SQL to big Oracle DB but not much to MySQL on our own computers. I find that there is a QUERY in the list for MySQL but it does nothing much. Also I was glancing through the mamouth MySQL reference manual and saw there are a bunch of tools that can be run outside of the interpreter and one has in it's title query. It seems this software will do a job like the Oracle QUERY. I have not verified this but hope to do so soon. If anyone on this list has done serious QUERY with MySQL I would sure like to know how you did it. Karl Larsen SQL stands for 'Structured Query Language'. We are doing queries all day long. Reading between your lines, I would guess that 'QUERY' is an Oracle utility? Since this is a MySQL list, you might explain to us what QUERY does in Oracle so we can tell you if there is a MySQL equivalent. --J -- 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
H - try adding a group by b.zipcode to the end ... Dan Scott Haneda wrote: 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 I have been bashing my head on that a bit, right now I get ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY
John Hicks wrote: Karl Larsen wrote: I and a friend have written SQL to big Oracle DB but not much to MySQL on our own computers. I find that there is a QUERY in the list for MySQL but it does nothing much. Also I was glancing through the mamouth MySQL reference manual and saw there are a bunch of tools that can be run outside of the interpreter and one has in it's title query. It seems this software will do a job like the Oracle QUERY. I have not verified this but hope to do so soon. If anyone on this list has done serious QUERY with MySQL I would sure like to know how you did it. Karl Larsen SQL stands for 'Structured Query Language'. We are doing queries all day long. Reading between your lines, I would guess that 'QUERY' is an Oracle utility? Since this is a MySQL list, you might explain to us what QUERY does in Oracle so we can tell you if there is a MySQL equivalent. --J Sorry, I was not thinking. In both MySQL and Oracle you do a Query using the SQL SELECT-FROM-WHERE-YES-NO and such and come up with the display of data your boss wants in the report. So your equiped to do a Query and you need to learn just which SELECT to use. I need to learn how you send a Query result to a file where it can incorporated into a document. The charts I'm making need some help too but they will come as I learn to use MySQL. Karl -- 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
Hi,
The table has ~2 million records and has 3 columns one of which is a text
field which on an average has 2000 characters(English alphabets, space are
there for now, but if required can be eliminated).
eg: adfastsdfasgadfdfdsaffagdasfd adfsadfadsgdsfdfsdfsgsdfdsg fg adfafg
adfddfgadsfdsgfghfghjgafedsf.
The query I am trying is like this...
Select pid,locate('affagdasfd adfsadf',txtfield) from tablename where
locate('affagdasfd adfsadf',txtfield) 10;
The substring I am searching for is always 30-50 characters in length. The
query execution takes more than 30 seconds and I would like to improve this.
I tried fulltext index on this field hoping that locate() will be faster,
but it dint make any difference. It would be great help if you could help
solve this problem.
thanks
On 6/24/06, John Hicks [EMAIL PROTECTED] wrote:
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.
Re: Version Numbers - Precedence
2006/6/24, Asif Lodhi [EMAIL PROTECTED]: Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. mathematically speaking, there no such number like 5.0.5 anyway... 5.05 perhaps... MySQL are numbered according to a X.Y.Z release number. X : is the major version, where major uplift and features are added (such as triggers, stored procedures) Y : is the minor release, where few features are added (events, partitionning) Z : is the revision number. No new features are added, only bugfix and security fix. and they are separated by dots. 5.0.5 being an beta release, I'll suggest to upgrade to the latest 5.0.z :-) -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
patch for 5.1.11 to use openssl
i don't know the proper place to send this and i didn't see any mention of it in the list archives. there is a really small problem with the configure script in 5.1.11 (and other versions, perhaps, but i haven't looked) that prevents building with openssl support. patch below. thanks, ben -- --- configure.orig Sat Jun 24 08:45:34 2006 +++ configure Sat Jun 24 08:45:59 2006 @@ -39099,7 +39099,7 @@ # compiler warnings when using gcc 3.x if test $openssl_include != /usr/include then -openssl_includes=-I$ssl_include +openssl_includes=-I$openssl_include fi # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
assembler not found or what?
Hi all i am compiling mysql 4.1.20 with he following options: export CFLAGS=-O3 -fomit-frame-pointer -march=nocona -msse3 export CXXFLAGS=-O3 -fomit-frame-pointer -felide-constructors -march=nocona -msse3 ./configure --prefix=/opt/.mysql-4.1.20 --enable-assembler --with-mysqld-ldflags=-all-static --enable-thread-safe-client --with-gnu-ld --with-mysqld-user=mysql --without-debug --with-charset=latin1 --with-collation=latin1_danish_ci --without-innodb --with-lib-ccflags=-O3 -fomit-frame-pointer -march=nocona -msse3 When configure runs it tells me that it isn't going to use assembler functions, even tho /usr/bin/as is in the path. anyone have any idea why? Regards Martin Jespersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting data from 2 local DBs with same table structure
Hello List! Does anybody know how to select and build a result set from two or more databases with the same table structures that are on the same server. I need the query to select the data from table1 on db1 and then select the data from table1 on db2 and return a result of where if a field data value is found in both tables that matches on name for example, it would count the occurrences. Thanks in advance Craig
Re: selecting data from 2 local DBs with same table structure
I need the query to select the data from table1 on db1 and then select the data from table1 on db2 and return a result of where if a field data value is found in both tables that matches on name for example, it would count the occurrences. Not entirely clear. Do you mean ... SELECT name, COUNT(field) AS Count1, (SELECT COUNT(field) FROM db2.tbl2 WHERE db2.tbl2.name=db1.tbl1.name) AS Count2 FROM db1.tbl1 GROUP BY name; ? PB - Cx Cx wrote: Hello List! Does anybody know how to select and build a result set from two or more databases with the same table structures that are on the same server. I need the query to select the data from table1 on db1 and then select the data from table1 on db2 and return a result of where if a field data value is found in both tables that matches on name for example, it would count the occurrences. Thanks in advance Craig No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.3/374 - Release Date: 6/23/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.3/374 - Release Date: 6/23/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A lot of HD Writing
Hi, is usual to have a lot of HD writing on a MySQL server where (according to mytop) there are between 800 and 1200 queries per second? my MRTG is showing a lot of HD Writing and i wanted to know if it's usual. The database is growing about 11 MB every 3 minutes. FYI, I don't have any log-type (binlog, queries log or slow queries log) activated. An extra question: Which scale of MaxBytes should i use for my HD Writing MRTG config? i'm using MaxBytes[hd-write]: 2400 right now. The Disk is a 80 GB SCSI disc with RAID 1 If you need more information just ask :). Thanks!! Santiago del Castillo
Re: A lot of HD Writing
At 06:55 PM 6/24/2006, Santiago del Castillo wrote: Hi, is usual to have a lot of HD writing on a MySQL server where (according to mytop) there are between 800 and 1200 queries per second? my MRTG is showing a lot of HD Writing and i wanted to know if it's usual. The database is growing about 11 MB every 3 minutes. FYI, I don't have any log-type (binlog, queries log or slow queries log) activated. Well, if this keeps up, you better run out and buy more hard drives.g Are you using InnoDb or MyISAM tables? You can run Show ProcessList to see what task is currently executing on the MySQL server. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
