Re: [PHP] Re: Sort with PHP or SQL?
ðÒÉ×ÅÔ!
Pekka Saarinen wrote:
> Also, many virtual hosts share MySQL server(s), so offering a choice to
> use PHP for pagination and sorting on "own server RAM/CPU" may by
> beneficial. I also believe that anything that produces less load to
> MySQL is good.
Absolutely true. First time a customer of mine wanted to host in the
States he got offered Oracle on dedicated machine. Looked quite good, as
usually having DB server on a dedicated machine performs *much* better.
Eventually, we found out that the yanks had *one* db server for
something like 50 overloaded webservers. The poor dbbox was actually
serving an incredible amount of MySql dbs, too.
So poor configuration must be taken into account. Especially when you
are hired into a project and cannot really choose who the provider is
going to be. In that case an oracle instance was *much* slower than a
PHP sort out of a plain text file. And we are talking about a ridicolous
amount of rows...
Everything may happen...
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Sort with PHP or SQL?
At 7/6/2002, you wrote: >SORT BY and ORDER by I mean LIMIT and ORDER BY of course Time for some more coffee. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Sort with PHP or SQL?
At 7/5/2002, you wrote: > >I'm working on a way to pull menus/pages from a database. Currently I have > >this working on 2 levels - Topic headers & actual page titles - & it prints > >it out fairly nice & doesn't take long to load. What I need to do is add a > >level between the 2, a sub-header. > > > >I'm running both the apache server & mysql server on the same linux box as > >the current load isn't very high. I'm wondering whether it would be > >better/easier to Load all the topic headers into an array, then any possible > >sub-headers into a 2nd array, then the pages into a 3rd array (or even into > >a multidimensional array?). Then go through the arrays & match the pages. > >Or would it be easier to write 1 huge SQL statement & have the SQL server > >run the whole query? > >*ANY* time you can sort/search in SQL, or you can do it in PHP, the SQL way >will be faster. > >I'm sure there's an exception to this rule, but I've never seen it :-) Hi, I have here a beta of my gallery software (normalized to fourth normal form and good use of indexes) where I did a user-selectable limit where you decide if pagination and sorting (SORT BY and ORDER by) are done by PHP or by MySQL. Especially ORDER BY is quite slow compared to other SQL commands and optimizing it may not always be easy: http://www.mysql.com/doc/O/R/ORDER_BY_optimisation.html In Apache/MySQL under Windows XP the difference is small result sets is quite big (e.g. 0.07s vs. 0.12s), and in Linux box it is much smaller but still noticeable. When result set gets bigger MySQL gains speed. The MySQL method also varies more in speed. So, offer a "treshold" value choice from "use always PHP" to "use always SLQ" and few steps in between, because there is no way of knowing which way works best in each server and image amount, without testing it. Also, many virtual hosts share MySQL server(s), so offering a choice to use PHP for pagination and sorting on "own server RAM/CPU" may by beneficial. I also believe that anything that produces less load to MySQL is good. This is just my experience: I believe in empirical research :) Pekka - Pekka Saarinen http://photography-on-the.net - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Sort with PHP or SQL?
On Sat, 6 Jul 2002, Patrick Teague wrote: > $query = "SELECT * " . > "FROM Headers LEFT JOIN Pages ON (Headers.Header_ID=Pages.Header_ID) " . > "ORDER BY HeaderOrder DESC, Header, PageOrder DESC, PageTitle;"; > > Any ideas on optimizing this? DB stuff doesn't come near the top of things > I'm good at :) The other problem is that any pages that end up having no > Header "disappear", which is a good thing on the menu, but a bad thing on > the admin pages when you're trying to connect all of them. SELECT from pages and use LEFT OUTER JOIN if you want to include pages with no header. miguel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Sort with PHP or SQL?
ðÒÉ×ÅÔ!
> Any suggestions on how to do this?
Yes,
1)
*DO* look around and find yourself some text about normalization. I
mean, paper. Something you can hold in your hands without rushing to
try-and-code it. Then get yourself a sigarette (if you are a smoker) and
spend two hours of your life making sure you perfectly understand what
data modeling is like. If you miss this step there is almost nothing
that people can do to help you.
2)
Look for a list that specializes in data-modeling. That's definitely OT
here. Not that I don't want to talk about it, but reading this list will
not give you much in that direction.
> Any ideas on optimizing this? DB stuff doesn't come near the top of things
> I'm good at :)
> The other problem is that any pages that end up having no
> Header "disappear", which is a good thing on the menu, but a bad thing on
> the admin pages when you're trying to connect all of them.
See point 1).
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
--
@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@-_=}{=_-@
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Sort with PHP or SQL?
> *ANY* time you can sort/search in SQL, or you can do it in PHP, the SQL way > will be faster. < and > > Grouping and sorting is something databases specialize in. Any suggestions on how to do this? Currently I have a table with Headers & another table with Pages... Should I create yet another table for Sub-Headers? The other thing I've thought of is putting everything in 1 table, but this seems slightly daunting. Here are the SQL statements I used to make the 2 tables I currently have - CREATE TABLE Headers ( Header_ID INT NOT NULL AUTO_INCREMENT, Header VARCHAR(25), HeaderOrder INT NOT NULL, HeaderInfo TEXT, PRIMARY KEY(Header_ID)); CREATE TABLE Pages ( Page_ID INT NOT NULL AUTO_INCREMENT, PageTitle VARCHAR(25), Header_ID INT NOT NULL, PageOrder INT NOT NULL, PageContent TEXT, PRIMARY KEY(Page_ID)); & then my current sql statement for php - $query = "SELECT * " . "FROM Headers LEFT JOIN Pages ON (Headers.Header_ID=Pages.Header_ID) " . "ORDER BY HeaderOrder DESC, Header, PageOrder DESC, PageTitle;"; Any ideas on optimizing this? DB stuff doesn't come near the top of things I'm good at :) The other problem is that any pages that end up having no Header "disappear", which is a good thing on the menu, but a bad thing on the admin pages when you're trying to connect all of them. Patrick -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
