Re: Optimizing GROUP BY and ORDER BY

> > Arthur > > On Fri, Jul 25, 2008 at 4:27 AM, Michael Stearne <[EMAIL PROTECTED]> > wrote: >> >> I have a query: >> >> SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE ( >> Country != 'USA' AND Country != 'US' AND C

Optimizing GROUP BY and ORDER BY

I have a query: SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE ( Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP BY Country ORDER BY Cnt DESC LIMIT 8 that gets the top 8 non-US countries fr

Re: Query/Key Optimization

As a note. The query itself may not be taking long but there are many "Sorting result " and "Copying to tmp table " in myTop. Thanks, Michael On Tue, May 6, 2008 at 3:26 PM, Michael Stearne <[EMAIL PROTECTED]> wrote: > Hi. > > The main table for our site i

Query/Key Optimization

Hi. The main table for our site is called properties and it gets hit quite often (several times per second) something like: Queries Total: 41,496 Avg/Sec: 6.89 Slow: 0 Cache Hits : 15,096 Avg/Sec: 2.51 Now/Sec: 0.00 Ratio: 36.38% Threads Total: 1 Active: 1 Cach

Single Column Indexes Vs. Multi Column

For a query like: SELECT id FROM properties WHERE `Country` = 'USA' AND Type='Residential' Is an multi-column index that is (Country, Type) better or worse or the same as a single index Country and another single index Type. Thanks, Michael -- MySQL General Mailing List For list archi

Re: Replication Falls Out Of Sync With LOAD DATA

On Nov 28, 2007 11:18 PM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > The reason I asked about version is that it looks like there is problem > replcating a load data infile command from some versions of 4.x to 5.x > slaves. Master and Slaves are 5.x. Hopefully I've figured out the issue. When t

Replication Falls Out Of Sync With LOAD DATA

We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes

Replication vs. mysql-table-sync

Is mysql-table-sync design to be used as a fix for when your replication is out of sync OR can it be used instead of replication? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Use Samba Share For Data Directory

On 1/26/07, Dominik Klein <[EMAIL PROTECTED]> wrote: Michael Stearne schrieb: > We have 5.0.27 installed on a CentOS machine that doesn't have a ton > of disk space. Is it possible to point the data directory to lie on a > samba connected share? The samba share does n

Use Samba Share For Data Directory

We have 5.0.27 installed on a CentOS machine that doesn't have a ton of disk space. Is it possible to point the data directory to lie on a samba connected share? The samba share does not support Unix file permissions so it is not possible to set mysql as the owner of the files. Is this possible

Re: How to pronounce MyISAM and InnoDB

I just say "My, I, Sam" and "inno, d, b" Michael -Original Message- From: "js " <[EMAIL PROTECTED]> Date: Mon, 8 Jan 2007 00:09:15 To:mysql@lists.mysql.com Subject: How to pronounce MyISAM and InnoDB Hi list, Sorry for this silly question but I've been always had trouble pronou

Last Inserted ID Using LOAD DATA

I have a group of updates that need to be done using LOAD DATA INFILE. Within this file there are some INSERTS. Is there anyway that after an INSERT happens I can use the auto-increment ID that was just generated in the next statement. Similar to PHP's mysql_insert_id() function. Thanks, Michae

Re: UK Postcodes

On 1/24/06, James Harvard <[EMAIL PROTECTED]> wrote: > It's the Royal Mail. Ordnance Survey, the government mapping agency for the > UK, are in on it too. > > To read their web site (as I have done a couple of years ago, and just now > too) you would think it had never occurred to them that peopl

Re: Postcode Search

We use the inradius function as described here: http://www.phparch.com/discuss/index.php?t=msg&th=878 It's not the fast way I'm sure but seems to be pretty reliable. Instead of the zipdata table you would point it at the Postcodes table and the Properties table. You'll also need to add an X,Y (

Re: Problem With FulltText Index and VarChar

On 12/30/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > Hello. > > > I guess my question is, how do I make sure the full text search is > > being done against the CommentsIDX and not just against the individual > > fields... > > > This shows that the search is done against concatenation of the fie

Re: Problem With FulltText Index and VarChar

`,`City`,`Country`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=101131 ; I guess my question is, how do I make sure the full text search is being done against the CommentsIDX and not just against the individual fields... SELECT * FROM properties WHERE MATCH (Comments,Subtype,Heading,Zip,A

Problem with FullText Indexes

I am trying to do a fulltext search with a multi-field index using MySQL 4.1.15. When I create a full text index of my Comments field which is of type TEXT. I can do a fulltext search fine. But when I add another field (like a varchar or even Text) to that index or change the name of the index I

Problem With FulltText Index and VarChar

I am trying to do a fulltext search with a multi-field index using MySQL 4.1.15. When I create a full text index of my Comments field which is of type TEXT. I can do a fulltext search fine. But when I add another field (like a varchar or even Text) to that index or change the name of the index I

Re: Repeating Results in Query

Ahh, thanks. Arthur Fuller wrote: GROUP BY Left(sku,5) hth, Arthur -Original Message- From: Michael Stearne [mailto:mstearne@;entermix.com] Sent: Thursday, October 31, 2002 11:31 AM To: [EMAIL PROTECTED] Subject: Repeating Results in Query The query below: SELECT DISTINCT LEFT(sku

Repeating Results in Query

The query below: SELECT DISTINCT LEFT(sku,5) AS Item_Sku,count(*) as Quantity_Sold, SUM(price) AS Total_Sales FROM `orderline` WHERE (order_date>='20021030' AND order_date<='20021031') GROUP BY sku ORDER BY Item_Sku ASC Produces results like: Item_Sku Quantity_Sold Total_Sales LE104 1 18

Re: MACOSX and PHP and MYSQL

There is a great page with instructions and binaries for this stuff at http://www.entropy.ch/software/macosx/ Michael Bensin Joseph wrote: > I have MACOSX 10.1.3. > PHP runs fine with no problem could someone help me install or direct me > to where i can get instruction of how to install MYSQL

Re: OS X

Try this like, http://www.entropy.ch/software/macosx/ , they're great instructions and binaries. Michael On Tuesday, March 5, 2002, at 06:38 PM, Ray Hughes wrote: > I have been going out of 'my' mind. I am trying to install mysql on > a Macintosh OS X server. Can anyone give me instructio

Re: How to unjar a package!

It's like tar. I do "jar xvf file.jar" "man jar" even works! :-) Michael Rahadul Kabir wrote: >can some one please tell me how to unjar a package, like a package which >comes with .jar extension (executable file). >thanks > >-

Re: Tighly packed table

see that >>there was not much use of doing it. >> >>Dobromir Velev >>Software Developer >>http://www.websitepulse.com/ >> >> >>-Original Message- >>From: DL Neil [mailto:[EMAIL PROTECTED]] >>Sent: Saturday, January 26, 2002 5:39 PM >

Re: Tighly packed table

runs, then prepare for a coffee overload whilst you >>>perform the ALTER TABLE, then get ready >>>because if you shorten the field to (say) 12 characters/bytes the >>>very next day, someone with a 13 character >>>name is going to try to register! >>> >

Re: Tighly packed table

ing to try to register! > > I'm wondering just how much space this 'little' exercise is going to > save, either as a ratio of the size of the > db, or as a ratio of HDD size? > > My glass is half-empty! > =dn > > > - Original Message - > From

Re: Tighly packed table

M and Mac OS X. Which is performing as better it seems than the SCO and Linux boxes running the same DBs. Thanks, Michael Christopher Thompson wrote: > At 05:58 PM 1/24/2002 -0500, Michael Stearne wrote: > >> The problem is, this query really hurts (I don't know if it finis

Re: Tighly packed table

The problem is, this query really hurts (I don't know if it finishes) for unindexed field for 2.9 million rows. But I'm sure it will finish eventually. Michael Roger Karnouk wrote: >select max(length(firstname)) from TableName; > >-Original Message----- >From: M

Re: Tighly packed table

Christopher Thompson wrote: > At 04:10 PM 1/24/2002 -0500, Michael Stearne wrote: > >> We have a somewhat large read-only table (2.9 million recs). I am >> wonder if there is a utility that will look at each row of each >> columns and come up with a summary of the lar

Tighly packed table

t the longest first name is 12 characters. That way I can ALTER the firstname field to be a char or varchar of 12? What would be better BTW? Thanks, Michael Stearne - Before posting, please check: http://www.mysql.com/manua

Varchars Vs. Chars on Read Only Table

When doing selects on a read-only myISAM packed table, are varchar fields still slower than char fields? Thanks, Michael - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql