[wp-hackers] sql problems

[B. V.]

Hi.
There are some problems with MySQL optimizations ( on big sites, are
visibile effects ).
>From my mysql-slow-query log:

1. *SELECT rating_username, rating_rating, rating_ip FROM wp_ratings WHERE
rating_postid = 450;*

the wp_ratings table doesn't have an index on "rating_postid" column:

CREATE TABLE `wp_ratings` (
  `rating_id` int(11) NOT NULL auto_increment,
  `rating_postid` int(11) NOT NULL,
  `rating_posttitle` text NOT NULL,
  `rating_rating` int(2) NOT NULL,
  `rating_timestamp` varchar(15) NOT NULL,
  `rating_ip` varchar(40) NOT NULL,
  `rating_host` varchar(200) NOT NULL,
  `rating_username` varchar(50) NOT NULL,
  `rating_userid` int(10) NOT NULL default '0',
  PRIMARY KEY  (`rating_id`)
);

mysql> show index from wp_ratings;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| wp_ratings |          0 | PRIMARY  |            1 | rating_id   |
A         |         964 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

So I fixed it with:
mysql> alter table wp_ratings add index (rating_postid);
Query OK, 965 rows affected (0.27 sec)
Records: 965  Duplicates: 0  Warnings: 0


CREATE TABLE `wp_ratings` (
  `rating_id` int(11) NOT NULL auto_increment,
  `rating_postid` int(11) NOT NULL,
  `rating_posttitle` text NOT NULL,
  `rating_rating` int(2) NOT NULL,
  `rating_timestamp` varchar(15) NOT NULL,
  `rating_ip` varchar(40) NOT NULL,
  `rating_host` varchar(200) NOT NULL,
  `rating_username` varchar(50) NOT NULL,
  `rating_userid` int(10) NOT NULL default '0',
  PRIMARY KEY  (`rating_id`),
  KEY `rating_postid` (`rating_postid`)
);

2. in wp-includes/post.php it's a strange query ( see bellow ) in function
update_postmeta_cache(), reported by mysql-slow-query log:

*SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN
(866,862,860,858,856,854,852,850,848,846,844,842,839,837,835,833,831,829,827,825,823,821,819,817,815,813,810,808,806,804,802,800,798,795,793,790,788,786,784,782,780,778,776,774,772,770,767,765,763,761,759,701,698,696,694,692,690,688,686,684,682,680,678,676,674,672,670,668,666,664,662,660,658,655,653,651,649,533,531,529,527,524,521,519,516,514,512,510,508,506,504,502,500,498,496,494,492,490,486,484,482,480,478,476,473,470,468,466,464,463,458,460,457,452,451,447,448,444,442,441,438,429,435,433,431,427,424,425,421,420,418,414,413,406,410,408,405,402,398,400,396,393,391,389,388,384,385,379,376,374,372,371,368,366,365,357,361,359,356,353,351,350,346,347,344,342,339,336,337,334,331,328,326,324,321,322,319,316,314,313,309,307,306,302,303,299,293,288,290,286,284,283,280,275,278,276,273,270,268,265,259,263,258,255,218,1);
*

This query apears on about each 5 seconds ...

This I don't know how to fix it, only to offer my suggestion that there
should be a table ( temporary or not ) for those values search with mysql
"SELECT ... WHERE post_id IN (x,y,z,..... )" !



-- 
The Roman Rule
       The one who says it cannot be done should never interrupt the
       one who is doing it.