On Thu, Aug 21, 2003 at 04:58:47PM -0700, Steven Roussey wrote:
> > No, the contents can be of mixed case. Where does that leave things?
>
> **Index the length of the entire column.** It then should not need to
> have to do the filesort. Actually the binary option would not have
> really helped. The explain should say 'Using Index'. Get back to me on
> this and tell me the results.
Huh, I was told the exact opposite, that if most of the
entries are smaller than the maximum length of the field, you
should use an index about the size you expect most entries to
be. Why would you ever use a shorter index than the full column
length if it led to such performance degradation?
In any case, I reindexed cg.cw to the length of the entire column,
and the result is...
GOD! OK, sorry, I wasn't quite expecting this:
mysql> SELECT cw FROM cg WHERE cw BETWEEN 's' AND 'szzz' ORDER BY cw LIMIT 3000,5;
+---------------------+
| cw |
+---------------------+
| sacrifice hit |
| sacrifice play |
| sacrifice the earth |
| sacrifice throw |
| sacrifice to |
+---------------------+
5 rows in set (0.02 sec)
Wow!
But what's the explanation for this huge improvement? Again, I
was always told the opposite, and the Manual itself says: "If
it's very likely that a column has a unique prefix on the
first number of characters, it's better to only index this
prefix. MySQL supports an index on a part of a character
column. Shorter indexes are faster not only because they take
less disk space but also because they will give you more hits
in the index cache and thus fewer disk seeks." (At sec. 5.4.2.)
> > In a working environment I'd never be querying on this table alone,
> > it would always be joined in to other tables that would limit things
> > in some way, but these don't seem to be affecting things. The
> suggestions
> > other people have made to try to get it to do the smaller queries
> first
> > don't seem to be having much effect, unfortunately.
>
> Optimize the join once you know how to optimize its parts. One thing at
> a time.
Hmm. When I returned to the multiple-table query that started
this thread, but with the full-column index, it took a
staggering 1m 15s; rerunning it speeded it up to 3.51 sec
(the original was 7.30 sec), but still nothing like the
improvement that the single table change made just above. The
explain looks like this:
mysql> EXPLAIN SELECT cg.cw FROM cg,q,cit,sref
-> WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id
-> AND cg.cw BETWEEN 't' AND 'tzzz'
-> AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
-> ORDER BY cg.cw
-> LIMIT 1000,10;
+-------+--------+-----------------+---------+---------+-------------+--------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+--------+-----------------+---------+---------+-------------+--------+-------------+
| cg | range | q_id,cw | cw | 101 | NULL | 190550 | Using
where |
| q | eq_ref | PRIMARY,cit_id | PRIMARY | 4 | cg.q_id | 1 |
|
| cit | eq_ref | PRIMARY,sref_id | PRIMARY | 4 | q.cit_id | 1 |
|
| sref | eq_ref | PRIMARY,cd | PRIMARY | 4 | cit.sref_id | 1 | Using
where |
+-------+--------+-----------------+---------+---------+-------------+--------+-------------+
4 rows in set (0.00 sec)
Where do I go from here?
And thanks for all the thought people have been putting into this.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
