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

??????????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"??
???????????????????????????????????????妫�????????些????????????