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
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
