Hi, I've really interresting topic this time: our application stopped getting data. It turned out these queries were unexpectedly slow while operating on MyISAM tables:
+----+-----------+-----------+--------------------------+---------+------+----------------+------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | | Info | | +----+-----------+-----------+--------------------------+---------+------+----------------+------------------------------------------------------------------------------------------------+ | 16 | wwwpedant | localhost | Saccharomyces_cerevisiae | Query | 0 | Sorting |result | select descr,hitcode,pval,score from blast where prot_data_id=180 and |pval<0.5 order by 4 desc | In case of other databases same applications and same mysqld operated normally. I think I see the problem why it's so slow: Instead of having: +------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | id | int(11) | | PRI | 0 | | | prot_data_id | int(11) | | MUL | 0 | | | contig_data_id | int(11) | | MUL | 0 | | | contig_data_code | varchar(100) | | | | | | code | varchar(100) | | MUL | | | | hitcode | varchar(255) | | MUL | | | | score | int(11) | | | 0 | | | pval | varchar(255) | | | | | | ident | int(11) | | | 0 | | | posit | int(11) | | | 0 | | | begin | int(11) | | | 0 | | | end | int(11) | | | 0 | | | length | int(11) | | | 0 | | | hitlength | int(11) | | | 0 | | | hitstart | int(11) | | | 0 | | | hitstop | int(11) | | | 0 | | | descr | varchar(255) | YES | MUL | NULL | | | aln | longtext | YES | | NULL | | | conf | varchar(6) | YES | | NULL | | | manual | char(3) | YES | | NULL | | +------------------+--------------+------+-----+---------+-------+ we have in one case: +------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | id | int(11) | | | 0 | | | prot_data_id | int(11) | | | 0 | | | contig_data_id | int(11) | | | 0 | | | contig_data_code | varchar(100) | | | | | | code | varchar(100) | | | | | | hitcode | varchar(255) | | | | | | score | int(11) | | | 0 | | | pval | varchar(255) | | | | | | ident | int(11) | | | 0 | | | posit | int(11) | | | 0 | | | begin | int(11) | | | 0 | | | end | int(11) | | | 0 | | | length | int(11) | | | 0 | | | hitlength | int(11) | | | 0 | | | hitstart | int(11) | | | 0 | | | hitstop | int(11) | | | 0 | | | descr | varchar(255) | YES | | NULL | | | aln | longtext | YES | | NULL | | | conf | varchar(6) | YES | | NULL | | | manual | char(3) | YES | | NULL | | +------------------+--------------+------+-----+---------+-------+ Actually, here's the diff: 4,6c4,6 < | id | int(11) | | | 0 | | < | prot_data_id | int(11) | | | 0 | | < | contig_data_id | int(11) | | | 0 | | --- > | id | int(11) | | PRI | 0 | | > | prot_data_id | int(11) | | MUL | 0 | | > | contig_data_id | int(11) | | MUL | 0 | | 8,9c8,9 < | code | varchar(100) | | | | | < | hitcode | varchar(255) | | | | | --- > | code | varchar(100) | | MUL | | | > | hitcode | varchar(255) | | MUL | | | 20c20 < | descr | varchar(255) | YES | | NULL | | --- > | descr | varchar(255) | YES | MUL | NULL | | It used to be: mysql> show index from blast; +-------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | |Cardinality | Sub_part | Packed | Comment | +-------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+ | blast | 0 | PRIMARY | 1 | id | A | | 86001 | NULL | NULL | | | blast | 1 | hitcode | 1 | hitcode | A | | NULL | NULL | NULL | | | blast | 1 | descr | 1 | descr | A | | NULL | NULL | NULL | | | blast | 1 | prot_data_id | 1 | prot_data_id | A | | NULL | NULL | NULL | | | blast | 1 | contig_data_id | 1 | contig_data_id | A | | NULL | NULL | NULL | | | blast | 1 | code | 1 | code | A | | NULL | NULL | NULL | | +-------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+ but now there're no indexes at all for this table. OK, my question is: could someone imagine a way how such a thing could happen? The information about KEY's is set when the table is created. I suspect more end-user problem then mysqld problem, but could it happen that the whole information in "Key" column got dropped? Can one get it out from the .frm file? Please Cc: me in replies. Thanks. -- Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs MIPS / Institute for Bioinformatics <http://mips.gsf.de> GSF - National Research Center for Environment and Health Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany tel.: +49-89-3187 3616 , fax: +49-89-3187 3585 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
