Re: Key trouble
Andrei Zmievski wrote: > > On Tue, 23 Jan 2001, Gerald L. Clark wrote: > > > Why isn't it using index on the last query? > > > > > Because there isn't an index that contains both fields. > > So it can't use separate indexes? > > I guess the obvious solution would be to create an index on both of > those fields, but I will also need to have an individual indexes on > those fields as well - is that a good way to do it? > > -Andrei Well, if you make an index on field1,field2 ( I forgot the field names ) you only need another index on field2, because queries on field1 can use the field1,field2 index. - 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
Re: Key trouble
* Andrei Zmievski <[EMAIL PROTECTED]> wrote on 23.01.01 16:54: > mysql> show keys from ARTICLES; >... [Andrei's mail trimmed down quite a bit]: > | ARTICLES | 1 | Publication_ID|1 | Publication_ID| A > |NULL | NULL | NULL | | >... > | ARTICLES | 1 | Status|1 | Status| A > |NULL | NULL | NULL | | >... > mysql> explain select count(*) from ARTICLES where Status = 'completed'; >... > | ARTICLES | ref | Status| Status | 255 | const | 8587 | where used; >Using index | >... > mysql> explain select count(*) from ARTICLES where Publication_ID = 9; >... > | ARTICLES | ref | Publication_ID | Publication_ID | 4 | const | 8526 | where >used; Using index | >... > mysql> explain select count(*) from ARTICLES where Publication_ID = 9 and Status = >'completed'; >... > | ARTICLES | ALL | Publication_ID,Status | NULL |NULL | NULL | 10440 | where >used | >... > Why isn't it using index on the last query? MySQL shows "Using index" in the first two queries because it could find the whole result in one of the indexes in these cases (it only needed to look at the index and not the table). With your last query, there is no index which would contain both Publication_ID and Status in the first two key fields, and thus MySQL must also refer to the table. Cardinality probably also plays an issue here, but I'll leave describing that up to some more knowledgeable people (I can't remember that offhand and I do need to go and eat now). -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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
Re: Key trouble
On Tue, 23 Jan 2001, Gerald L. Clark wrote: > > Why isn't it using index on the last query? > > > Because there isn't an index that contains both fields. So it can't use separate indexes? I guess the obvious solution would be to create an index on both of those fields, but I will also need to have an individual indexes on those fields as well - is that a good way to do it? -Andrei As I was going up the stair, I met a man who wasn't there. He wasn't there again today. I wish, I wish he'd stay away. -Hughes Mearns - 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
Re: Key trouble
Andrei Zmievski wrote: > > Just installed 3.23.29-gamma and played around with fulltext indexes and > some other stuff. Ran into a weird problem that I hope you can shed some > light on. > > Basically, a query's where clause has two parts joined by 'and': > > select count(*) from ARTICLES where Publication_ID = 9 and Status = >'completed'; > > It takes 10 seconds to run in a table of 10,000 records. If I do a > query using those parts separately, the results come back instantly. I > ran explain on them and here are the results: > > mysql> show keys from ARTICLES; > >+--++---+--+---+---+-+--++--+ > | Table| Non_unique | Key_name | Seq_in_index | Column_name | >Collation | Cardinality | Sub_part | Packed | Comment | > >+--++---+--+---+---+-+--++--+ > | ARTICLES | 0 | PRIMARY |1 | Article_ID| A > | 10440 | NULL | NULL | | > | ARTICLES | 1 | Publication_ID|1 | Publication_ID| A > |NULL | NULL | NULL | | > | ARTICLES | 1 | Incoming_Datetime |1 | Incoming_Datetime | A > |NULL | NULL | NULL | | > | ARTICLES | 1 | idx_search|1 | Headline | A > |NULL |1 | NULL | FULLTEXT | > | ARTICLES | 1 | idx_search|2 | Tagline | A > |NULL |1 | NULL | FULLTEXT | > | ARTICLES | 1 | idx_search|3 | Byline| A > |NULL |1 | NULL | FULLTEXT | > | ARTICLES | 1 | idx_search|4 | Body | A > |NULL |1 | NULL | FULLTEXT | > | ARTICLES | 1 | Status|1 | Status| A > |NULL | NULL | NULL | | > >+--++---+--+---+---+-+--++--+ > 8 rows in set (0.00 sec) > > mysql> explain select count(*) from ARTICLES where Status = 'completed'; > >+--+--+---++-+---+--+-+ > | table| type | possible_keys | key| key_len | ref | rows | Extra > | > >+--+--+---++-+---+--+-+ > | ARTICLES | ref | Status| Status | 255 | const | 8587 | where used; >Using index | > >+--+--+---++-+---+--+-+ > 1 row in set (0.00 sec) > > mysql> explain select count(*) from ARTICLES where Publication_ID = 9; > >+--+--+++-+---+--+-+ > | table| type | possible_keys | key| key_len | ref | rows | Extra > | > >+--+--+++-+---+--+-+ > | ARTICLES | ref | Publication_ID | Publication_ID | 4 | const | 8526 | where >used; Using index | > >+--+--+++-+---+--+-+ > 1 row in set (0.00 sec) > > mysql> explain select count(*) from ARTICLES where Publication_ID = 9 and Status = >'completed'; > >+--+--+---+--+-+--+---++ > | table| type | possible_keys | key | key_len | ref | rows | Extra > | > >+--+--+---+--+-+--+---++ > | ARTICLES | ALL | Publication_ID,Status | NULL |NULL | NULL | 10440 | where >used | > >+--+--+---+--+-+--+---++ > 1 row in set (0.00 sec) > > Why isn't it using index on the last query? > Because there isn't an index that contains both fields. - 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
Key trouble
Just installed 3.23.29-gamma and played around with fulltext indexes and some other stuff. Ran into a weird problem that I hope you can shed some light on. Basically, a query's where clause has two parts joined by 'and': select count(*) from ARTICLES where Publication_ID = 9 and Status = 'completed'; It takes 10 seconds to run in a table of 10,000 records. If I do a query using those parts separately, the results come back instantly. I ran explain on them and here are the results: mysql> show keys from ARTICLES; +--++---+--+---+---+-+--++--+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +--++---+--+---+---+-+--++--+ | ARTICLES | 0 | PRIMARY |1 | Article_ID| A || 10440 | NULL | NULL | | | ARTICLES | 1 | Publication_ID|1 | Publication_ID| A ||NULL | NULL | NULL | | | ARTICLES | 1 | Incoming_Datetime |1 | Incoming_Datetime | A ||NULL | NULL | NULL | | | ARTICLES | 1 | idx_search|1 | Headline | A ||NULL |1 | NULL | FULLTEXT | | ARTICLES | 1 | idx_search|2 | Tagline | A ||NULL |1 | NULL | FULLTEXT | | ARTICLES | 1 | idx_search|3 | Byline| A ||NULL |1 | NULL | FULLTEXT | | ARTICLES | 1 | idx_search|4 | Body | A ||NULL |1 | NULL | FULLTEXT | | ARTICLES | 1 | Status|1 | Status| A ||NULL | NULL | NULL | | +--++---+--+---+---+-+--++--+ 8 rows in set (0.00 sec) mysql> explain select count(*) from ARTICLES where Status = 'completed'; +--+--+---++-+---+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra | | +--+--+---++-+---+--+-+ | ARTICLES | ref | Status| Status | 255 | const | 8587 | where used; |Using index | +--+--+---++-+---+--+-+ 1 row in set (0.00 sec) mysql> explain select count(*) from ARTICLES where Publication_ID = 9; +--+--+++-+---+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra || +--+--+++-+---+--+-+ | ARTICLES | ref | Publication_ID | Publication_ID | 4 | const | 8526 | where |used; Using index | +--+--+++-+---+--+-+ 1 row in set (0.00 sec) mysql> explain select count(*) from ARTICLES where Publication_ID = 9 and Status = 'completed'; +--+--+---+--+-+--+---++ | table| type | possible_keys | key | key_len | ref | rows | Extra || +--+--+---+--+-+--+---++ | ARTICLES | ALL | Publication_ID,Status | NULL |NULL | NULL | 10440 | where used || +--+--+---+--+-+--+---++ 1 row in set (0.00 sec) Why isn't it using index on the last query? -Andrei "When I get a little money, I buy books; and if any is left I buy food and clothes." -- Erasmus - 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
