On the other hand, a developer should almost always specify column names when doing INSERTs into a table. We all know what happens when a schema change occurs on the database and your code suddenly stops working.

Martin.

On Tue, Nov 18, 2014 at 5:10 PM, Martin Akolo Chiteri <martin.chiteri@gmail.com> wrote:


On Mon, Nov 17, 2014 at 2:23 PM, Adam Nelson via skunkworks <skunkworks@lists.my.co.ke> wrote:
Geoffrey,

1. There shouldn't be any MyISAM tables at all (aside from maybe the information schema), that's a problem right there.
2. Hopefully you're on Windows Server 2012?
3. It seems like you should still be able to move onto MySQL 5.6 which has InnoDB improvements.

Hi,  

4. 'SELECT *' should be rare in code. 

I highly suspect that this is a myth ....
 
It would be better if the developers were using an ORM and weren't writing very much SQL but that's probably not under your purview.

For some reason I cannot access Youtube at the monent but. One of the keynote talks at 2008's DjangoCon had Cal Henderson (Flickr's creator) dispute the fact that SELECT * FROM YOUR_TABLE is a high overhead query and should not be avoided by the Django ORM developers. Doing queries in the any other way leads to unreadable SQLs in some cases, like Geoffrey having tables with many columns http://image.slidesharecdn.com/hatedjangopart2-1220820560497639-9/95/why-i-hate-django-part-22-56-728.jpg?cb=1220891249

Perhaps someone needs to bring this argument to rest once and for all ....



Martin.
 
5. If you can find the top 5 queries by time, do an 'explain' on each one and you'll see whether indexes will help.  Be careful because indexes can slow down INSERTs and UPDATEs.
6. You might be able to reduce what gets logged - although that's a minor improvement usually.
7. MySQL works better under Linux.  I know that's a big change but it's worth thinking about over the longer term.

-Adam


--
Kili - Cloud for Africa: kili.io
More Musings: varud.com

On Mon, Nov 17, 2014 at 2:08 PM, geoffrey gitagia <ggitagia@gmail.com> wrote:

Thanks Adam
    I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements,  impacting on cache. 



_______________________________________________
skunkworks mailing list
skunkworks@lists.my.co.ke
------------
List info, subscribe/unsubscribe
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------

Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke