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.
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.
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