Download States and Capitals Database
Dear all, I googled a lot and find data of all countries , cities , location etc from Geo Spatial websites but I am able to find the data that shows all *states their respective capitals* in world. Please let me know if anyone as prior information about this ? Thanks
Mysql user kill privilege
Hiya I got a request from a client for the rights to kill his queries if something goes wrong. Long story short, he doesn't want to have to phone. I see via the MySQL documentation, that the client would need PROCESS and SUPER privileges. Im not happy with that. Would know of an alternative solution. I could reply, write better queries, but somehow I dont think the client would be to happy with that. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql user kill privilege
I got a request from a client for the rights to kill his queries if something goes wrong. Long story short, he doesn't want to have to phone. I see via the MySQL documentation, that the client would need PROCESS and SUPER privileges. Im not happy with that. Would know of an alternative solution. I could reply, write better queries, but somehow I dont think the client would be to happy with that. A user can always kill his own queries. As long as he can have 2 simultaneous connections or more, killing his own queries should be simple. http://dev.mysql.com/doc/refman/5.1/en/kill.html If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
query efficiency
Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this? The table is huge, 25M rows or so, but I can create any indexes.
change from an OR to an AND condition
Hi, I have a query where I'm using a left outer join to connect two tables, entries and entries_tags, where entries_tags is a join table between entries and another table, tags. In my query I am selecting values from the entries table where the entries have any of the provided tags - WHERE entries_tags.tag_id IN (x, y, z) What I would like to do is switch this from an OR condition to an AND condition, so that an entry is only returned if it has all of x, y, and z. How would I go about doing this? Thanks, Simon
Re: change from an OR to an AND condition
Just to clarify, here is some more info: Here is what the query looks like: SELECT DISTINCT `entries`.id FROM `entries` LEFT OUTER JOIN `entries_tags` ON `entries_tags`.entry_id = `entries`.id LEFT OUTER JOIN `tags` ON `tags`.id = `entries_tags`.tag_id WHERE (`entries_tags`.tag_id IN (5,6)); So in this instance, what I'm looking for are the entries where they have two entries in entries_tags with tag ids 5 and 6, instead of entries that have either. Thanks again, Simon On 27 September 2011 09:46, Simon Wilkinson simon.wilkin...@gmail.comwrote: Hi, I have a query where I'm using a left outer join to connect two tables, entries and entries_tags, where entries_tags is a join table between entries and another table, tags. In my query I am selecting values from the entries table where the entries have any of the provided tags - WHERE entries_tags.tag_id IN (x, y, z) What I would like to do is switch this from an OR condition to an AND condition, so that an entry is only returned if it has all of x, y, and z. How would I go about doing this? Thanks, Simon
Re: query efficiency
2011/09/27 05:32 -0700, supr_star Here's a simple query: select name,status,min(dt), max(dt),count(*) from mytable group by name I need an efficient way to get this data, along with the status of the row with the max(dt). 'status' is not in the group by, so I get apparently random statuses. Is there any way to do this without a table join? Or I guess the real question is: What is the most efficient way of accomplishing this? I cannot answer the real question, but there is this: select name,(SELECT status FROM mytable AS x WHERE x.name = mytable.name AND x.dt = max(mytable.dt)) AS status, min(dt), max(dt),count(*) from mytable group by name I will not vouch that this is more efficient than joining. (Surely there are enough of this that this is entitled to a special SQL construct.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [GENERAL] Download States and Capitals Database
This Link gives the capitals of all countries but I need the states and their capitals in all these countries too.. But I think this is not possible because very few countries are divided into states and some into cantons, county etc. Thanks planas wrote: On Tue, 2011-09-27 at 12:18 +0530, Adarsh Sharma wrote: Dear all, I googled a lot and find data of all countries , cities , location etc from Geo Spatial websites but I am able to find the data that shows all *states their respective capitals* in world. Please let me know if anyone as prior information about this ? Thanks Try this link from Wikipedia http://en.wikipedia.org/wiki/World_capital_cities_by_country . I just searched for world capitals by country. -- Jay Lozier jsloz...@gmail.com
how to limit the creation on disk temp tables
Hello List, so far I could not find any answer on how to abort queries that exceed certain size of internal temporary tables. On certain quite often scenarios these internal tables are being converted to Myisam on disk tables. And that creates a high IO depending on situation. Putting tmpdir in tmpfs solves half of the problem. Have anyone came up with a better approach on how to restrict them? Sincerely, Alexandr N -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [GENERAL] Download States and Capitals Database
- Original Message - From: Adarsh Sharma adarsh.sha...@orkash.com This Link gives the capitals of all countries but I need the states and their capitals in all these countries too.. But I think this is not possible because very few countries are divided into states and some into cantons, county etc. Yep. The only one I can think of off the top of my head is the USA, although I can't exactly claim accurate geopolitical knowledge about the whole world :) Here in Belgium, we have a bunch of provinces. France has departements. It varies a lot. I suppose you could build a hierarchy of geographical subdivisions, but you'd still have to save the appropriate naming schemes per-country. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to limit the creation on disk temp tables
- Original Message - From: Alexandr Normuradov norma...@gmail.com so far I could not find any answer on how to abort queries that exceed certain size of internal temporary tables. I'm not sure there is. On certain quite often scenarios these internal tables are being converted to Myisam on disk tables. And that creates a high IO depending on situation. Putting tmpdir in tmpfs solves half of the problem. Well, yes, although it's more efficient to increase your max_temporary_table variable (or whatever it's called; lazy) so you don't waste cycles on the conversion. Have anyone came up with a better approach on how to restrict them? Well, the whole idea of a database is that you ask it a question and it gives you an answer - there's not really any mechanism for asking a question and then saying don't answer that. I suppose you could point your tmpspace to an unwriteable location, although I'm not sure what the effect would be on the rest of the database's operations. If you do an explain of each query beforehand, you'll get an idea of what it's going to do and you could prevent actually executing it based off that, but it'd not be an exact science either. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
