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 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/ 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457566 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 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457584 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 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457586 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457630 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] Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457644 Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 The EXPLAIN is attatched You sure? Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457647 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 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457661 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457752 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? Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457789 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457812 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? Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-457935 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-458030 Share on other sites More sharing options...
jaymc Posted February 5, 2008 Author Share Posted February 5, 2008 I have indexes on all 3 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-458109 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? Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-458542 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-458639 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 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-458867 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-458929 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: Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-459633 Share on other sites More sharing options...
jaymc Posted February 6, 2008 Author Share Posted February 6, 2008 Here [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-459637 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-459869 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.. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-459935 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. Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-459966 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 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-460289 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 Link to comment https://forums.phpfreaks.com/topic/89355-heavily-accessed-large-tables/#findComment-460844 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.