:strip_icc():strip_exif()/u/62639/tm.jpg?f=community)
Ik heb een tabel met user-data en een tabel met postcode-ranges die per range de streeknaam en (zelfverzonnen) regio aangeven.
de tabel met user-data bestaat o.a. uit een primary key `id` en een postcode veld die overal bestaat uit 4cijfers gevolgd door een spatie en twee letters (of is helemaal leeg).
dump van de tabel met postcodes:
De bedoeling is om nu aan de hand van de postcode de regio te bepalen van een gebruiker. Dat is opzich natuurlijk een eitje, maar wat niet wil lukken is om dit een beetje efficient te doen.
Het meest efficiente wat ik tot nu toe heb is dit:
(range is een kolom die ik extra heb aangemaakt om alleen de cijfers van de postcode in op te slaan, en van het type SMALLINT)
Die laatste WHERE is het snelste, die 'between'-where lijkt ietsje langzamer en die substring-where is echt een stuk langzamer.
Maar, die laatste WHERE is nog steeds te langzaam.
Als ik een explain doe dan blijkt ook dat ie alle 164 rows van de postcodes tabel beschouwd
explain waarbij er een index op de kolom dm.range staat:
(wat overigens gek is, is dat als ik op dm.range een index zet, dit een positief effect heeft op de 'between'-where, maar een negatief effect op de laatste where
)
explain zonder die index en met een BETWEEN:
En hij duurt ongeveer 2 seconden. Dat is echt onacceptabel, zeker gezien dit princiepe in veel ingewikkelder queries gebruikt moet gaan worden en het verschil dus alleen maar toe zal nemen.
Hij mag eigenlijk maar een paar honderste van een seconde duren ... en dat zal waarschijnlijk ook wel lukken als mysql in de gaten heeft dat die ranges uniek zijn en er dus maar 1 row in die postcode tabel op gejoined hoeft te worden. Hij heeft volgens de explain ook wel in de gaten dat ie die range-key moet nemen in de postcode tabel, maar heeft vervolgens niet in de gaten dat ie niet alle rows hoeft te checken
Iemand enig idee hoe ik dit mysql duidelijk maak?
overigens zoek ik eigenlijk uiteindelijk naar een oplossing waarbij ik die dm.range kolom niet nodig heb en het dus met een SUBSTRING op de postcodes af kan.
En een referentie naar de primary key van de postcodes-tabel opslaan in de user-tabel vind ik ook geen oplossing, omdat ik dan na iedere update query nog eens een aparte update query moet gaan doen om die pk-referentie bij te werken (mysql-versie laat geen updates over meerdere kolommen toe)
de tabel met user-data bestaat o.a. uit een primary key `id` en een postcode veld die overal bestaat uit 4cijfers gevolgd door een spatie en twee letters (of is helemaal leeg).
dump van de tabel met postcodes:
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| CREATE TABLE postcodes (
postcode_id tinyint(3) unsigned NOT NULL auto_increment,
range_from smallint(5) unsigned default NULL,
range_till smallint(5) unsigned default NULL,
regio tinyint(3) unsigned default NULL,
streek char(25) default NULL,
PRIMARY KEY (postcode_id),
UNIQUE KEY range (range_from,range_till),
UNIQUE KEY postcode_id (postcode_id),
KEY rf (range_from),
KEY rt (range_till),
KEY regio (regio)
) TYPE=MyISAM; |
De bedoeling is om nu aan de hand van de postcode de regio te bepalen van een gebruiker. Dat is opzich natuurlijk een eitje, maar wat niet wil lukken is om dit een beetje efficient te doen.
Het meest efficiente wat ik tot nu toe heb is dit:
MySQL:
1
2
3
4
5
6
| SELECT COUNT(*) FROM data_main AS dm, postcodes AS p #WHERE SUBSTRING(dm.postcode, 1, 4) BETWEEN p.range_from AND p.range_till #WHERE dm.range BETWEEN p.range_from AND p.range_till WHERE dm.range >= p.range_from AND dm.range <= p.range_till |
(range is een kolom die ik extra heb aangemaakt om alleen de cijfers van de postcode in op te slaan, en van het type SMALLINT)
Die laatste WHERE is het snelste, die 'between'-where lijkt ietsje langzamer en die substring-where is echt een stuk langzamer.
Maar, die laatste WHERE is nog steeds te langzaam.
Als ik een explain doe dan blijkt ook dat ie alle 164 rows van de postcodes tabel beschouwd
explain waarbij er een index op de kolom dm.range staat:
MySQL:
1
2
3
4
5
6
7
| explain SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range >= p.range_from AND dm.range <= p.range_till table type possible_keys key key_len ref rows Extra p index range,rf,rt range 6 NULL 164 Using index dm ALL range NULL NULL NULL 13943 range checked for each record (index map: 32) |
(wat overigens gek is, is dat als ik op dm.range een index zet, dit een positief effect heeft op de 'between'-where, maar een negatief effect op de laatste where
)explain zonder die index en met een BETWEEN:
code:
1
2
3
4
5
6
7
| explain SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range BETWEEN p.range_from AND p.range_till table type possible_keys key key_len ref rows Extra p index NULL range 6 NULL 164 Using index dm ALL NULL NULL NULL NULL 13943 where used |
En hij duurt ongeveer 2 seconden. Dat is echt onacceptabel, zeker gezien dit princiepe in veel ingewikkelder queries gebruikt moet gaan worden en het verschil dus alleen maar toe zal nemen.
Hij mag eigenlijk maar een paar honderste van een seconde duren ... en dat zal waarschijnlijk ook wel lukken als mysql in de gaten heeft dat die ranges uniek zijn en er dus maar 1 row in die postcode tabel op gejoined hoeft te worden. Hij heeft volgens de explain ook wel in de gaten dat ie die range-key moet nemen in de postcode tabel, maar heeft vervolgens niet in de gaten dat ie niet alle rows hoeft te checken
Iemand enig idee hoe ik dit mysql duidelijk maak?
overigens zoek ik eigenlijk uiteindelijk naar een oplossing waarbij ik die dm.range kolom niet nodig heb en het dus met een SUBSTRING op de postcodes af kan.
En een referentie naar de primary key van de postcodes-tabel opslaan in de user-tabel vind ik ook geen oplossing, omdat ik dan na iedere update query nog eens een aparte update query moet gaan doen om die pk-referentie bij te werken (mysql-versie laat geen updates over meerdere kolommen toe)
/u/1830/acm.png?f=community)
:strip_exif()/u/3333/pumpkin_avatar.gif?f=community){kind=avatar}