php - How to Handle a great number of rows with SQL Queries and take only small amount of data efficiently? -

-

i'm coding site in php, , site contain messages(like 100.000 , 200.000 or more) users post on site. problem is, messages stored on table called 'site_messages' it's id. means, messages aren't grouped poster, it's grouped id. if want fetch messages posted user 'foo', have query lot of rows, , slow think. or want fetch messages post subject(yes, contain post subject column too, , maybe more column add), must query table again, , unfortunately, less efficient. there speedy solutions that? i'm using php , mysql(and phpmyadmin).

edit: example, table this:

messageid: 1

messagecontent(varchar, message user posts): hi site. bye!

messageposter(varchar): crazyuser

messagepostdate: 12/12/09

messagepostedin(varchar, post subject): how make pizza

messageid: 2

messagecontent(varchar): site reallllly sucks.

messageposter(varchar): top_lel

messagepostdate: 12/12/09

messagepostedin(varchar): hello, have question!

messageid: 3

messagecontent(varchar): admin of site?

messageposter(varchar): creepy2000

messagepostdate: 1/13/10

messagepostedin(varchar): site boring.

etc...

this dbs (especially relationship dbs) built for! mysql , other dbs use things indexes access rows need in efficient way. able write queries select * site_messages subject "news%" order entrydatetime desc limit 10 find latest ten messages starting "news", or select * site_messages, user user.userid='foo' , site_messages.fk_user=user.id find posts user, , you'll find performs pretty well. these, you'd have (amongst others) index subject column, , index on fk_user column.

work on having table structure (data model). of course if have issues can research db performance , topic of explain plans help.

yes, each set of columns want, query table again. think of query set of rows. avoid sending large numbers of rows on connections. other commenters have suggested, can't more without more details tables.


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more