CODEWITHSUNDEEP
phpmysql
Nita
Nita

Reputation: 561

I Have MySQL Query I Would Like To Make Faster

$result1 = mysql_query("SELECT distinct city FROM postcodedb WHERE web='ALR' ORDER BY city") or die(mysql_error());
while($row1=mysql_fetch_array($result1)) {
    $city = $row1['city'];
    $result = mysql_query("SELECT * FROM postcodedb WHERE area='$city' AND city='$city' LIMIT 1") or die(mysql_error());
    while($row=mysql_fetch_array($result)) {
        $city = $row['city'];
    }
}

So in database records look like this:

---------------------
id  city     area
---------------------
1   Bromley  Bromley <- I need this record

2   Bromley  other1

3   Bromley  other2

4   Ilford   Ilford <- and then this one

5   Ilford   other1
---------------------

and there is more cities and areas assigned to these cities.

So, what I’m trying to get is a listing of distinct cities, but important is for me is that i get row ID where both city and area are the same??

This query works as I want it to work but is a bit slow, and I believe there is a better way to achieve the same result?

I hope I explained myself clearly.

Thank you very much in advance!

Nita

Upvotes: 0

Views: 59

Answers (2)

Nita
Nita

Reputation: 561

    $result1 = mysql_query("SELECT distinct city FROM postcodedb WHERE web = 'ALR' and city = area ORDER BY city") or die(mysql_error()); 
while($row1=mysql_fetch_array($result1)) {
$city = $row1['city'];
}

1 query instead of 2. Visible increase on performance. Works so much faster.

Thank you Guys.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If the city and area are the same, then that should be a condition in the query:

SELECT distinct city
FROM postcodedb
WHERE web = 'ALR' and city = area
ORDER BY city;

Ideally, there would be no duplicates, so you can eliminate the distinct:

SELECT city
FROM postcodedb
WHERE web = 'ALR' and city = area
ORDER BY city;

And for this query you would want an index on postcodedb(web, city, area) for optimal performance.

Upvotes: 1

Related Questions