Page 1 of 3 123 LastLast
Results 1 to 15 of 37

Thread: MySQL Resources

  1. #1
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106

    MySQL Resources

    I'm looking for any good websites about writing and optimizing queries -- beyond the basics. Any recommendations?
    I'm Dan. This is my blog. I give you... free web stats.

  2. #2
    4x4
    Join Date
    Oct 2004
    Posts
    1,043
    Quote Originally Posted by Dan Grossman
    I'm looking for any good websites about writing and optimizing queries -- beyond the basics. Any recommendations?
    Hey Dan what do you consider "the basics"?

    I would assume you mean using Explain etc as the basics? Not to sure what you know/have done... post up what you have done.

    HUGE improvements come by properly indexed databases and seperate tables etc.

    I recently did a LOT of db optimizing... if you have specific questions let me know.

  3. #3
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106
    Quote Originally Posted by ToddW
    Hey Dan what do you consider "the basics"?

    I would assume you mean using Explain etc as the basics? Not to sure what you know/have done... post up what you have done.

    HUGE improvements come by properly indexed databases and seperate tables etc.

    I recently did a LOT of db optimizing... if you have specific questions let me know.
    Yes, something like SitePoint's article on adding indexes to a table I consider to be pretty basic.

    How can I determine the proper memory settings for the mySQL configuration for a given server and application?

    Is there any way to speed up queries grouped or sorted by aggregates that can't be indexed?

    Is there any way to speed up queries grouped or sorted by variable length TEXT fields?

    Perhaps there's an article that discusses the benefits of retrieving information using different types of joins and subqueries over eachother, since there are many ways to accomplish the same thing, with drastic performance differences.

    I'm not sure exactly what I'm looking for... that magic document that explains how some sites can deal with millions of pieces of data much faster than the quarter second it takes to "select [basic columns] from [table] order by [basic column]" from one of my 10,000 row tables.
    I'm Dan. This is my blog. I give you... free web stats.

  4. #4
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    If you're unfamiliar with the concept of normalization you should read this:

    http://dev.mysql.com/tech-resources/...alization.html

    If you have a mysql heavy application I would look to do the same types of optimizations high traffic forums have to do.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  5. #5
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106
    Do you have any specific examples of those optimizations?

    I believe the database schema I'm using is good. It's not completely normalized because of the huge performance hits it'd take to write 6 or 7 tables every time new data had to go in instead of one or two. There are some summary tables that are batch updated, but if I make a summary table for every type of report I want to build from the data I'll simply run out of disk space or processing time.

    Take a simple two column table like this:
    Code:
    CREATE TABLE example (ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name TEXT);
    Say you want to count up how many rows there are for each name in that table and sort those counts. You'd do

    Code:
    SELECT COUNT(ID) AS count FROM example GROUP BY name ORDER BY count
    The names can be arbitrarily long, and my own tests show that a fixed length text index won't change the time it takes to perform the query, nor can you index the count at all. EXPLAIN is going to show something like this:

    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	SIMPLE 	example 	ALL 	NULL 	 	NULL 	NULL 	 	NULL 	10000 	Using temporary; Using filesort
    With as little as 10,000 rows you start taking tenths of a second instead of hundredths of a second to get the result set of this simple query on this simple table. Given you don't have the storage space or CPU time to keep counts in another table, how do you optimize this? How do you deal with a million rows like this? What settings would improve performance of this query?
    Last edited by Dan Grossman; 03-24-2006 at 02:15 PM.
    I'm Dan. This is my blog. I give you... free web stats.

  6. #6
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    I think within SQL there is only so much you can do, the rest is server optimization/caching.

    vbulletin has a whole area of their forums for server optimizations, I've found that to be a good place to find info on mysql config edits that can speed performance.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  7. #7
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106
    Quote Originally Posted by Chris
    vbulletin has a whole area of their forums for server optimizations, I've found that to be a good place to find info on mysql config edits that can speed performance.
    I'll check it out, thanks
    I'm Dan. This is my blog. I give you... free web stats.

  8. #8
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106
    I went through every "server optimization request" thread on the first four pages of that forum and read all the responses given there for various servers. I applied what seemed most appropriate for the hardware I have.

    There's no change in the processing time of queries. Those similar to the example above still take exactly the same time (and some of the more complex ones I'd love to get some help with -- joins on subqueries and such, still take 1-2 full seconds).

    What the effect of the advice they're handing out seems to be a large bias towards caching. A lot of memory is allocated to it. I can see the results in my tests where many more queries, even complex ones with grouping and sorting on many rows, are being entirely cached and even after viewing several pages are still in the cache.

    So there's a benefit but not in the first time each query is run, which will still account for a high amount of the CPU time this server will spend.

    I'll keep an eye on that forum in case any interesting tips come up.
    I'm Dan. This is my blog. I give you... free web stats.

  9. #9
    Senior Member AndyH's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    553
    Are you making these summary tables HEAP tables?

    Using "USE INDEX()", "FORCE INDEX()" ?

    Using InnoDB tables for tables which are read/written from often - row level locking instead of table locking?

    Why in your example make "name" TEXT? VARCHAR would be better and index it.
    New website released. ya rly!

  10. #10
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106
    Quote Originally Posted by AndyH
    Are you making these summary tables HEAP tables?
    Batch-updated MyISAM tables.

    Using "USE INDEX()", "FORCE INDEX()" ?
    There's really no index type that applies to TEXT fields of arbitrary length, is there?

    Using InnoDB tables for tables which are read/written from often - row level locking instead of table locking?
    This wouldn't help this particular case. Every user has their own set of tables, so while many users doing these long queries is a problem I'm trying to solve, the queries aren't on the same tables so it's not a locking issue.

    Why in your example make "name" TEXT? VARCHAR would be better and index it.
    An example of the real problem I have. The length of the fields can be arbitrarily long -- full URLs (very long query strings), the titles of webpages, etc. Since I want to be able to provide links, I can't cut off the URLs using a fixed length field and index.
    I'm Dan. This is my blog. I give you... free web stats.

  11. #11
    Senior Member AndyH's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    553
    Maybe a FULLTEXT index? I don't use them so i'm not sure.

    Why not make them batch-updated HEAP tables?

    As for your example, there is only so much you can do with mySQLs configuration and query optimizing. Faster/better hardware, caching, and replication/clustering is how bigger sites deal with it.
    New website released. ya rly!

  12. #12
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106
    Quote Originally Posted by AndyH
    Maybe a FULLTEXT index? I don't use them so i'm not sure.

    Why not make them batch-updated HEAP tables?

    As for your example, there is only so much you can do with mySQLs configuration and query optimizing. Faster/better hardware, caching, and replication/clustering is how bigger sites deal with it.
    I tried it for the heck of it a few days ago, and the FULLTEXT index didn't improve things (it wasn't used, and wasn't listed as a possible index). I don't think they're meant for speeding up a group by type query.

    You're suggesting for 8,000 users I keep 16,000 summary tables in memory?

    If I have time next week I'm going to install MySQL 5 on one of my spare computers and check out performance of views, but I can't imagine selecting from a view would be faster than using the exact same query generating the view as a subquery.
    I'm Dan. This is my blog. I give you... free web stats.

  13. #13
    Senior Member AndyH's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    553
    Try this: (with `name` as a FULLTEXT index)
    SELECT COUNT(*) AS count, name FROM example FORCE INDEX(name) GROUP BY name ORDER BY count DESC;
    You're suggesting for 8,000 users I keep 16,000 summary tables in memory?
    16,000 tables? Can you give me an example of a summary table?
    New website released. ya rly!

  14. #14
    Registered
    Join Date
    Mar 2004
    Location
    Philadelphia, PA
    Posts
    106
    Quote Originally Posted by AndyH
    Try this: (with `name` as a FULLTEXT index)
    Code:
    Showing rows 0 - 29 (2186 total, Query took 0.8125 sec)
    SQL-query: 
    SELECT COUNT( * ) AS count, webpage
    FROM _1_hits
    FORCE INDEX ( ftIdx )
    GROUP BY webpage
    ORDER BY count DESC
    LIMIT 0 , 30
    That's the same amount of time it takes without the index; it wasn't used.

    16,000 tables? Can you give me an example of a summary table?
    This is a web stats service, like Google Analytics.

    This is one table every user has:
    (ID, date, uniqueVisitorCount, pageviewCount, returningVisitorCount, updatedTime)

    Those counts are derived data from the full log of hits to their site. Since there are millions to billions of hits recorded per day (every hit to every user of the site's website!) I can't simply update every table like this on every hit. They also allow me to provide this type of "summary stat" for a much longer period than I keep around the entire log.
    Last edited by Dan Grossman; 03-24-2006 at 07:05 PM.
    I'm Dan. This is my blog. I give you... free web stats.

  15. #15
    Senior Member AndyH's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    553
    I think you could do the summary tables better by only having one. Is this possible to do from your application because if you have alot of tables there will also be overhead from finding the correct table - for every query to the database.

    The summary table will be large, but say if you add "MemberID" MEDIUMINT(8) UNSIGNED and index it should be faster than thousands of seperate tables - and alot more organized.
    New website released. ya rly!

Similar Threads

  1. Replies: 0
    Last Post: 01-11-2006, 07:08 AM
  2. Replies: 0
    Last Post: 01-04-2006, 10:40 PM
  3. Clear my misunderstanding: 1 MySQL database for many programs?
    By iKwak in forum Website Programming & Databases
    Replies: 4
    Last Post: 08-21-2005, 10:40 AM
  4. Replies: 0
    Last Post: 11-25-2004, 01:06 AM
  5. Replies: 8
    Last Post: 11-12-2004, 04:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •