Re: Distinct select over 2 fields?
Why don't you just use a GROUP BY on lat,long? > You could try using CONCAT: > > select distinct(CONCAT(lat, long)) from table where ... > > Steve Musumeche > CIO, Internet Retail Connection > [EMAIL PROTECTED] > > > > Brian Dunning wrote: > > Lat & lon are two different fields. Either can be duplicated, but not > > both. > > > > > > On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote: > > > >> Select DISTINCT(lat_long_field) from table where... > >> > >> Steve Musumeche > >> CIO, Internet Retail Connection > >> [EMAIL PROTECTED] > >> > >> > >> > >> Brian Dunning wrote: > >>> Many different records will be returned though, I just don't want > >>> any dupes where both lat/lon is the same. > >>> > >>> :) > >>> > >>> On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: > >>> > select * from table where . limit 1 > > that would do it if you don't care which one it returns > > JC > > On Tue, 12 Sep 2006, Brian Dunning wrote: > > > I'm searching a database of geopoints, and when two records have the > > same latitude and longitude, I only want to return one of them - > > basically just find all the unique locations. How do you set up a > > select like this? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld running at 100% after being accessed in startup script
Hi, I have a strange problem. I am running mysqld (5.0.20) on NetBSD 3.0 and after the startup scripts are complete the mysqld process ramps up to 100% utilization. However this only happens if I make mysql calls from within the startup scripts. If I wait until the startup scripts have completed before accessing the database, then everything is ok. The server is being run as follows: /usr/pkg/libexec/mysqld -uroot & and I am accessing it using something like: echo "select * from MyTable" | /usr/pkg/bin/mysql -uroot -ppassword MyDatabase If these are put next to each other in the startup scripts (rc.local), mysqld becomes very unhappy. This has worked fine in the past with mysql 3 and 4. Having compiled and run mysqld with --debug the following appears in the trace file: > [EMAIL PROTECTED] : | >hash_free > [EMAIL PROTECTED] : | | enter: hash: 0x852e76cd > [EMAIL PROTECTED] : | | >my_free > [EMAIL PROTECTED] : | | | my: ptr: 0x8534400 > [EMAIL PROTECTED] : | | [EMAIL PROTECTED] : | [EMAIL PROTECTED] : | >hash_init > [EMAIL PROTECTED] : | | enter: hash: 0x852e76c size: 128 > [EMAIL PROTECTED] : | | >init_dynamic_array > [EMAIL PROTECTED] : | | | >my_malloc > [EMAIL PROTECTED] : | | | | my: size: 1024 my_flags: 16 > [EMAIL PROTECTED] : | | | | exit: ptr: 0x8534400 > [EMAIL PROTECTED] : | | | [EMAIL PROTECTED] : | | [EMAIL PROTECTED] : | [EMAIL PROTECTED] : | >process_alarm > [EMAIL PROTECTED] : | | info: sig: 14 active alarms: 0 > [EMAIL PROTECTED] : | [EMAIL PROTECTED] : | >process_alarm > [EMAIL PROTECTED] : | | info: sig: 14 active alarms: 0 > [EMAIL PROTECTED] : | [EMAIL PROTECTED] : | >process_alarm > [EMAIL PROTECTED] : | | info: sig: 14 active alarms: 0 > [EMAIL PROTECTED] : | [EMAIL PROTECTED] : | >process_alarm > [EMAIL PROTECTED] : | | info: sig: 14 active alarms: 0 > [EMAIL PROTECTED] : | [EMAIL PROTECTED] : | >process_alarm > [EMAIL PROTECTED] : | | info: sig: 14 active alarms: 0 > [EMAIL PROTECTED] : | http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index merge optimization (with OR) and table joins
Thanks for the reply, it's much appreciated. I'll have another look at the tables, although my criteria were such that the query should have only returned a tiny fraction (<0.1%) of the rows (which is why I was confused). If I get any closer to a solution I'll try and post with a bit more info. And next time I'll try and remember to send the SHOW CREATE TABLE statements as well:) Kind regards Stuart On 5/4/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: > (again, apologies for the lateness...) > > MySQL has a cost-based optimizer. If it's deciding that a full-table > scan is appropriate, there's a reason. If more than 30% (approx) of > the table would be returned in a range query, the optimizer reasons > that it's LESS expensive to just do a full table scan. Otherwise, if > say you're returning 50% of the rows, you have to find the pointer to > the row using the index, then go to the row. Doing a table scan > eliminates needing that extra step of the index. > > Next time full SHOW CREATE TABLE statements would be useful. > > -Sheeri > > On 4/11/06, Stuart Brooks <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I have been having a hassle getting the index_merge to work as expected > > when I am joining 2 tables on MySQL 5.0.19. The following example should > > make it clear: > > > > Table A > > key1 (primary key) > > key2 > > some_data > > > > Table B > > key1 (indexed) > > key2 (indexed) > > more_data > > > > SELECT a.key1,a.key2,b.more_data > > FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) > > WHERE (a.key1=10); > > > > This works as expected. An EXPLAIN yields : > > a | const| PRIMARY > > b | index_merge | key1,key2 > > > > However if I make the WHERE clause a range (or remove it altogether): > > > > SELECT a.key1,a.key2,b.more_data > > FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) > > WHERE (a.key1<10) #optional > > > > I end up with... > > a | range | PRIMARY > > b | ALL | none > > > > which is a brute force attack on table b. Am I missing something here, I > > would have expected it to use an index merge on table b in both cases. > > Is there a way to force it to use the index merge? > > > > Regards > > Stuart > > > > > > > > -- > > 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]
Index merge optimization (with OR) and table joins
Hi, I have been having a hassle getting the index_merge to work as expected when I am joining 2 tables on MySQL 5.0.19. The following example should make it clear: Table A key1 (primary key) key2 some_data Table B key1 (indexed) key2 (indexed) more_data SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key1=10); This works as expected. An EXPLAIN yields : a | const| PRIMARY b | index_merge | key1,key2 However if I make the WHERE clause a range (or remove it altogether): SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key1<10) #optional I end up with... a | range | PRIMARY b | ALL | none which is a brute force attack on table b. Am I missing something here, I would have expected it to use an index merge on table b in both cases. Is there a way to force it to use the index merge? Regards Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
