请问我的设置的索引有问题吗
索引设置有
名 栏位名 索引类型
indexs1 number, stattuima, statsizi Normal
indexs1 uid, printstat, id Normal
indexs1 stattuima, reportstat Normal
indexs1 stattuima, pid5, classid Normal
indexs1 pid5, dingmoney Normal
indexs1 uid Normal
dingdanbiao.MYD 这个文件到240M
dingdanbiao.MYI 这个文件到130M
因为经常查询 WHERE number='1212' and stattuima=0 and statsizi='';组合，一旦量大（都是100万笔数据）都会有超出三秒的。
请教大家我的索引设置是否合理或有什么方法优化的呢。
explain SELECT uid,pid2,pid3,pid4,pid5,money,om1,om2,om3,om4,om5 FROM dingdanbiao WHERE number='1212' and stattuima=0 and statsizi='';
+----+-------------+------------------+------+-------------------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+-------------------------+---------+---------+-------------------+------+-------------+
| 1 | SIMPLE | dingdanbiao | ref | indexs1,indexs3,indexs4 | indexs1 | 16 | const,const,const | 2 | Using where |
+----+-------------+------------------+------+-------------------------+---------+---------+-------------------+------+-------------+
1 row in set
mysql> show index from dingdanbiao;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| dingdanbiao | 0 | PRIMARY | 1 | id | A | 92 | NULL | NULL | | BTREE | |
| dingdanbiao | 1 | indexs1 | 1 | number | A | NULL | NULL | NULL | | BTREE | |
| dingdanbiao | 1 | indexs1 | 2 | stattuima | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs1 | 3 | statsizi | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs2 | 1 | uid | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs2 | 2 | printstat | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs2 | 3 | id | A | NULL | NULL | NULL | | BTREE | |
| dingdanbiao | 1 | indexs3 | 1 | stattuima | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs3 | 2 | reportstat | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs4 | 1 | stattuima | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs4 | 2 | pid5 | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs4 | 3 | classid | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs5 | 1 | pid5 | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs5 | 2 | dingmoney | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs6 | 1 | uid | A | NULL | NULL | NULL | YES | BTREE | |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
15 rows in set
[解决办法]
你提供的信息上显示你的表中不过才92条记录。
建议提供你实际的信息以供分析。
[解决办法]
number='1212' and stattuima=0 and statsizi
这三个字段加组合索引
[解决办法]
| dingdanbiao | 1 | indexs1 | 1 | number | A | NULL | NULL | NULL | | BTREE | |
| dingdanbiao | 1 | indexs1 | 2 | stattuima | A | NULL | NULL | NULL | YES | BTREE | |
| dingdanbiao | 1 | indexs1 | 3 | statsizi | A | NULL | NULL | NULL | YES | BTREE | |
怎么都是空？ANALYZE TABLE 一下然后看一下统计更新结果。
[解决办法]
现在查询速度如何？ 如果慢，再贴一下EXPLAIN
[解决办法]
ANYLYZE 之后情况如何？
