Hi.
On Thu 2002-07-25 at 10:49:47 -0500, [EMAIL PROTECTED] wrote:
> Hi, I have a problem where the index for a SET column does not get used
> once I link in another table.
>
> The output to explain:
>
> mysql> explain select count(*) from NS_articles where
> find_in_set('approved', artFlags) > 0;
>
>
>+-------------+-------+---------------+----------+---------+------+------+-------------------------+
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
>
>+-------------+-------+---------------+----------+---------+------+------+-------------------------+
> | NS_articles | index | NULL | artFlags | 2 | NULL | 560
> | where used; Using index |
>
>+-------------+-------+---------------+----------+---------+------+------+-------------------------+
>
> This is good, the index on artFlags is being used. Now I link in another
> table:
No, the index is not really used (possible_keys = NULL), because you
have an expression (FIND_IN_SET()) instead of a column and MySQL
cannot indexes on (most/any?) expressions. I.e. it is not used to find
the rows based on an index lookup, but MySQL sees that you are only
interested in column(s) from an index and therefore reads in the
index, instead of the data file ("using index"). Well, in hindsight,
"using index" surely was not best choice to describe that behaviour.
To make that more clear. Imagine you have 1.000.000 rows, 100 bytes
each. An index on the column of interest needs 10 bytes per row.
Looking up 200 rows of a range without index, reads the whole table
(i.e. the data file): about 1.000.000 * 100 bytes = ~100MB.
Reading them using only the index file ("using index") reads 1/10th of
that (10 bytes instead of 100 bytes per row), i.e. 10MB and one can
assume that this will also be faster about 10 times (this is specific
to this example, of course). That is why MySQL does this in your case.
Really making use of the index for the lookup, needs about 1 index
lookup for the first row, reading log2(1.000.000) =~ 20 index pages
(each 1KB usually) plus the 199 remaining rows, each 100 bytes, or 19
index pages (10 fitting in one 1KB page; in reality, it's less, let's
say 5). I did not consider the data file intentionally ("using index"
additionally, you know). So it reads less than 60KB, but needs
additional 20 disk seeks. That would be real good! ;-)
> mysql> explain select count(*) searchTotal from NS_articles nsa,
> NS_editors nse where find_in_set('approved', nsa.artFlags) > 0 and
> nsa.artEditor = nse.id;
> +-------+-------+---------------+-----------+---------+--------+------+-------------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
> +-------+-------+---------------+-----------+---------+--------+------+-------------+
> | nse | index | PRIMARY | PRIMARY | 32 | NULL | 21 |
> Using index |
> | nsa | ref | artEditor | artEditor | 32 | nse.id | 19 |
> where used |
> +-------+-------+---------------+-----------+---------+--------+------+-------------+
>
> What happened to the index on artFlags? The table link uses indexes
> which is good, but now artFlags is doing a raw table scan.
I presume it is because you do not have any key in NS_articles which
covers both, id and artFlags. I.e. for "using index" you would need a
combined key (id, artFlags).
But as explained above, you should get the real speed boost if you can
somehow manage to avoid the expression instead of only the column
name. Unfortuneately, that is not possible in the common case. If you
query very often based on this 'approved' flag, you may want to
consider to give it an own column (which should use indexes
appropriately).
For more details, see the manual sections about index usage and
optimization.
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
