Hello.
I'm unable to comment your decision about making a separate table
and how your data will be normalized after it. But, in my opinion,
if you're going to use only one letter for WHERE condition your index
will have a low cardinality (number of unique values) and optimizer
might prefer a full table scan. And indexes won't work with your
WHERE condition because you're using function from a column in
expression, not a column directly. Changing it to ' LIKE {letter}%'
probably can solve this. In your case having index only on first
character in the string makes sense for me.
"M.E. Koch" <[EMAIL PROTECTED]> wrote:
> Hello dear all-mighty list :)
>
> I do run mysql in 4.0.24 and have the following very slow query.
>
> select tbl3.colname from tbl
> .. (some left joins)
> where
> left(colname,1)="{letter}"
>
> the tbl.col has no index nore will it ever get one because auf
> the tbl structre. (this would make no sense becaus only 8tsd.)
> rows in the table total 82tsd rows have to get selected like
> this.
>
> As you might guess the query is much too slow.
>
> Now I have the chance (and time) to do the following.
>
> Doing a seperate table for this on col like
>
> +--------+-----------------+
> | id | colnames |
> +--------+-----------------+
>
> how would i have to index it? I think colnames should get
> a fulltext-field but how would it beccorect? Just one letter
> or do the whole field.
> colnames type = text (varchar might be ok with 255 chars).
>
> What would you suggest?
>
> ==============
> the other thing is that I would like to update to 4.1.x on
> my debian sarge. Has anybody done this yet?
>
> do I have to just: apg-get remove mysql ?
> and apg-get install mysql-4.1
>
> wich packages are needed.. will there be much mor performance?
>
>
> Bon Vibes and Thank you
>
> mathias
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
