? Would you like to explain why you say this?Kieran Huggins wrote:Saving db queries is overrated, in my opinion. Database connections are expensive, simple queries: not so much.
.
Pushing Query Results into an Array
Moderator: General Moderators
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
matthijs wrote:? Would you like to explain why you say this?Kieran Huggins wrote:Saving db queries is overrated, in my opinion. Database connections are expensive, simple queries: not so much.
.
From: http://wiki.dreamhost.com/index.php/KB_ ... _ConueriesDreamhost Wiki wrote:Why do connections count so much towards conueries?
Database connections use far more system resources than the average database query does. Because of this, we want to encourage people to make as few connections as possible with their code. So we "penalize" each connection by making it worth so many conueries. You can do a lot of things to cut down the number of connections you make with your code - it all depends on your particular application.
One of our customers averages over 1300 queries per connection! Thanks to the design of his code, his "conueries" are basically just queries.
Another customer averages just one query per connection. What they've done is basically turn their "conueries" into 25 times their queries. This uses up the monthly allotment of conueries a lot faster than necessary, so you should try to keep those connections open as long as possible. It'll also make your site faster in the process.
Remember, it's possible, through the use of peristent connections, to not have to make a new connection to your database even on a new page load!
Keep in mind that if PHP is running as CGI (PHP-CGI) and not as an Apache module, you will not be able to use persistent connections.
I trust them because it's their money on the line... that always tends to inspire a little homework. And they have enough users to make it worth a truckload (and they are profitable).
This is true, but irrelevant. We're talking about connecting to a database, getting some stuff, and disconnecting. The connection will only be built once whether the tree is built using a single parent_id system or a left/right/level system.Kieran Huggins wrote:Dreamhost Wiki wrote:Database connections use far more system resources than the average database query does.
In the single parent_id method there will be one query per item as it recurses through the tree finding any children.
With the left/right/level system there will be one query and that's it.
Imagine what happens if your tree has 500 items in it. Which solution is better? It's incredibly obvious that a little work to calculate the left and right branch ids occasionally (when the tree changes) saves a HUGE amount of processing and makes for much more scalable code. I'm always one for taking the path of least coding, but in this particular case it really is worthwhile doing a little extra legwork at the start to save yourself a huge headache if your tree expands beyond your expectations.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Ok, I basically understand what you guys are saying. But what 2 methods are we comparing exactly?
The first is pulling everything (select *) from the db and calculating/iterating the tree in PHP. The other is using the left/right system which is also used in the phpriot articles, in which you select what you need with a specialize query?
And which one is better (for which situation). I thought that it's almost always better to let the db do a little more work (with a more complicated query) instead of pulling everything out and calculating everything in php?
The first is pulling everything (select *) from the db and calculating/iterating the tree in PHP. The other is using the left/right system which is also used in the phpriot articles, in which you select what you need with a specialize query?
And which one is better (for which situation). I thought that it's almost always better to let the db do a little more work (with a more complicated query) instead of pulling everything out and calculating everything in php?
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Not exactly.. What I was talking about was: viewtopic.php?p=345897#345897
-
Dennishiding
- Forum Newbie
- Posts: 1
- Joined: Sat Jan 06, 2007 11:58 am
First off, thanks. I found this thread provided impetus for this C programmer by trade to see if he could toss together an example linked list in PHP/mysql.
I'm tentative to post much of my meager effort but here's the database structure I used.
I won't bore you with details of code, but I think you can see how this could be traversed to produce
a similar heirarchical list of links/pages.
But what are the advantages/disadvantages of this over the parent,nleft.nright structure?
They both require traversal of the entire tree to display the resulting page with a llist of links.
The firstchild,nextsibling table requires atomic update of 2 entries to move add or delete an item.
The parent,nleft,nright table seems to require an atomic traversal of the table and rebuild of the nleft and nright to add or delete an item.
If you wanted to display a subsection, the parent,nleft,nright tree might allow you to determine if the number of elements that are under a section are less than some set number and then make a bundled request for all those items at once. I suppose in a case where the user could collapse/expand subtrees, there might be some queries that could extract a subtree more easily with the parent,nleft,nright table.
Edit:
drat I think I found most of answer at http://www.phpriot.com/d/articles/php/a ... page6.html
I'm not sure that I see those searches are that much more efficient than ones I'd do with the child/sibling linklist approach though.
I'm tentative to post much of my meager effort but here's the database structure I used.
Code: Select all
mysql> select id, firstchild as child, nextsibling as next, name,url from list;
+----+-------+------+----------+----------------------------------------+
| id | child | next | name | url |
+----+-------+------+----------+----------------------------------------+
| 1 | 2 | NULL | mypage | NULL |
| 2 | 5 | 3 | section1 | NULL |
| 3 | 8 | 4 | section2 | NULL |
| 4 | 9 | NULL | section3 | NULL |
| 5 | NULL | 6 | google | http://www.google.com |
| 6 | NULL | 7 | Fidelity | http://www.401k.com |
| 7 | NULL | NULL | E*Trade | http://www.etrade.com |
| 8 | NULL | NULL | PHP | http://forums.devnetwork.net |
| 9 | NULL | 10 | manual | http://www.php.net/manual/en |
| 10 | NULL | NULL | mysql | http://dev.mysql.com/doc/refman/5.0/en |
+----+-------+------+----------+----------------------------------------+a similar heirarchical list of links/pages.
But what are the advantages/disadvantages of this over the parent,nleft.nright structure?
They both require traversal of the entire tree to display the resulting page with a llist of links.
The firstchild,nextsibling table requires atomic update of 2 entries to move add or delete an item.
The parent,nleft,nright table seems to require an atomic traversal of the table and rebuild of the nleft and nright to add or delete an item.
If you wanted to display a subsection, the parent,nleft,nright tree might allow you to determine if the number of elements that are under a section are less than some set number and then make a bundled request for all those items at once. I suppose in a case where the user could collapse/expand subtrees, there might be some queries that could extract a subtree more easily with the parent,nleft,nright table.
Edit:
drat I think I found most of answer at http://www.phpriot.com/d/articles/php/a ... page6.html
I'm not sure that I see those searches are that much more efficient than ones I'd do with the child/sibling linklist approach though.