Forum PHP.pl > Skomplikowane (?) zapytanie

Pomoc - Szukaj - Użytkownicy - Kalendarz

Pełna wersja: Skomplikowane (?) zapytanie

Forum PHP.pl > Forum > Bazy danych > MySQL

ShaXbee

9.12.2004, 22:28:37

Jeżeli ktoś jest zainteresowany optymalizacją zapytania to proszę o pomoc.

Co to jest?

Baza providerów, operujących na terenie miast i poszczególnych ulic.

Dane:

$province_id - identyfikator województwa
$provider_id - identyfikator dostawcy

Tablica miast (cities) z polami:

id - unikalny identyfikator miasta
province_id - identyfikator województwa w którym się znajduje miasto
name - nazwa miasta
providers - lista id providerów dostarczających usługi na terenie całego miasta
sub_providers - lista id providerów dostarczających usługi na terenie określonych w innej tabeli ulic danego miasta

Wynik zapytania:

Dla podanego providera znaleźć miasta w których dostarcza on usługi.

Pola:

id
name
provider - równe 1 jeśli dany provider jest na liście w polu cities.providers
sub_provider - równe 1 jeśli provider jest na liście w polu cities.sub_providers

Miasta nie mogą się dublować - i tu mam problem - nie potrafię wyeliminować z poziomu MySQL duplikatów.

Zapytanie:

Zmienna $regexp jest ustawiana następująco:

[PHP] pobierz, plaintext 
<?php
$regexp = &#092;"^\" . $provider_id . \",|,\" . $provider_id . \",|,\" . $provider_id . \"$|^\" . $provider_id . \"$\";
?>
[PHP] pobierz, plaintext

No i samo zapytanie

[SQL] pobierz, plaintext 
(
SELECT *, 1 AS provider, 0 AS sub_provider
FROM cities WHERE province_id=$province_id AND providers REGEXP '$regexp' AND sub_providers NOT REGEXP '$regexp'
)
UNION
(
SELECT *, 0 AS provider, 1 AS sub_provider
FROM cities WHERE province_id=$province_id AND providers NOT REGEXP '$regexp' AND sub_providers REGEXP '$regexp'
)
UNION
(
SELECT *, 1 AS provider, 1 AS sub_provider
FROM cities WHERE province_id=$province_id AND providers REGEXP '$regexp' AND sub_providers REGEXP '$regexp'
)
ORDER BY name
[SQL] pobierz, plaintext

EDIT: Dołączam rozwiązanie w php

[PHP] pobierz, plaintext 
<?php
 
	$regexp = &#092;"^\" . $provider_id . \",|,\" . $provider_id . \",|,\" . $provider_id . \"$|^\" . $provider_id . \"$\";
 
	$query =
	&#092;"(\" .
	&#092;"SELECT * \" .
	&#092;"FROM cities \" .
	&#092;"WHERE \" .
	  &#092;"province_id=\" . $this->province_id . \" AND \" .
	  &#092;"providers REGEXP '\" . $regexp . \"' \" .
	&#092;")\" .
	&#092;" UNION \" .
	&#092;"(\" .
	&#092;"SELECT * \" .
	&#092;"FROM cities \" .
	&#092;"WHERE \" .
	  &#092;"province_id=\" . $this->province_id . \" AND \" .
	  &#092;"sub_providers REGEXP '\" . $regexp . \"'\" .
	&#092;")\" .
	&#092;"ORDER BY name\";
 
	$result = mysql_query($query);
 
	while($row = mysql_fetch_array($result)){
 
	 if(in_array($provider_id, explode(&#092;",\", $row[\"providers\"]))){ $row[\"city_provider\"] = TRUE; } else { $row[\"city_provider\"] = FALSE; };
	 if(in_array($provider_id, explode(&#092;",\", $row[\"sub_providers\"]))){ $row[\"sub_provider\"] = TRUE; } else { $row[\"sub_provider\"] = FALSE; };
 
	 $items[] = $row;
 
	};
 
?>
[PHP] pobierz, plaintext

DeyV

9.12.2004, 22:53:23

Kod

SELECT *,
IF( providers REGEXP '$regexp' , 1, 0 )  AS provider,
IF( sub_providers REGEXP '$regexp' , 1, 0 ) AS sub_provider
FROM cities
WHERE province_id=$province_id AND
(
    (
   providers REGEXP '$regexp' AND sub_providers NOT REGEXP '$regexp'
    ) or (
  providers NOT REGEXP '$regexp' AND sub_providers REGEXP '$regexp'
    ) or (
  providers REGEXP '$regexp' AND sub_providers REGEXP '$regexp'
    )
)
ORDER BY name

Tylko musisz się zastanowić, czy w tym zapytaniu potrzebne jest takie dokładne uwarunkowywanie w WHERE
(oczywiście moga być jakieś literówki

)

ShaXbee

9.12.2004, 22:58:43

Zapomniałem wspomnieć o jednej rzeczy

Ważnej rzeczy MySQL < 5.0.0

Mimo wszystko dzięki za odzew.

BTW instrukcja IF bardzo mi się przyda :]

DeyV

9.12.2004, 23:01:21

ale jaką ja wykorzystałem funkcje, dostępna tylko pod MYSQL 5 ?
Wydawało mi się, że moje zapytanie jest nawet bardziej kompatybilne z starszymi wersjami MySQL (bo nie używa UNION, które w MySQL jest dosyć nowym wynalazkiem)...

ShaXbee

13.12.2004, 19:52:42

Cytuje z dokumentacji MySQL

Cytat

An OR using a single key is well optimized, as is the handling of AND.

The one tricky case is that of searching on two different keys combined with OR:

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'

This case is optimized from MySQL 5.0.0. See section 7.2.6 Index Merge Optimization.

In MySQL 4.0 and up, you can also solve the problem efficiently by using a UNION that combines the output of two separate SELECT statements. See section 13.1.7.2 UNION Syntax.

Link: http://dev.mysql.com/doc/mysql/en/Searching_on_two_keys.html

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.