MySQL Forums
Forum List  »  General

Re: CPU usage, need some help.
Posted by: Tristan Foureur
Date: May 02, 2009 03:29PM

Hi,

Thanks for your help. I saw that this query :

SELECT f1_nom,f2_nom,f1_id,f2_id FROM fb_qms_amis WHERE f1_id = $id OR f2_id = $id

It's one of the queries that takes quite long to execute... This one is taking 2 seconds which is huge ... So, i'm gonna give some details about the fb_qms_amis table, but the average number of results is around 200, I tried SQL_BIG_RESULT and that's not worth it.

Here is the EXPLAIN :

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE fb_qms_amis ALL NULL NULL NULL NULL 2758115 Using where

(The table is pretty huge, 2,700,000 entries, and it's gonna have way more in the future...)


SHOW TABLE STATUS fb_qms_amis :

fb_qms_amis MyISAM 10 Dynamic 2754031 39 107753232 281474976710655 28312576 0 2810291 2009-04-30 18:20:21 2009-05-02 23:22:06 NULL latin1_swedish_ci NULL

SHOW TABLE CREATE fb_qms_amis :

fb_qms_amis CREATE TABLE `fb_qms_amis` (
`id` int(30) NOT NULL auto_increment,
`f1_id` int(30) NOT NULL default '0',
`f1_nom` varchar(255) NOT NULL default '',
`f2_id` int(30) NOT NULL default '0',
`f2_nom` varchar(255) NOT NULL default '',
`date` int(30) NOT NULL default '0',\n PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2810447 DEFAULT CHARSET=latin1

And the SHOW TABLE INDEXES
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
fb_qms_amis 0 PRIMARY 1 id A 2754917 NULL NULL BTREE

Any idea about how I could optimise this ?



Edited 1 time(s). Last edit at 05/02/2009 03:47PM by Tristan Foureur.

Options: ReplyQuote


Subject
Written By
Posted
Re: CPU usage, need some help.
May 02, 2009 03:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.