Query Efficiency Help Please

[eluser]toopay[/eluser]
[quote author="aquary" date="1309337718"]Most of the time, I found it's much faster to run a complex query once than running many queries in a loop.[/quote]

WRONG! Not always like that. If you just joining 4 or 10 records, you maybe can say that. But beyond that, like hundred, thousand or more than 1000 records and you're out of luck.

If you use native SQL statement (not using CI AR!), and immediate free the result after do a query, the result will against you.

Well since you quoting my post, i fell reponsible to provide a fact here. See for yourself :

See below attachment, extract and dump into your test database. The dump file (.sql) contain a general city (5000 records) list and a country table contain +- 250 - 300 records

[continue to the next post]

[eluser]toopay[/eluser]
Continue with dump below sql file (part 2, which contain the rest 5000 records of cities ), then copy test.php in your controller folder, test it.

Both, cities and countries table, was indexed.

The result : Both, memory used and execution time benchmark, provide a fact that looping simple queries is always better (aprox 25-40 % !!) rather than perform JOIN statement.