[wp-hackers] sql problems

[mikeschinkel at newclarity.net]

Which ratings plugin are you using?

Sent from my iPhone

On Apr 21, 2009, at 10:34 AM, "B. V." <phil2k at gmail.com> wrote:

> 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.
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers