Preserved Topic: 300X MySQL Performance Increase (Page 1 of 1)

Holy cow. I used to have a caching system on my events calendar because it was taking 30+ seconds to generate a page. Then I noticed that the page generates in under 2 seconds while generating the exact same page with an expanded WHERE query.

I ran the query in the MySQL unix client and lo & behold, the query went from 25 seconds to .25 seconds. Then I added indexes to a many-to-many junction table inherent in the WHERE clause and boom .09 second query.

I can see how database experience is valuable :0



-jiblet

you wanna increase it even more? add CLUSTERS to your INDEXES.

Do you mean like creating indexes of multiple columns?

-jiblet

uh, sorry i dont know the details.
but clusters work like pointers or so. less space on the harddrive is used and so inserting and deleting is much faster.
http://www.dragonlee.co.uk/adv01.html

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

Grumble- I saw no mention of CLUSTERs on that page you pointed to, but I think it applies to Oracle and not MySQL anyway.

InI- I kind of dove headfirst in database-driven web apps this spring with my Events Calendar. When I started I was seeking academic credit (despite getting paid while working on it, eheheh). My advisor was a database researcher, so he really drilled home the importance of database design decisions and normalization.

The material I read about normalization clearly demonstrated the importance of good database design, yet it wasn't until I started coding that I realized how much amorphous the concept of normalized databases really is. In the real world, efficiency can be gained by bending the rules of normalization slightly.

The most confusing thing to me about the project was the many-to-many relationships which are modeled via a junction table which is really just the two PRIMARY KEYs of the two tables to be related. Initially I was too concerned with solving all my output needs in the JOIN clauses, when in fact the WHERE clause is where most of the necessary row restrictions should be placed.

I'm finally just scraping the surface of multi-column indexes and other efficiency concepts. Right now I am modularizing the calendar script to provide functions that fetch the data in such a way that I can build widely varied output for pages with different layouts.

Which brings up another point... the database is designed to store data about events in a flexible way so that any conceivable subset of information can be retrieved through a simple query. Yet the tradeoff is that the data is not in the common form that is used for output... so there is repetitive processing that must be done to convert internal data to human-readable format. I'm trying to develop a conceptual function layer that queries the database and returns the data in output form without becoming to specific to use on any of the various pages where events are listed. And of course, do all that without sacrificing efficiency.

Now you throw this new wrench into the works... closing connections ASAP. I'll definitely be taking that into consideration as well. My current design makes that difficult because I group queries up with code to form easily commentable units. In some cases later queries depend on results of earlier queries, so I don't know how far I can optimize resource allocation.



-jiblet

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

I forget where I read this, but somewhere in the MySQL manual it talks about the length of fields, and how certain optimizations can only be made when different fields are the same length (perhaps multi-column indexes should go over same-length fields).

Probably the reason that it took me so long to get into these details is that my strength in programming lies in algorithms. I am good at estimating performance and writing efficient code, but because I don't understand the algorithms that MySQL uses, I fall short in the DB department. Fortunately the documentation has very good information in this regard, and so it's only a matter of time before I master DB optimization.

I guess I agree that the DB should store minimum information and the application should contain the formatting (preferably separate from the actual application logic). If MySQL had sub-selects and stored procedures, it would offer some interesting possibilities for maintaining formatting information in the database without cluttering it with extra fields or information.

As an example, in the events calendar, the main table contains start_time and end_time, both DATETIMEs. This is the minimum information needed to describe any event. Yet when it comes time to output the data, it takes 20+ lines of PHP code to generate all the necessary output. Depending on the relationship between the two, it might print: Dec. 9, or Dec. 9-10, or Dec. 9-Jan. 1. Likewise the time could be: 8am, 8-10am, 8am-1pm, Noon, All Day, etc. So it would be cool to have a function in the DB such that you could just call outputDate() or outputTime() right in your query so you would have the output handy without bloating up your app logic.

I just thank god that I had someone to ask me the right questions about my DB design so that I did it the right way from the beginning, because it's turning out to be very easy to enhance this application. When I took my first C programming class I questioned the purpose of pseudo-code, but now I spend a good deal of my time trying to develop new flowcharting and commenting techniques to make my code maintainable.

-jiblet

yea, jiblet. you might be right about the oracle thing. we here use an oracle 8 db and use clusters.
and since my mysql-dbs are so small i never really thought about optimizing.