Re: [PHP] help with making appropriate indexes.
On Friday 16 August 2002 18:05, lallous wrote: > But i'm not satisfied with your answer. This is not the appropriate list for your question in the first place. > I instinctly created indexes on > every field that I'm using w/ conditions. > But I feel that my script can be rewritten more efficiently using JOINTs or > other things. Possibly. The manual for your database should have tips on optimisation. > Any site with reading material regarding how to index and make complex > queries fast? Possibly the website for whatever DB you're using and its associated mailing-list? -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* It's Like This Even the samurai have teddy bears, and even the teddy bears get drunk. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] help with making appropriate indexes.
Thanks Miles. But i'm not satisfied with your answer. I instinctly created indexes on every field that I'm using w/ conditions. But I feel that my script can be rewritten more efficiently using JOINTs or other things. Any site with reading material regarding how to index and make complex queries fast? I use the 'table_' just to hide the original table names prefixes. Elias "Miles Thompson" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > General rule of thumb - create indexes on any fields which will be used in > the conditional part of a select statement, whether where or join. Since > you are working with tables, you can save yourself a lot of typing by > dropping the "table_" from the table names. After all, that's what they > are. (You can do this unless you've gone too far with your system.) > > Miles Thompson > > At 02:15 PM 8/14/2002 +0200, lallous wrote: > >give this query: > > > > SELECT > > table_routing.id AS routeid, > > table_pricing.units AS price, > > table_routing.providerAS providerid, > > table_country.nameAS countryname, > > table_country.id AS countrycode > > FROM > > table_pricing, > > table_routing, > > table_country > > WHERE > > table_country.id = table_routing.country > >AND > > table_routing.country = table_pricing.country > >AND > > table_routing.provider = table_pricing.provider > >AND > > table_country.enabled = 1 > > ORDER BY routeid > > > >what keys / indexes should I create on whatever tables to make this query > >efficient? > >I don't know if this query can be re-written even with JOINs and stuff. > > > >Please advise, > > > >If you've got an online doc. about that it will help too. > > > > > >Elias > > > > > > > >-- > >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] help with making appropriate indexes.
General rule of thumb - create indexes on any fields which will be used in the conditional part of a select statement, whether where or join. Since you are working with tables, you can save yourself a lot of typing by dropping the "table_" from the table names. After all, that's what they are. (You can do this unless you've gone too far with your system.) Miles Thompson At 02:15 PM 8/14/2002 +0200, lallous wrote: >give this query: > > SELECT > table_routing.id AS routeid, > table_pricing.units AS price, > table_routing.providerAS providerid, > table_country.nameAS countryname, > table_country.id AS countrycode > FROM > table_pricing, > table_routing, > table_country > WHERE > table_country.id = table_routing.country >AND > table_routing.country = table_pricing.country >AND > table_routing.provider = table_pricing.provider >AND > table_country.enabled = 1 > ORDER BY routeid > >what keys / indexes should I create on whatever tables to make this query >efficient? >I don't know if this query can be re-written even with JOINs and stuff. > >Please advise, > >If you've got an online doc. about that it will help too. > > >Elias > > > >-- >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
[PHP] help with making appropriate indexes.
give this query: SELECT table_routing.id AS routeid, table_pricing.units AS price, table_routing.providerAS providerid, table_country.nameAS countryname, table_country.id AS countrycode FROM table_pricing, table_routing, table_country WHERE table_country.id = table_routing.country AND table_routing.country = table_pricing.country AND table_routing.provider = table_pricing.provider AND table_country.enabled = 1 ORDER BY routeid what keys / indexes should I create on whatever tables to make this query efficient? I don't know if this query can be re-written even with JOINs and stuff. Please advise, If you've got an online doc. about that it will help too. Elias -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
