sql optimize me beautiful
category: general [glöplog]
Project Pouët Pi.
Knowing that altering the Pouet sql design at this stage would be a maintaining nightmare it's probably wiser (besides adding indexes and other smaller db tweaking) to go the way Poi said: The more you cache, the faster it gets.
Caching groups, parties, platforms etc. as PHP arrays would speed up things - however caching really large amounts of data such as users and prods (10000+) would terrorize the page loading times... A good middle way lies somewhere in between.
Caching groups, parties, platforms etc. as PHP arrays would speed up things - however caching really large amounts of data such as users and prods (10000+) would terrorize the page loading times... A good middle way lies somewhere in between.
Just do the caching segmented, then you can cache prety much everything.
If you start rendering only cached stuff, then you can also sneak a converssion of the db in the background without having to rewrite the web modules.
So yes, I agree: Cache cache cache, rewrite all web modules so they dont ever use the "bad" tables, this way you could phase them out afterwards in the db, replacing them with more elegant sollutions. Its much better to start write stuff like this with a bigger plan from the beginning. That way its easier to see why it has to be done.
If you start rendering only cached stuff, then you can also sneak a converssion of the db in the background without having to rewrite the web modules.
So yes, I agree: Cache cache cache, rewrite all web modules so they dont ever use the "bad" tables, this way you could phase them out afterwards in the db, replacing them with more elegant sollutions. Its much better to start write stuff like this with a bigger plan from the beginning. That way its easier to see why it has to be done.
melwyn: I doubt caching large amounts of data is so bad. Well it depends how large they are.
For a previous intranet I worked on, on some pages we needed acces to the whole hierarchy of the site ( we had at least 8,000 pages ) including the rights per users/groups. To optimize that we cached the whole tables we needed as GZencoded and serialized PHP arrays. Including, unzipping, unserializing and parsing such PHP arrays that would be 1.7mb otherwise took usually 0.1 second instead of ~4 seconds to request the datas from the DB.
For a previous intranet I worked on, on some pages we needed acces to the whole hierarchy of the site ( we had at least 8,000 pages ) including the rights per users/groups. To optimize that we cached the whole tables we needed as GZencoded and serialized PHP arrays. Including, unzipping, unserializing and parsing such PHP arrays that would be 1.7mb otherwise took usually 0.1 second instead of ~4 seconds to request the datas from the DB.
im just gonna cache this query pure and simple. just to get cdc.php working properly again till i redo the cdc cdc2 cdc3 crap.. i'll take care of it tonight perhaps...
also, doing a sql-call to get the version-number of pouet (on each page) could probably be avoided. :)
PS:
According to your 1st post, I had similary speed problems by using such a beast of a query.
Somehow I gained speed by replacing the LEFT JOIN with
an INNER JOIN and sort the conditions by their "selective power" ... meaning 1st contition should leave out the most data you don't need and from that on you can dig deeper with more complicated conditions.
That would work if you have a COUNT(), LEFT JOIN and a GROUP BY in it....
But as I look closer now you're using at least 3(!) joins on a MySQL database...well, that's damn slow. I'd suggest to split that thingie in two. F.e. get the groups first with a simple select+condition and buffer 'em in an array with it's id as array-index. Later you just get the groupname in the PHP routy by picking the name with the id from array.
According to your 1st post, I had similary speed problems by using such a beast of a query.
Somehow I gained speed by replacing the LEFT JOIN with
an INNER JOIN and sort the conditions by their "selective power" ... meaning 1st contition should leave out the most data you don't need and from that on you can dig deeper with more complicated conditions.
That would work if you have a COUNT(), LEFT JOIN and a GROUP BY in it....
But as I look closer now you're using at least 3(!) joins on a MySQL database...well, that's damn slow. I'd suggest to split that thingie in two. F.e. get the groups first with a simple select+condition and buffer 'em in an array with it's id as array-index. Later you just get the groupname in the PHP routy by picking the name with the id from array.
what puzzles me most is that this query rarely changes, so if it would be beeing cached by sql it would be no problem..
shouldnt there be someway to tell the db only to query for real if any of the cdc stuff got updated in the database since last time?.. i find it peculiar how with all the years of optimization code they been putting into it they still expect ppl to know the proper way of doing db design instead of doing/allowing the query optimizations to be done on db side.. *shrug* oh well, i guess i just dont know enough of db managment and should stfu :)
shouldnt there be someway to tell the db only to query for real if any of the cdc stuff got updated in the database since last time?.. i find it peculiar how with all the years of optimization code they been putting into it they still expect ppl to know the proper way of doing db design instead of doing/allowing the query optimizations to be done on db side.. *shrug* oh well, i guess i just dont know enough of db managment and should stfu :)
Quote:
But as I look closer now you're using at least 3(!) joins on a MySQL database...well, that's damn slow.
Are you serious?
Depends on some more factors like simultaneously (what a word) users online, how much rows in table, how good it is indexed.
But I've had a search function that (in the worst case) had to look in 4 tables. I tried it with multiple left joins (first) and got reported: "Man, the search function takes toooo freaking long....at 300+ users online".
So a college reviewed the indexing and I split up the monster query into few shorter but faster ones...and it worked.
In the end we built a complete site-caching additionally, exept the search function. That's what I experienced.
But I've had a search function that (in the worst case) had to look in 4 tables. I tried it with multiple left joins (first) and got reported: "Man, the search function takes toooo freaking long....at 300+ users online".
So a college reviewed the indexing and I split up the monster query into few shorter but faster ones...and it worked.
In the end we built a complete site-caching additionally, exept the search function. That's what I experienced.
I kindof assumed you where benchmarking wellindexed tables with friendly db layout when you made that previous statement. Obviously if you didnt have proper indexing it would get *slow*.
If the database's physical model is well maintained (proper indexes, mainly) inner joins should be basically free.
Tweaking the sql or schema because the indexes are not there is often done, but not because it's a good idea. It's the wrong place to tweak, in that case. (But that's only true for a properly normalized schema, of course)
Tweaking the sql or schema because the indexes are not there is often done, but not because it's a good idea. It's the wrong place to tweak, in that case. (But that's only true for a properly normalized schema, of course)
