jaymc Posted February 4, 2008 Share Posted February 4, 2008 What is the best way to deal with heavily acessed large tables When I say large, lets just say you had a table with 5 million rows of data, all of which contain pretty well optimized data With an index yeh sure it only takes 0.8 second to complete a standard query, but this added with other queries combine to make a page take 3+ seconds to load What can one do when faced with a huge table which has relivent data and no redundant to clean out Bit stuck, any ideas/articles which go through combating this problem Quote Link to comment Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 Way too generic a question... optimization problems are always case-by-case. And there's no such thing as "well optimized data", since it's entirely dependent on how you query it. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 4, 2008 Author Share Posted February 4, 2008 Ok, the table is 6.7million rows and the query is pretty basic SELECT viewedpro.viewer, viewedpro.time, viewedpro.views, cache.propic as image FROM viewedpro LEFT JOIN cache ON viewedpro.viewer = cache.username WHERE viewedpro.username = 'JMC' GROUP BY viewedpro.viewer ORDER BY viewedpro.time DESC LIMIT 0,5 The execution time is completely dependant on the sheer size of the table rather than the query 0.8 seconds is quick for what its doing, but webpages should never really have a noticeable lag therefor I want to try and do something about it Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 4, 2008 Author Share Posted February 4, 2008 The left join appears to be adding 0.3 seconds onto the total query time.. yet the cache table is only 50,000 rows and the username field does have an index on it Quote Link to comment Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 A few things... First, could you post the EXPLAIN output? Mixing group by/order by will sometimes produce unexpected index usage. Second, why the LEFT JOIN? You're not doing anything with non-matching records... this can often be slower. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 4, 2008 Author Share Posted February 4, 2008 I changed LEFT to INNER and it didnt really make any difference in terms of query time, if thats what you meant? The EXPLAIN is attatched [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 The EXPLAIN is attatched You sure? Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 4, 2008 Author Share Posted February 4, 2008 Yeh http://www.phpfreaks.com/forums/index.php?action=dlattach;topic=180554.0;attach=3744;image Quote Link to comment Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 Well, with temporary and filesort (i.e. no covering index), you will never make this any faster. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 4, 2008 Author Share Posted February 4, 2008 So the speed Im having is purely down to table size? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 Unless you change the indexing, yes. Filesorts/temporary tables are necessarily size-dependent... temporary tables in particular. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 4, 2008 Author Share Posted February 4, 2008 What are the options then to deal with so many rows in a table? What do the experts do.. Think of myspace for instance, there messages table must have millions in at any one time, what might they do? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 If you added a covering index on (username, viewer, time), you should see a dramatic improvement. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 5, 2008 Author Share Posted February 5, 2008 I have indexes on all 3 Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 5, 2008 Author Share Posted February 5, 2008 Already* How about a way to partition the table, if there is such a method? What is the best practice to do this? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 5, 2008 Share Posted February 5, 2008 I have indexes on all 3 Really? Individually, or as a group? The EXPLAIN says otherwise. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 5, 2008 Author Share Posted February 5, 2008 I added the viewer index after I posted the explain, but in the query I gave you an index on the viewer would have had no purpose as it wasnt involved in the Word or any file sorting Regardless, queries still take 0.5 seconds.. combined with other large table queries meaning 3+ seconds for page execution I have to do something with the table.. sooner or later, without flusing out date, just wondering on the common methods to go about this Quote Link to comment Share on other sites More sharing options...
fenway Posted February 5, 2008 Share Posted February 5, 2008 I added the viewer index after I posted the explain, but in the query I gave you an index on the viewer would have had no purpose as it wasnt involved in the Word or any file sorting Could you post your SHOW CREATE TABLE? I think we're talking about different things. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 6, 2008 Author Share Posted February 6, 2008 I used phpmyadmin to create it so dont have the info, and SHOW CREATE TABLE didnt work, if thats even a valid command? Anyway, I attatched this which should be of use: Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 6, 2008 Author Share Posted February 6, 2008 Here [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
fenway Posted February 6, 2008 Share Posted February 6, 2008 Like I thought... you don't have a 3-column index, you have 3 indexes, each with one column.... that's very different. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 6, 2008 Author Share Posted February 6, 2008 Ok, what is the diffence and will not having this be an issue? No idea what that is.. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 6, 2008 Share Posted February 6, 2008 There are some excellent articles in the stickies on performance optimization that discuss covering indexes. Basically, MySQL picks one to use... not at all three. Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 6, 2008 Author Share Posted February 6, 2008 Ok, I will look into this and see what the score is Sounds useful Quote Link to comment Share on other sites More sharing options...
jaymc Posted February 7, 2008 Author Share Posted February 7, 2008 Ok I had a look into that covering index, I know how to create one, but just wondering the down sides on having it What does it actually do, and is it only good for certain queries vrs bad for different types of queries Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.