php - Best way to approach large MySQL Database organisation? -

-

i have question relating general approach pretty large mysql database. i've made php code interact database. i'm trying analyse hefty-ish set of data (~130k rows, 200 columns), , have been toying different methods so. i've been learning great deal along way, , feel though close getting setup really speedy, still bit stuck.

i began being firmly in 'excel' mindset. continually added more , more columns dataset, trying select various bits , pieces out purpose of statistical analysis. of php/mysql scripts had made took hours.

then, @ least basics working, learned joins. bit of revelation guess, resulted in me re-writing joins play nice data. net result massive increase in performance - took hours before takes 15 seconds now.

after chatting few people, came conclusion still make faster. way had set different samples of data each contained in different table. each table had it's data summarised in further table used part of joins - general info particular dataset stored in secondary table easy access , increase speed.

now, question have here this: better me change way database , application work these different samples of data combined single, large table? i've been experimenting far bit, , doesn't seem faster current method using.

in other words, better run lots of 'little' queries involving multi-table joins, doing @ moment, rather single, gigantic query involving multi-table joins? i've been examining execution time of queries , seems joins causing slow-down new method.

i under impression repeatedly sending small queries php mysql less optimal sending single query, there tipping point more complex queries not case? seem have reached point?

doing query optimization in php not way best performance out of db. formatted sql query , mysql's built-in query optimizer job better. (for example, query optimizer in mysql can things http://en.wikipedia.org/wiki/block_nested_loop)

but real answer depends on you're trying do. if speed number 1 priority, please list you're trying query , data schema. answers typically involve adding/removing indices , tweaking queries.

if keeping database footprint small goal (which highly doubt given how cheap disk space is), normalize everything.


Comments

Post a Comment

Popular posts from this blog

linux - Mailx and Gmail nss config dir -

-
i trying send mail linux command line using mailx command. can send local domain no problem want set mail send gmail account receive mail sent gmail account. after configuring mail.rc so: account gmail { set smtp=smtps://smtp.gmail.com:587 set smtp-auth=login set smtp-auth-user=username@gmail.com set smtp-auth-password=password set ssl-verify=ignore } i error: resolving host smtp.gmail.com . . . done. connecting 74.125.25.109 . . . connected. missing "nss-config-dir" variable. "/home/username/dead.letter" 11/354 . . . message not sent. after looking "nss-config-dir" here , located certn.db , keyn.db files , added mail.rc so: account gmail { set smtp=smtps://smtp.gmail.com:587 set smtp-auth=login set smtp-auth-user=username@gmail.com set smtp-auth-password=password set ssl-verify=ignore set nss-config-dir=/home/user/.mozilla/firefox/location.default } now when try send mail using command: echo "sent gmail account" | mailx -v -a
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more