????????????

??????????PHP????????????????????????????????????????????????????PHP?????????????????????????????????Щ????? PHP??????????????????????????????????????????????Щ???????????????????????迴???????????????????????????????? ??PHP??ó??????????????
?????Щ????λ?????????????????????????????????е???????棬???????????????? ???????г?????????????????????????MySQL????????????й??????????????????????????????????Щ???????????????????????? ?????????????????????????д????????μ??????
???????????????????????where??????ν??????????????????????????????????μ???
????CREATE TABLE mytable (
????id serial primary key??
????category_id int not null default 0??
????user_id int not null default 0??
????adddate int not null default 0
????);
??????????????????????????????????????????????????????????μ????
????SELECT * FROM mytable WHERE category_id=1;
???????????????????category_id?????????????????
????CREATE INDEX mytable_categoryid
????ON mytable (category_id);
????OK???????????????????в??????????????????磺
????SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
??????????????????????user_id????????????????????????????????????????????????????
????CREATE INDEX mytable_categoryid_userid ON mytable (category_id??user_id);
??????????????????????????????"????_???1??_???2??"????????????????????????????
?????????????????????ν??????????????????????е???????????????????????????????Щ?????????????OK??????????????????????????????????????EXPLAIN????
????EXPLAIN
????SELECT * FROM mytable
????WHERE category_id=1 AND user_id=2;
????This is what Postgres 7.1 returns (exactly as I expected)
????NOTICE: QUERY PLAN:
????Index Scan using mytable_categoryid_userid on
????mytable (cost=0.00..2.02 rows=1 width=16)
????EXPLAIN
??????????postgres????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????и?ORDER BY???????????????????????????????????order by?????????????????????檔
????SELECT * FROM mytable
????WHERE category_id=1 AND user_id=2
????ORDER BY adddate DESC;
?????е????????????????where????е???ν?????????????????ORDER BY??????е???ν????????????
????CREATE INDEX mytable_categoryid_userid_adddate
????ON mytable (category_id??user_id??adddate);
???????: "mytable_categoryid_userid_adddate" ????????
????"mytable_categoryid_userid_addda"
????CREATE
????EXPLAIN SELECT * FROM mytable
????WHERE category_id=1 AND user_id=2
????ORDER BY adddate DESC;
????NOTICE: QUERY PLAN:
????Sort (cost=2.03..2.03 rows=1 width=16)
????-> Index Scan using mytable_categoryid_userid_addda
????on mytable (cost=0.00..2.02 rows=1 width=16)
????EXPLAIN
????????EXPLAIN????????????е?????????????????????????????????????????????????????????????????????????????????????е??????????????????????????????ɡ?
???????????????????????????????????????????????????????????????????????????postgres???????????????????????????--?? ORDER BY????У?????where????е???Ρ????????????????????????????????????????????????????????????????κε??????????????????? ??postgres?????????Щ????????????
????EXPLAIN SELECT * FROM mytable
????WHERE category_id=1 AND user_id=2
????ORDER BY category_id DESC??user_id DESC??adddate DESC;
????NOTICE: QUERY PLAN:
????Index Scan Backward using
????mytable_categoryid_userid_addda on mytable
????(cost=0.00..2.02 rows=1 width=16)
????EXPLAIN
?????????????????????????????????????????????????????????濪???????????????κε?????
?????? ????????????????????????????????????????????????????????????????????????????????????????????????????罫??????? ??????????????where????????е??????????????????????????????????????????????????????????????????????????????????е????????? ????????????????Щ????????У????????????
??????????????????ò??????????????????????????????????????????????????EXPLAIN?????????????????????????е????????????????OK???????????????????????????????????????????????????????????
?????????????????????????????????o?????????????????????????????????????????????????????o?????????б??????????????where??????????????????????С?????????????????????б?????????????????
????????????????Щ?????????????????????????????????EXPLAIN??????????ж??÷??????????????????????????????Щ??? ????????????????????????????????????????????????????Щ????￡?????????????δ???????????????????????????洢??????????? ??????????????????????????????????????????????顣
????????????????????????б???????????????????????????????????????????Щ????????????????MySQL????"OPTIMIZE TABLE"??
???????????????????????????????????????棬????????Щ????????????