Zhe Wang <[EMAIL PROTECTED]> wrote on 07/28/2005 10:40:08 AM: > Hi, there, > > I am have a hard time figuring out why a simple query is extremely
> slow. I would greatly appreciate if you can shed some light! > > The table is in InnoDB: > > CREATE TABLE `rps_hits` ( > `gi` int(10) unsigned NOT NULL default '0', > `cddid` int(10) unsigned NOT NULL default '0', > `bit_score` float NOT NULL default '0', > `evalue` double NOT NULL default '0', > `identity` smallint(5) unsigned NOT NULL default '0', > `query_from` smallint(5) unsigned NOT NULL default '0', > `query_to` smallint(5) unsigned NOT NULL default '0', > `hit_from` smallint(5) unsigned NOT NULL default '0', > `hit_to` smallint(5) unsigned NOT NULL default '0', > `hit_len` smallint(5) unsigned NOT NULL default '0', > `align_len` smallint(5) unsigned NOT NULL default '0', > `bz_alignment` blob NOT NULL, > KEY `gi` (`gi`), > KEY `cddid` (`cddid`), > KEY `evalue` (`evalue`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1410065408 AVG_ROW_LENGTH=300 > > It is a big table with more than 60 million rows, the rps_hits.ibd > file is 22 G. All the queries I mention below were run when no other job > were running against the database. > > I did a very simple query against the table: "select gi, cddid, > evalue from rps_hits where cddid=3161". It took 4 minutes and 29.90 > seconds to pull out only 1952 rows. Whereas another simply query on gi > "select gi, cddid, evalue from rps_hits where gi=393396" pulled out 1532 > rows in just 0.09 second. > > "Expalin" the above query gave: > mysql> explain select gi, cddid, evalue from rps_hits where cddid=3161\G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: rps_hits > type: ref > possible_keys: cddid > key: cddid > key_len: 4 > ref: const > rows: 1376 > Extra: Using where > 1 row in set (0.06 sec) > > It looks OK. > > Then I did "show index from rps_hits", all the index properties > for each of the 3 indexes are the same except cardinality. Column gi has > a cardinality of 3084286 and cddid has 14. Though a specific "select > count(distinct cddid) from rps_hits" returned 11156. Since 11156 unique > cddid is less than 0.01% of the total number of rows in the table, I > believed the server decided to do a full table scan (does anyone know > the exact percentage number of the total counts that MySQL uses as a > criteria when deciding to do a FTS?) > > I then use "use index" in the query after I made sure the query > and index were no longer in the cache : "select gi, cddid, evalue from > rps_hits use index (cddid) where cddid=3161". It still took a long time > (2 min 59.79 sec) to return the 1952 rows. > > I also noticed that a simple query on evalue like "select gi, > cddid, evalue from rps_hits where evalue=1.97906;" is also extremely > slow even "force index" was used (5.78 sec for 56 rows). > > I have not been able to figure out what went wrong. Since the > index on gi worked fine, I am just wondering if the slowness is caused > by the large size of the table and that the indexes on cddid and evalue > were created as second index and third index respectively in "create > table". However I have another huge table with blob column and with > comparable size and number of rows to this rps_hits table, if I searched > on the third index, it was very fast. > > This problem really troubled me and I would greatly appreciate if > anyone could give me a hint. Thank you in advance! > > Regards, > Zhe > My first idea is to have you run ANALYZE TABLE against your table. Analyze table will update your index statistics (like cardinality). Your index cache may be too small or your the actual index may be too large to accomodate it in memory all at the same time. That means that you are using swap space to store part of your indexes on disk and it may be takeing just that much more time to crawl a paged index compared to one that can reside completely (or mostly) in memory. How many records are returned is only important if you and your server are connected by a slow network or if you are receiving HUGE quantities of data (gigabytes) in your results. The slower the network, the longer it will take to transfer the data from the server to you. However, most modern networks take that factor out of consideration for issues that you are describing. My suspicion is that you memory paging and cache sizes and disk performance are your most likey bottlenecks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
