MYSQL :: Efficient Country/Province City Setup

When Country Value Is Numeric, Order By City
data in myTable

(id) country city
(1) America Washington
(3) Japan Tokyo
(4) 1 Chicago
(5) France paris
(6) 3 Nagoya
I have data in myTable like the above.

I like to produce order by country when country value is not numeric and order by city when country value is numeric.

The following would-be code doesn't work correctly, but it will show what I want.

would-be code

select t1.id,t1.country, t1.city
from myTable t1
left join myTable t2 on t2.country=t1.id

order by case when t1.top rlike '^[0-9]+$'
then t1.city else t1.country end

target result

(1) America Washington
(4) 1 Chicago
(5) France paris
(3) Japan Tokyo
(6) 3 Nagoya

//alphabetical order : A -> C -> F -> J -> N

View Replies !

Is This An Efficient MySQL Setup?
I would like to offload the MySQL server from my dedicated box in order to speed up page loads.

I don't have an additional dedicated server so my only option is to get a VPS.
But is offloading the MySQL server to a VPS, albeit a modest one, even worthwhile?

I understand this is a very broad question because I am not providing any details but that being the case I am expecting a broad answer

View Replies !

City State Database
I've looked at the Zip Code data on the census website, but don't know
the best approach to get a .dbf file into MySQL.

View Replies !

Random City During Query Circulation
data in myTable1

(id) name
(1) Danaka
(2) Jane
(3) Jean

data in myTable2

(id) city
(1) Tokyo
(2) New York
(2) Chicago
(2) L.A.
(3) Paris
(3) Lyon
I have two tables like the above.

The following codes produce the following results.

code1

select myTable1.id, name, city
from myTable1
left join myTable2 on myTable1.id=myTable2.id
where myTable1.id=2

result1

(2) Jane New York
(2) Jane Chicago
(2) Jane L.A.
code2

select myTable1.id, name, city
from myTable1
left join myTable2 on myTable1.id=myTable2.id
where myTable1.id=2
ORDER BY RAND() LIMIT 0,1

result2

(2) Jane L.A. //random city
I like to produce my target result below.

target result

(2) Danaka Tokyo //random city
(2) Jane Chicago //random city
(2) Jean Lyon // random city

View Replies !

How To Find Nearest City By GPS Information Available
I have a database with cities list with their longitude and latitude. I need to find nearest city for some point (I also know longitude and latitude of that point).

I wonder is it possible to find nearest city using MySQL? Does MySQL able to find nearest number for DOUBLE like 34.782991023?

If yes, how can I use it to find really nearest city? Searching by longitude or latitude alone sound easy, but what about searching by both?

It sound like hard task, because one degree of latitude is always about 110-112 Km, but one degree of longitude can be from 0 to 110 Km (depends on latitude). Ok, even if I take one degree of latitude as 100 Km and one degree of longitude as 70 km, how would you search for the nearest city for some specified point?

View Replies !

Replacing Field CITY In All Lines In A Table
table 'CUSTOMER'

I want to write the PHP code to replace the field 'CITY' with 'Lancaster' for every line in the table where 'CITY' = 'Lanc'

View Replies !

Country Database
im doing a country database which contains countries around the world. My problem is i have no luck in finding the towns, cities, states etc.
im sure somebody here had done something like this one.
can you give me a link where can i download such database?

View Replies !

Country Table
I would like to have an sql script that fills a table with all countries with their codes.
since i'm too lazy to create one myself.

View Replies !

Country Which Is Belongs To Max(id)
data in myTable

(id) countryGroup country
(1) 1 Asia
(2) 2 Europe
(3) 2 France
(4) 1 China
(5) 3 North America
(6) 2 Spain
(7) 1 Korea
(8) 2 Italy
(9) 3 Canada
(10) 2 Germany
(11) 4 Africa
I have data in myTable like the above.

The following code will produce the following result.

code
select max(id) as id,country
from myTable31
group by countryGroup
order by id

result

(7) Asia
(9) North America
(10) Europe
(11) Africa
I can retrieve the last id with max(id) like the above, but countries are retrieved arbitrary.

I like to produce my target result below.

target result1
(7) Korea
(9) Canada
(10) Germany
(11) Africa

View Replies !

One More Column (country) Which Is Not Retrieved
myTable1

(id) city
(1) Moscow
(2) Lodon
(3) Seoul
(4) Denver
: :

myTable2

(id) city country
(1) Moscow Russia
(2) Lodon England
(3) Seoul Korea
(4) Denver America
: :
Let's suppose I have myTables like the above.

And I have the codes below.

code1

select city
from myTable1
where city like '%n%'

code2

select city
from myTable2
where city like '%n%'
(fact1) Code1 and code2 are same except their table names.

(fact2) They will have same results.

(fact3) MyTable1 has two columns, and myTable has three columns.

(fact4) The 3rd column country is not retrieved in code2.

If myTables have tens of thousands of records each, Is each perfomance Same? or is code1 better in perfomance than code2?.

View Replies !

LIKE Operator Searching Country
why following query does not return a result

PHP Code:

$country="South Korea"
$query="SELECT * FROM flags WHERE country LIKE "%Korea South"";

but This works fine.

PHP Code:

$country="South Korea"
$query="SELECT * FROM flags WHERE country LIKE "%South Korea"";

but i need the 1st one. coz in one db table it is saved as "Korea South" and in another table it is saved as "South Korea".

Note:- in actual code "%Korea South" is like "%$country" and which holds "Korea South" and matching to "South Korea" as i sad ealier.

View Replies !

Average Of Top Results For Each Country
Tables are;

users:
------
uid
country (two letter acronym)

teams:
-------
uid
score

Average score for each country (top 150 countries, more than 50 users per country minimum):

Code:
SELECT COUNT(u.country) AS players
, u.country
, AVG(t.score) AS avg_score

FROM users AS u

LEFT OUTER
JOIN teams AS t
ON u.uid = t.uid

GROUP
BY u.country

HAVING players > 50

ORDER BY avg_score DESC

LIMIT 150

View Replies !

World->country->capital Query
I am learning to write SQL queries. I'm using the world database. The Capital column in the Country table only contains numbers (int(11)) which I assume are pointers to an entry in the City table. When I look in the Country table to select the Capital, I get the number. How do I word the query to get appropriate data from the City table, too? Will this become evident as I work my way through the guide?

View Replies !

Autochoosing DropDown Country Based On IP?
http://www.ip2location.com/articles/article10.htm

This is supposed to make it so if you have a drop down of countries, it automatically selects the country youre in based on your ip, but I cant seem to get it to work... I tried running it on my server but I was just given a blank dropdown box..

View Replies !

Country, State, Area, Suburb Storage
Country
- Country_ID
- Country_Name
(PK: Country_ID)

State
- Country_ID
- State_ID
- State_Name
(PK: Country_ID, State_ID)

Area
- Country_ID
- State_ID
- Area_ID
- Area_Name
(PK: Country_ID, State_ID, Area_ID)

Suburb
- Country_ID
- State_ID
- Area_ID
- Suburb_ID
- Suburb_Name
(PK: Country_ID, State_ID, Area_ID, Suburb)

View Replies !

Mysql User Profile Update Problem With Country Dropdown Menu Updates
I have trying to resolve this myself for a few days, but despite referencing two books and falling short on finding sample code on the web for this, I would wellcome your assistance to help me resolve this.

All I'm trying to do is to have an edit account form where the user is able to change their country_id field
in the user table, through a dropdown of country names that is stored in a country table with an (id, name, iso) fields. So far I have been able to do this:

1) register a new user and store their country_id field,
2) display the user's country name with all other user info on the "summary info" page,
3) and I have managed to display on the edit page in a single dropdown the users country 'selected' and all other countries.

The only thing that is not working now is when I try to change the country from the dropdown menu on the edit profile page and click update, nothing happens, the users old country selection remains. I have the value="country_id" for both options as you can see in the dropdown, but its not working. I suspect the variables are getting mixed up somehwere, or I'm not calling them in the right way, but I feel like my head is going to explode soon I have included below the code. I look forward to hearing your advice. T

Miro

<?php
if (isset($_POST['submit']) && $_POST['submit'] == "Update") {
$query_update = "UPDATE user SET " .
"first_name = '" . $_POST['first_name'] . "', " .
"last_name = '" . $_POST['last_name'] . "', " .
"nick_name = '" . $_POST['nick_name'] . "', " .
"alt_email = '" . $_POST['alt_email'] . "', " .
"mobile = '" . $_POST['mobile'] . "', " .
"country_id = '" . $_POST['country_id'] . "' " .
"WHERE username = '" . $_SESSION['user_logged'] . "' " .
"AND password = (PASSWORD('" .
$_SESSION['user_password'] . "'))";
$result_update = mysql_query($query_update)
or die(mysql_error());

$query = "SELECT * FROM user " .
"WHERE ............................

View Replies !

What's The Most Efficient Way?
I've done a database scheme and wonder what the most efficient way of storing certain entries would be. The site will have news, reviews and tutorials and these will be under the same categories. So I'm wondering what the best way to do design the database scheme would be.

Have one table like I've done now in the "content" table with the posibilty to differentiate the entries with the "post_type" field or have three different tables? Below is the table "content".

Field Type Null Default
post_id int(10) No
post_date datetime No 0000-00-00 00:00:00
post_text text No
post_title text No
post_cat_id int(4) No 0
post_description text No
post_name varchar(200) No
post_type int(4) No 0

View Replies !

How Efficient Is In() , And How Much Is The Most That You Should Put In An In()?
I am just wondering as to the efficiency of the in() function in MySQL.

select field1,field2,...,fieldn from table where id in(1,2,3,4,5,6, ... , n);

assuming:
id is an indexed field.
table is a VERY big table (100 000+ - 1 million+ records)

what do you think is the largest number of values you could pass to the in() function without completly flattening your server?

View Replies !

Most Efficient YES/NO...
I)
Table_A has fields Data_1, Data_2, Data_3, and Data_4; I need to determine whether value 'X' is present (at least once) in any of the Data_N fields. All I need is a YES/NO answer: YES, 'X' is present (at least once) in one of the Data_N fields of Table_A [Record number(s) not required!], or NO, 'X' is not present in any of the Data_N fields in any of the records in Table_A.

II)
Similar scenario, but this time Table_A has fields RecID, Data_1, Data_2, Data_3, and Data_4. I need to determine the RecID (none, one, or more) of every record that has value 'X' in any of the Data_N fields.

I'm looking for the query that will be fastest and most elegant to implement.

View Replies !

Quickest And Most Efficient
i run an online game. I want to give each user a ranking, based on how high thier score is.If for example, i had 20,000 players, i dont want to have to update them all one by one, it may strain the server and take a long time.
Is there another way i can assign a rank number (rank 1 has the top score and so on)

View Replies !

Efficient UPDATE
I have a table with the following structure;

CREATE TABLE my_table
(
id_1 int(11) NOT NULL ,
id_2 int(10) NOT NULL ,
stauts tinyint(1) NOT NULL DEFAULT 0 ,
PRIMARY KEY (id_1)
) Engine =InnoDB';

The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query;

UPDATE IGNORE my_table
SET id_1 = 74240, id_2 = 5

I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.

View Replies !

Better Key, More Efficient Indexing
I have a complex query that uses UNION and I want to make this query use indexes for faster execution. Code:

View Replies !

Efficient Tables.
I am starting out on a project where I need to store GPS information. The data consists largely of a series of "Points" each consisting of a longitude,latitude and elevation.

On a typical "route" there could be hundreds of points.

My question is how can I efficiently store this information. It does not sound sensible to normalise this and add hundreds of rows to a table for each "route".

Sample data is along the lines of :

- <trkpt lat="54.016942977905273" lon="-1.4903640747070313">
<ele>82.330078125</ele>

<time>2006-09-03T07:35:41Z</time>

</trkpt>

- <trkpt lat="54.016938870772719" lon="-1.490332055836916">
<ele>0</ele>

<time>2006-09-03T07:35:42Z</time>

</trkpt>

View Replies !

Efficient Storage Of IP Address
I am establishing a database for the purpose of logging access to my secure
webserver and am wanting to make the database as efficient as I can because
it will be doing a lot of work when the site goes live.

What is the most efficient way in a MySQL table to store remote IP
addresses? What data type should I use? Should I just go with a basic
VARCHAR(15) to allow for 4 sets of 3 digits with 3 decimal separators, or is
there a better way?

View Replies !

Efficient Placement Of Fields
is there any noticable efficiency is ordering the position of the field
types?. Like say i place the join keys in a table at the end, and varchars
and text at the top of the table does it really matter?

Also when i do a query like select * from table where id IN (1), where 1 is
usually a primary key int is that quicker than doing where id=1 or is there
no difference and is it handling the int as a string or as an int?

View Replies !

Efficient Database Structure
I have been developing a new website and i need help in deciding the best database structure for it. The site is basically a dating website with various modules like blogs, videos, comments, friends, photos etc. I have created a member table that stores all the basic profile info and created seperate tables for friends, photos, messages, blogs etc and MemberID as foreign key. Now on profile page, i want to display all info related to member like his profile info, his photos, friend list, messages etc and i have to execute 7-10 short queries on profile page for this. Also, i think Joins will not be much helpful as there is one to many relationship e.g there can be more than 1 photo for a member and i am saving each photo in a seperate record. Similar is the case for other tables?

View Replies !

Creating An Efficient Database
i'm currently writing a web based catalogue system in php using a mysql database.

the catalogue has a number of products in it from different brands.

i would like to know if it is more efficient to have each brand in a separate table then a "master" table just listing the brand name and corresponding table

or

all the products in one large table and a "master" table listing each brand in the large table. the large product table would of course have a field to state which brand the product was from.

the efficiency would be based on users being able to access the database via html browsers using php and also search the database.

View Replies !

How To Make Index Most Efficient
I have a large table (> 3,000,000 records). Each records contain a primary key like 'id' and a lots of attributes like 'age', 'department'. I want to build some indices
to accelerate my query.

I read the document which says that too many indices may slow down the INSERT and UPDATE operations. So is there any rule on how to set indices in such table? If I create an index for every field, would that be a very bad idea? If I create an index on each of two fields but not on their combination, will the indices contribute to queries on the combination?

View Replies !

Efficient Way To Count Rows
I'm trying to get the number of rows in a table with a very large number of records in it (~9 million). When I run a select count(*) for some criteria (where name='something', etc) it takes around 6-8 secs for the query to return the value. I tried by using SQL_CALC_FOUND_ROWS with a very small LIMIT but then the query was taking even longer.

I'm using InnoDB, with query caching enabled. I could look at the information_schema and get the approximate row count but whenever I use a where clause it'll be way off mark.

View Replies !

Efficient Way Of Mass Indexing?
I have been working on a program that will populate and index a database. The populating doesn't take too long, but the indexing does. My question is: Is there a better approach to indexing this table than the one I'm using right now?

I'm doing this through QtSql. headers is a QString array of 48 column names I want to index.

for (int i = 0; i < headers.size(); ++i) {
q.exec("ALTER TABLE mysqltable ADD INDEX(" + headers[i] + ");");
}

example run for 20000 rows:
viper,david $ time ./main -r 20000
Database Connection Established
0.520u 0.060s 0:21.27 2.7% 0+0k 0+0io 0pf+0w

View Replies !

Is This INSERT With SELECT As Efficient As Possible?
I'm writing a pretty complex web app and will be repeating many times over a query very similar to below and need to know if there is a more efficient way to do it. If anyone has input, I'd be happy to hear:

INSERT INTO table (somecolumn) VALUES ((SELECT id FROM other_table WHERE foo = 'bar'))

View Replies !

Is Between Date More Efficient For Query?
PHP

//grab rows from yesterday
WHERE (month(articles.date) = month(now()) AND
dayofmonth(articles.date) = (dayofmonth(now())-1) AND
year(articles.date)=year(now()))

View Replies !

Efficient Way To Left Join?
What is the most efficient way to do a table join, but even if there is no matching foreign key, still return the table on the left.

SELECT col, col2, COUNT(col3)
FROM tbl1 t1
LEFT JOIN tbl2 t2
ON t1.id = t2.id
GROUP BY t1.id

But this only works if there is a matching foreign key on t2, I would like all t1 rows to return regardless of whether they exist on t2 or not.

View Replies !

Most Efficient Way To Extract Limited Data
I am currently using the following code, is it the most efficient way to extract and sort the 6 items from the database. The database currently holds over 2,500,000 rows and I want to extract the data as efficiently and quickly as possible. Code:

View Replies !

Which Is More Efficient? One Big Query Or Lots Of Little Queries?
Which is more efficient/faster/better?

running one big query with lots of joins, or lots of little queries in a loop?

so:

1.) one big query:

select cat.*, subcat.* from cat join subcat on ...
or

select * from cat

while $row = $cat_result->fetch()
select * from subcat where catid = $row['catid']
????

View Replies !

Most Efficient, Stable Version Of MYSQL?
We're still using mysql 3.23.56, so it's time to upgrade! It has never once crashed on us, so we've been content with it so far. However, some of the inefficient query optimizations make us want to upgrade.

Does anyone know what the current most efficient and stable version of MYSQL is? We don't require views or cursors or stored procedures or any of that... Just whatever's the most reliable and best optimized for fast query execution of fairly simple SQL.

I know that 3.23 has some issues with using indices correctly during optimization (most notably in ORDER BY ... DESC). Is that problem fixed in 4?

View Replies !

Efficient Ways To Retrieve Specific Rows.
My site used forum software that I wrote myself (in Perl) which, up until
now, has used flat files. This worked fine, however lately I've been wanting
to do more stuff with user accounts, and had been eying MySQL for over a
year.
Finally I've decided to start off small by converting the forum's account
system to a MySQL database (and convert the rest later after I'm
comfortable). So far everything is working fine, and I've figured out how to
create the table, insert records, modify records, and so on.
However I had a question on what was the most efficient way to retrieve
information about a user as I read through the flat file containing
messages.
As each message is read I want to find that user's relevant information,
build it into my output, then continue on to the next message. Now here's my
problem. Since I'm reading a flat file in a way that was once trying to be
somewhat memory efficient (on really slow server way back when, trying to
avoid arrays and hashes) I'm finding that I'm having to send separate
questions to MySQL as each message comes up. Say from 1 to 20 very simple
questions to complete printing the page. Also, sometimes the questions might
be repetitive - since I'm not storing any of the results in memory, if a
user appears twice I ask about it twice.
Would I really be better off trying to find a way to consolidate all
distinct users into a single question? Or is MySQL efficient enough that
this isn't really much of a concern?

View Replies !

Efficient String Compare To A Group Of Values
(Post edited to change "WHERE field = options" to "WHERE field IN options").

I have a website using PHP and MySQL 5.0; the database holds personal names, addresses and various information. Right now I'm building a query which checks whether a person lives in a certain group of UK counties.

I've got arrays set up in PHP which hold the groups of counties, and I've dynamically created a "group" from each array. So, in PHP,

$county_array = array('Kent','Surrey','London','Essex','...');
$county_group gets set to "('kent','surrey','london','essex',...)"

Each group may hold perhaps 20 individual counties.

Now, for my database query, I use:

$query = "SELECT ... WHERE lower(county) IN $county_group";

Is the "WHERE field IN (a|b|c|d|e|...)" a slow comparison in MySQL? Is there a more efficient way to do it? Is this something that stored procedures can help me with?

View Replies !

The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people
The table is quite large (300,000 rows)
I need to write code that update many of the rows in the table

Here is what I need to do
Two of the fields that I store about each person are: location and country
The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on

I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly

My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements

I cannot use LOAD DATA as this is product database

Is there a way to update multiple rows (each with a different value and condition in a single query?

View Replies !

Efficient SELECT From Multiple Tables With Same Formatting?
Let's say I have 2 tables with the exact same formatting (field/column names, etc) -- the only difference is the name of the tables.. one is called "table1" the other is called "table2"

If I wanted to query both of the together is this legal and good coding practice?

"SELECT id, headline, permalink, body FROM table2007_11, table2007_12 WHERE id=23"

understandly I get this error: Column 'id' in field list is ambiguous"

is there a better way to do this than printing out each table name for each field individually? (ie. id. table2007_11, id. table2007_12, etc)?

View Replies !

Most Efficient Way Of Storing Data From Multiple Accounts
As part of a system I am putting together I need to allow users to create thier own accounts on my servers. Each user can create their own account, and then have their users register for it. Each account needs it data seperate from the others, a member registered for one account should not be able to view another account and a username registered with one account should still be available to the other accounts.

The ways I have been looking at are:

1. Create a new database for every account created so that all users are kept in seperate databases.

2. Have one table for users, one for topics, one for posts etc and then associate each row within this table with the relevent account. So for example a user could register with the forum with the ID 4, so their user entry would be

Userid: 234
Username: xxxx
Password: xxxx
Forumid: 4

Then when a new member registers with any account I simply check that there is not another user with the same account ID and username. Indexes on relevent fields in this system could help speed up huge tables.

I expect to quickly have 20,000 plus accounts (and in theory it could go up to hundreds of thousands).

I guess my question is which of these methods is better from a speed point of view once we get a large number of accounts and users. Also, are there restrictions on the number of fields in a table that could cause problems?

Using MySQL by the way, on an Apache server.

View Replies !

Most Efficient Date Field Type For Selects?
I have searched this forum seeking the answer to this question, to no avail.

I am making an events diary that will have a lot of entries. Entries will be entered in all kinds of order, including multiple events spanning a year or so.

Even multiple events of the same type will be individual entries. (e.g. weekly chess club meeting every weds at 7pm will be 52 entries)

My question is : Which is the best format to store dates?

Native mysql date type
Integer timestamp
A.n.other?

I suppose a worst case scenario is imagine I want to generate a calendar for July (31 days) and I want to show how many events there are for each day.

1st July (13 events)
2nd July (22 events)

and so on ... it is possible that any given day could have up to 100 entries.

What in your opinion is the best column type to use and ss there any kind of Indexing I can use to speed up select by dates? Or any other tip or trick you'd be prepared to share with me?

View Replies !