php - How to index a query the right way -

php - How to index a query the right way -

i trying create db more optimized , in origin of indexing not sure how right.

i have query:

$year = date("y"); $thisyear = $year; //$nextyear = $thisyear + 1; $sql = mysql_query("select sum(points) userpoints ".$prefix."_publicpoints date between '$thisyear" . "-01-01' , '$thisyear" . "-12-31' , fk_player_id = $playerid"); $row = mysql_fetch_assoc($sql); $userpoints = $row['userpoints']; $sql = mysql_query("select fk_player_id ".$prefix."_publicpoints date between '$thisyear" . "-01-01' , '$thisyear" . "-12-31' grouping fk_player_id having sum(points) > $userpoints"); $row = mysql_fetch_assoc($sql); $userwrank = mysql_num_rows($sql)+1;

i not sure how index this? have tried indexing fk_player_id still looks through rows (287937).

i have indexed date field gives me in explain:

1 simple nf_publicpoints range idxdate idxdate 3 null 143969 using pushed condition; using temporary...

i have 2 calls same table... done in one?

how index and/or done smarter?

you should spend time reading on indexing, there's lot written it, , it's of import understand what's going on.

broadly speaking, , index imposes ordering on rows of table.

for simplicity's sake, imagine table big csv file. whenever row inserted, it's inserted @ end. "natural" ordering of table order in rows inserted.

imagine you've got csv file loaded in rudimentary spreadsheet application. spreadsheet display data, , numbers rows in sequential order.

now imagine need find rows has value "m" in 3rd column. given have available, have 1 option. scan table checking value of 3rd column each row. if you've got lot of rows, method (a "table scan") can take long time!

now imagine in add-on table, you've got index. particular index index of values in 3rd column. index lists of values 3rd column, in meaningful order (say, alphabetically) , each of them, provides list of row numbers value appears.

now have strategy finding rows value of 3rd column m! instance, can perform binary search! whereas table scan requires n rows (where n number of rows), binary search requires @ log-n index entries, in worst case. wow, that's sure lot easier!

of course, if have index, , you're adding rows table (at end, since that's how our conceptual table works), need need update index each , every time. little more work while you're writing new rows, save ton of time when you're searching something.

so, in general, indexing creates tradeoff between read efficiency , write efficiency. no indexes, inserts can fast -- database engine adds row table. add together indexes, engine must update each index while performing insert.

on other hand, reads become lot faster.

hopefully covers first 2 questions (as others have answered -- need find right balance).

your 3rd scenario little more complicated. if you're using like, indexing engines typically help read speed first "%". in other words, if you're selecting column 'foo%bar%', database utilize index find rows column starts "foo", , need scan intermediate rowset find subset contains "bar". select ... column '%bar%' can't utilize index. hope can see why.

finally, need start thinking indexes on more 1 column. concept same, , behaves stuff -- essentialy, if have index on (a,b,c), engine go on using index left right best can. search on column might utilize (a,b,c) index, 1 on (a,b). however, engine need total table scan if searching b=5 , c=1)

hopefully helps shed little light, must reiterate you're best off spending few hours digging around articles explain these things in depth. it's thought read particular database server's documentation. way indices implemented , used query planners can vary pretty widely.

more info , illustration visit here : http://blog.sqlauthority.com/category/sql-index/

php mysql optimization indexing