Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Mon, 2008-02-04 at 01:08 +0100, Jochem Maas wrote: > the column spec. what kind of geomtery column is it? and A geographical "geometry" i.e. a projected data set of Geo data. > are you using it as a primary key? or some else No, the integer gid is usually a primary key still. The geo data is indexed though. > ... if so what kind AFAIK it is a modified bTree index - but I will have to look closer to give you a more correct answer. > of stuff are you storing in there? also in what way are you > using it that gives you such a speed boost with queries? > Simply an index that is specific to a geometry. > I read the mysql docs, I understand the principles but I'm having > a hard time figuring out how to apply in practice in terms of > making use of the performance gains you mentione ... specifically > in a web environment where heavy queries are often along the lines > of paginated data combined with user defined filters (e.g. a product > list sorted by price, etc and filter on type/category/keyword/etc) > The web environment that we are talking about here is not really all that webby ;) It is more of a web frontend to a _very_ powerful database system... > sorry if I'm sounding like a idiot :-) > Not at all, how do we learn if we don't ask questions? There is no such thing as a stupid question, only stupid answers... --Paul All Email originating from UWC is covered by disclaimer http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Tue, 2008-02-05 at 11:53 -0600, Richard Lynch wrote: > This is actually pretty OT for the PHP list itself, really, unless you > want to try to implement GIS in PHP, which would be a particularly Bad > Idea (tm) due to the scale, scope, and calculations involved. > Err, sorry, but I must correct you there. PHP is used as one of the more common GIS (Web mapping at least) languages. I use it extensively with the UMN Mapserver (mapserver.gis.umn.edu) as PHP/Mapscript which you compile into PHP as an extension to access your map data. The OT part comes in with the actual guts of the db transaction, and the AJAX based interfaces that everyone insists on these days. PHP in this case is an extremely important aspect of web mapping, and I have even used PHP to interface to GRASS and other GIS apps (OK now _this_ is starting to be a Bad Idea(TM)). Some of the largest web mapping and scientific apps run off PHP/Mapscript (closest competitor is PythonMapscript). Just to let you know... --Paul All Email originating from UWC is covered by disclaimer http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Sun, February 3, 2008 6:08 pm, Jochem Maas wrote: > of paginated data combined with user defined filters (e.g. a product > list sorted by price, etc and filter on type/category/keyword/etc) If you want GIS to be useful for that, you'd have to pretend that something like "type" was, say, longitude, and "category" was latitude and keyword was height, and then convert back and forth and... If you can create ONE index in MySQL that contains ALL your WHERE and ORDER BY fields, then you are golden. MySQL can only use ONE index per query. If your query uses WHERE clauses and/or ORDER BY clauses of fields that are NOT in your index, then it will be "slow" create index shopping_index on shopping(type, category, keyword, etc); is probably going to be far more maintainable and provide a similar performance boost. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Fri, February 1, 2008 3:55 am, Paul Scott wrote:
>
> Did anyone actually get this mail?
>
> More concrete example? What would you like to see?
>
> I suspect that some of my mail is getting dropped :(
I got the email below, and it made perfect sense...
Though I am not sure it would make sense to somebody who hasn't used
GIS before, or at least read about it...
Ultimately, what you do is have a NEW datatype to play with called a
'geometry' or a 'gis' or whatever the SQL guys chose to call it.
You also have ways to create "points" or "lines" or "polygons" etc,
which are sub-types of geometries.
And, finally, you can put an index on one of these nifty 'geometry'
columns, and it uses a btree to sort the data based on rules that make
sense for the underlying 'geometry' data.
For a concrete example, you might do something like this:
create table store (
store_id int(11) autoincrement unique not null primary key,
address text,
city varchar(255),
state char(2),
postal varchar(15),
longlat gis
);
create index store_gis_index on store(longloat);
inset into store(city, state, postal, longlat)
values ('Chicago', 'IL', '60601', GisLongLat(41.885844, -87.618128));
As far as you're concerned, it just goes really fast by "magic" when
you try to look something up by being "close to" a given LongLat.
Somewhere down in the guts of the DB, there is something to order the
data by both long and lat in a btree to find things quickly that are
"near" each other.
This is actually pretty OT for the PHP list itself, really, unless you
want to try to implement GIS in PHP, which would be a particularly Bad
Idea (tm) due to the scale, scope, and calculations involved.
Perhaps a Postgres GIS list or the MySQL list would be of more use, or
a Geography/Cartography database list.
You could probably achieve a SIMILAR performance level for something
as simple as long lat with:
create table store (
/* same columns as before */
long float,
lat float
);
create index store_long_lat_index on store(long, lat);
It wouldn't be QUITE as efficient, perhaps, as the ordering of the
btree for a GIS column might put more weight on the "lat" part
somehow, rather than just plain sorting by long, and then by lat...
But you have to have an AWFULLY large index for that to matter, since
the depth of the btree the same size, and only for specific clumped
data will it make any difference, I think.
Disclaimer:
I am NOT an expert on this stuff, but I have messed with it a tiny
bit, and this is my best understanding from my research so far.
>
> --Paul
>
> On Fri, 2008-02-01 at 06:33 +0200, Paul Scott wrote:
>> On Fri, 2008-02-01 at 03:40 +0100, Jochem Maas wrote:
>>
>> > I for one would really like to see a concrete example of this kind
>> of
>> > use of geometry columns and spacial indexes as an alternative to
>> the stand
>> > integer based primary keys.
>>
>>
>> On one of my local postGIS tables:
>>
>> CREATE INDEX k1
>> ON kanagawa
>> USING gist
>> (the_geom);
>>
>>
>> A gist index is a GEOS based spatial index. You will need GEOS to
>> create
>> one.
>>
>> When loading spatial data, your geometry column looks like so:
>>
>> 01050001000102000C0011ECE564CF7561404A8999CCDABC4140E5C0981ACE75614012901CD641BD4140603C8386BE756140E525611B40BD41405BF216D3BD756140151DC9E53FBD414054DC1A4DBD756140760B997A3FBD414012219BD1BC756140D20823E33EBD41407AB2884EBC7561400F2110243EBD41404571B4D0BB756140CC0C6A213DBD4140F707192ABB7561405DF2A1803CBD4140F0F11CA4BA756140C3D1B7413CBD4140E89CB2ADB97561406F046D233CBD414017D4B7CCA97561406D47AD7F39BD4140
>>
>> Which is WKB (Well Known Binary) data or WKT (Well Known Text) data.
>> The
>> gist index simply indexes this as opposed to the regular gid (which
>> you
>> still use btree indexes on anyways)
>>
>> --Paul
>>
>> All Email originating from UWC is covered by disclaimer
>> http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
> --
> .
> | Chisimba PHP5 Framework - http://avoir.uwc.ac.za |
> ::
>
> All Email originating from UWC is covered by disclaimer
> http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Paul Scott schreef: Did anyone actually get this mail? it came through :-) More concrete example? What would you like to see? the column spec. what kind of geomtery column is it? and are you using it as a primary key? or some else ... if so what kind of stuff are you storing in there? also in what way are you using it that gives you such a speed boost with queries? I read the mysql docs, I understand the principles but I'm having a hard time figuring out how to apply in practice in terms of making use of the performance gains you mentione ... specifically in a web environment where heavy queries are often along the lines of paginated data combined with user defined filters (e.g. a product list sorted by price, etc and filter on type/category/keyword/etc) sorry if I'm sounding like a idiot :-) I suspect that some of my mail is getting dropped :( --Paul On Fri, 2008-02-01 at 06:33 +0200, Paul Scott wrote: On Fri, 2008-02-01 at 03:40 +0100, Jochem Maas wrote: I for one would really like to see a concrete example of this kind of use of geometry columns and spacial indexes as an alternative to the stand integer based primary keys. On one of my local postGIS tables: CREATE INDEX k1 ON kanagawa USING gist (the_geom); A gist index is a GEOS based spatial index. You will need GEOS to create one. When loading spatial data, your geometry column looks like so: 01050001000102000C0011ECE564CF7561404A8999CCDABC4140E5C0981ACE75614012901CD641BD4140603C8386BE756140E525611B40BD41405BF216D3BD756140151DC9E53FBD414054DC1A4DBD756140760B997A3FBD414012219BD1BC756140D20823E33EBD41407AB2884EBC7561400F2110243EBD41404571B4D0BB756140CC0C6A213DBD4140F707192ABB7561405DF2A1803CBD4140F0F11CA4BA756140C3D1B7413CBD4140E89CB2ADB97561406F046D233CBD414017D4B7CCA97561406D47AD7F39BD4140 Which is WKB (Well Known Binary) data or WKT (Well Known Text) data. The gist index simply indexes this as opposed to the regular gid (which you still use btree indexes on anyways) --Paul All Email originating from UWC is covered by disclaimer http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php All Email originating from UWC is covered by disclaimer http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Did anyone actually get this mail? More concrete example? What would you like to see? I suspect that some of my mail is getting dropped :( --Paul On Fri, 2008-02-01 at 06:33 +0200, Paul Scott wrote: > On Fri, 2008-02-01 at 03:40 +0100, Jochem Maas wrote: > > > I for one would really like to see a concrete example of this kind of > > use of geometry columns and spacial indexes as an alternative to the stand > > integer based primary keys. > > > On one of my local postGIS tables: > > CREATE INDEX k1 > ON kanagawa > USING gist > (the_geom); > > > A gist index is a GEOS based spatial index. You will need GEOS to create > one. > > When loading spatial data, your geometry column looks like so: > > 01050001000102000C0011ECE564CF7561404A8999CCDABC4140E5C0981ACE75614012901CD641BD4140603C8386BE756140E525611B40BD41405BF216D3BD756140151DC9E53FBD414054DC1A4DBD756140760B997A3FBD414012219BD1BC756140D20823E33EBD41407AB2884EBC7561400F2110243EBD41404571B4D0BB756140CC0C6A213DBD4140F707192ABB7561405DF2A1803CBD4140F0F11CA4BA756140C3D1B7413CBD4140E89CB2ADB97561406F046D233CBD414017D4B7CCA97561406D47AD7F39BD4140 > > Which is WKB (Well Known Binary) data or WKT (Well Known Text) data. The > gist index simply indexes this as opposed to the regular gid (which you > still use btree indexes on anyways) > > --Paul > > All Email originating from UWC is covered by disclaimer > http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- . | Chisimba PHP5 Framework - http://avoir.uwc.ac.za | :: All Email originating from UWC is covered by disclaimer http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
2008. 02. 1, péntek keltezéssel 11.55-kor Paul Scott ezt írta: > Did anyone actually get this mail? > > More concrete example? What would you like to see? > > I suspect that some of my mail is getting dropped :( sure I got it but answered before I reached it in reading. sorry for the noise. as for the GEOS stuff I think I'll do some research on it, it sounds very interesting greets Zoltán Németh > > --Paul > > On Fri, 2008-02-01 at 06:33 +0200, Paul Scott wrote: > > On Fri, 2008-02-01 at 03:40 +0100, Jochem Maas wrote: > > > > > I for one would really like to see a concrete example of this kind of > > > use of geometry columns and spacial indexes as an alternative to the stand > > > integer based primary keys. > > > > > > On one of my local postGIS tables: > > > > CREATE INDEX k1 > > ON kanagawa > > USING gist > > (the_geom); > > > > > > A gist index is a GEOS based spatial index. You will need GEOS to create > > one. > > > > When loading spatial data, your geometry column looks like so: > > > > 01050001000102000C0011ECE564CF7561404A8999CCDABC4140E5C0981ACE75614012901CD641BD4140603C8386BE756140E525611B40BD41405BF216D3BD756140151DC9E53FBD414054DC1A4DBD756140760B997A3FBD414012219BD1BC756140D20823E33EBD41407AB2884EBC7561400F2110243EBD41404571B4D0BB756140CC0C6A213DBD4140F707192ABB7561405DF2A1803CBD4140F0F11CA4BA756140C3D1B7413CBD4140E89CB2ADB97561406F046D233CBD414017D4B7CCA97561406D47AD7F39BD4140 > > > > Which is WKB (Well Known Binary) data or WKT (Well Known Text) data. The > > gist index simply indexes this as opposed to the regular gid (which you > > still use btree indexes on anyways) > > > > --Paul > > > > All Email originating from UWC is covered by disclaimer > > http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > All Email originating from UWC is covered by disclaimer > http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
2008. 02. 1, péntek keltezéssel 03.40-kor Jochem Maas ezt írta: > Per Jessen schreef: > > Richard Lynch wrote: > > > >>> OK, what is a 'geometry column' and what is a 'spatial index' ? > >> Imagine a single column combining both longitude and latitude. > >> > >> Now imagine an index that "knows" about long/lat, and keeps > >> geographically "close" objects sorted in the index for you. > >> > >> Including "knowing" about the 180 <-> -180 degree wrap-around. > >> (Or 360 === 0 wrap-around in the other geo-system.) > >> > >> So when you ask for "theme parks near Zurich" your DB can answer in > >> milliseconds instead of minutes. > > > > Thanks Richard - I thought Nathan was talking about an abstract concept, > > not something "real". > > > > So, back the Nathans suggestion: > > > >> Back on the mysql side of things, try using geometry columns rather > >> than numerical primary keys, with spatial indexes.. it's a MASSIVE > >> performance upgrade (I've cut 5 second queries down to 0.005 by > >> using geo columns) > > > > Is this worth a try? Have others tried this? > > I for one would really like to see a concrete example of this kind of > use of geometry columns and spacial indexes as an alternative to the stand > integer based primary keys. me too greets Zoltán Németh > > > > > > > /Per Jessen, Zürich > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Fri, 2008-02-01 at 03:40 +0100, Jochem Maas wrote: > I for one would really like to see a concrete example of this kind of > use of geometry columns and spacial indexes as an alternative to the stand > integer based primary keys. On one of my local postGIS tables: CREATE INDEX k1 ON kanagawa USING gist (the_geom); A gist index is a GEOS based spatial index. You will need GEOS to create one. When loading spatial data, your geometry column looks like so: 01050001000102000C0011ECE564CF7561404A8999CCDABC4140E5C0981ACE75614012901CD641BD4140603C8386BE756140E525611B40BD41405BF216D3BD756140151DC9E53FBD414054DC1A4DBD756140760B997A3FBD414012219BD1BC756140D20823E33EBD41407AB2884EBC7561400F2110243EBD41404571B4D0BB756140CC0C6A213DBD4140F707192ABB7561405DF2A1803CBD4140F0F11CA4BA756140C3D1B7413CBD4140E89CB2ADB97561406F046D233CBD414017D4B7CCA97561406D47AD7F39BD4140 Which is WKB (Well Known Binary) data or WKT (Well Known Text) data. The gist index simply indexes this as opposed to the regular gid (which you still use btree indexes on anyways) --Paul All Email originating from UWC is covered by disclaimer http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Per Jessen schreef: Richard Lynch wrote: OK, what is a 'geometry column' and what is a 'spatial index' ? Imagine a single column combining both longitude and latitude. Now imagine an index that "knows" about long/lat, and keeps geographically "close" objects sorted in the index for you. Including "knowing" about the 180 <-> -180 degree wrap-around. (Or 360 === 0 wrap-around in the other geo-system.) So when you ask for "theme parks near Zurich" your DB can answer in milliseconds instead of minutes. Thanks Richard - I thought Nathan was talking about an abstract concept, not something "real". So, back the Nathans suggestion: Back on the mysql side of things, try using geometry columns rather than numerical primary keys, with spatial indexes.. it's a MASSIVE performance upgrade (I've cut 5 second queries down to 0.005 by using geo columns) Is this worth a try? Have others tried this? I for one would really like to see a concrete example of this kind of use of geometry columns and spacial indexes as an alternative to the stand integer based primary keys. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Richard Lynch wrote: >> >> OK, what is a 'geometry column' and what is a 'spatial index' ? > > Imagine a single column combining both longitude and latitude. > > Now imagine an index that "knows" about long/lat, and keeps > geographically "close" objects sorted in the index for you. > > Including "knowing" about the 180 <-> -180 degree wrap-around. > (Or 360 === 0 wrap-around in the other geo-system.) > > So when you ask for "theme parks near Zurich" your DB can answer in > milliseconds instead of minutes. Thanks Richard - I thought Nathan was talking about an abstract concept, not something "real". So, back the Nathans suggestion: > Back on the mysql side of things, try using geometry columns rather > than numerical primary keys, with spatial indexes.. it's a MASSIVE > performance upgrade (I've cut 5 second queries down to 0.005 by > using geo columns) Is this worth a try? Have others tried this? /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Mon, January 28, 2008 12:37 pm, Per Jessen wrote: > Richard Lynch wrote: > >> On Sat, January 26, 2008 3:45 am, Per Jessen wrote: >>> Nathan Rixham wrote: >>> Back on the mysql side of things, try using geometry columns rather than numerical primary keys, with spatial indexes.. it's a MASSIVE performance upgrade (I've cut 5 second queries down to 0.005 by using geo columns) >>> >>> Uh, could you could elaborate a bit on that (whilst I go and do >>> some >>> googling)? >> >> If you are doing geography/geometry stuff, spatial indices can be >> nice. >> > > OK, what is a 'geometry column' and what is a 'spatial index' ? Imagine a single column combining both longitude and latitude. Now imagine an index that "knows" about long/lat, and keeps geographically "close" objects sorted in the index for you. Including "knowing" about the 180 <-> -180 degree wrap-around. (Or 360 === 0 wrap-around in the other geo-system.) So when you ask for "theme parks near Zurich" your DB can answer in milliseconds instead of minutes. It's a bit more complex than that, as a "geometry" can be much more than just long/lat, and space could be 3-D or even N-space, but that's the simple version to light up the bulb. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Mon, 2008-01-28 at 12:29 -0600, Richard Lynch wrote: > >>Back on the mysql side of things, try using geometry columns rather > >>than numerical primary keys, with spatial indexes.. it's a MASSIVE > >>performance upgrade (I've cut 5 second queries down to 0.005 by using > >>geo columns) > > > > Uh, could you could elaborate a bit on that (whilst I go and do some > > googling)? > > If you are doing geography/geometry stuff, spatial indices can be nice. > > I suspect that massive performance upgrade just came from having what > is essentially a multi-field index, because MySQL can only use *ONE* > index per query. Yeah, for sure! You will need to use GEOS to index those rows. Also, if your database is getting large, consider switching to PostGIS on Postgres, as opposed to MySQL. The MySQL spatial extension is still quite new and immature compared to PostGIS. I routinely create and manage databases of over 10 million rows of spatial data on PostGIS, and have never needed to look further. --Paul All Email originating from UWC is covered by disclaimer http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Richard Lynch wrote: > On Sat, January 26, 2008 3:45 am, Per Jessen wrote: >> Nathan Rixham wrote: >> >>>Back on the mysql side of things, try using geometry columns rather >>>than numerical primary keys, with spatial indexes.. it's a MASSIVE >>>performance upgrade (I've cut 5 second queries down to 0.005 by using >>>geo columns) >> >> Uh, could you could elaborate a bit on that (whilst I go and do some >> googling)? > > If you are doing geography/geometry stuff, spatial indices can be > nice. > OK, what is a 'geometry column' and what is a 'spatial index' ? /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Richard Lynch wrote: > When you create a cursor, it's like a handle to a running query in the > background process, and returns immediately. > Create three cursors, and you SHOULD have them running in parallel. Ah, I see. Hmm, interesting idea. Although it won't be much good as I'm not actually fetching a lot of rows. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Sat, January 26, 2008 3:45 am, Per Jessen wrote: > Nathan Rixham wrote: > >> I posted you a short script on this thread at 04:07 GMT today >> that'll >> get you multithreading (via cli) - but even then you can shell exec >> that cli script from apache.. > > Yeah, I noticed, thanks. The thing is - once pcntl_fork() is added to > the mix, it's getting a little too complicated for what was otherwise > a > simple php web-application. I'm thinking I'm probably better off with > my iframe-rpc stuff. > >>Back on the mysql side of things, try using geometry columns rather >>than numerical primary keys, with spatial indexes.. it's a MASSIVE >>performance upgrade (I've cut 5 second queries down to 0.005 by using >>geo columns) > > Uh, could you could elaborate a bit on that (whilst I go and do some > googling)? If you are doing geography/geometry stuff, spatial indices can be nice. I suspect that massive performance upgrade just came from having what is essentially a multi-field index, because MySQL can only use *ONE* index per query. If your where and/or order by force MySQL to pick among several possible indices, it can get ugly fast. If you have an index with all the data it needs for both where and order by clauses, it will (usually) pick the right one, and have USEFUL indices, instead of not-so-useful indices. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Sat, January 26, 2008 3:17 am, Per Jessen wrote: > Richard Lynch wrote: > >> On Fri, January 25, 2008 3:35 am, Per Jessen wrote: >>> I have a website where some of the pages require several mysql >>> queries - >>> they're independent, so in principle they could easily be run in >>> parallel. Has anyone looked at doing that? >> >> If MySQL has implemented "cursors" in some new version, you have >> just >> described a "cursor"... >> >> There may be something else in MySQL to do this, mind you, but >> that's >> the easiest way I know of in other DBs... > > cursors exist in mysql too, but I don't quite see how I could use them > to run two queries concurrently? Also, these queries don't return a > lot of data. When you create a cursor, it's like a handle to a running query in the background process, and returns immediately. Create three cursors, and you SHOULD have them running in parallel. The overhead of a cursor may be more than you save for fast queries though... Or maybe not, as a cursor should not have THAT much overhead. Only used them in PostgreSQL and SQL Server/Sybase, though, so don't know for sure about MySQl. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Nathan Rixham wrote: > I posted you a short script on this thread at 04:07 GMT today that'll > get you multithreading (via cli) - but even then you can shell exec > that cli script from apache.. Yeah, I noticed, thanks. The thing is - once pcntl_fork() is added to the mix, it's getting a little too complicated for what was otherwise a simple php web-application. I'm thinking I'm probably better off with my iframe-rpc stuff. >Back on the mysql side of things, try using geometry columns rather >than numerical primary keys, with spatial indexes.. it's a MASSIVE >performance upgrade (I've cut 5 second queries down to 0.005 by using >geo columns) Uh, could you could elaborate a bit on that (whilst I go and do some googling)? /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
I posted you a short script on this thread at 04:07 GMT today that'll get you multithreading (via cli) - but even then you can shell exec that cli script from apache.. Per Jessen wrote: Richard Lynch wrote: On Fri, January 25, 2008 3:35 am, Per Jessen wrote: I have a website where some of the pages require several mysql queries - they're independent, so in principle they could easily be run in parallel. Has anyone looked at doing that? If MySQL has implemented "cursors" in some new version, you have just described a "cursor"... There may be something else in MySQL to do this, mind you, but that's the easiest way I know of in other DBs... cursors exist in mysql too, but I don't quite see how I could use them to run two queries concurrently? Also, these queries don't return a lot of data. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
Richard Lynch wrote: > On Fri, January 25, 2008 3:35 am, Per Jessen wrote: >> I have a website where some of the pages require several mysql >> queries - >> they're independent, so in principle they could easily be run in >> parallel. Has anyone looked at doing that? > > If MySQL has implemented "cursors" in some new version, you have just > described a "cursor"... > > There may be something else in MySQL to do this, mind you, but that's > the easiest way I know of in other DBs... cursors exist in mysql too, but I don't quite see how I could use them to run two queries concurrently? Also, these queries don't return a lot of data. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how dod you get to do multiple mysql queries concurrently?
On Fri, January 25, 2008 3:35 am, Per Jessen wrote: > I have a website where some of the pages require several mysql queries > - > they're independent, so in principle they could easily be run in > parallel. Has anyone looked at doing that? If MySQL has implemented "cursors" in some new version, you have just described a "cursor"... There may be something else in MySQL to do this, mind you, but that's the easiest way I know of in other DBs... I suppose you could try with a shell script and pipes and files and using "&" with exec() but any savings from MySQL would probably be dwarfed by the exec() call... Are the queries actually SLOW and killing your site, or are you just performance-wanking? -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] how dod you get to do multiple mysql queries concurrently?
All, I have a website where some of the pages require several mysql queries - they're independent, so in principle they could easily be run in parallel. Has anyone looked at doing that? /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
