Hi.
On Mon, Jul 23, 2001 at 02:33:21PM +0200, [EMAIL PROTECTED] wrote:
> Hi Benjamin...
>
> I am not sure, but it looks that I (sometimes) get an email back
> telling me that your email address isn't working..
> >recipient [EMAIL PROTECTED] does not exist, verify e-mail address, please
Hm. But I get your mails also directly?!? Could you please send me
privately a complete copy of such an error mail? Thank you.
> I still want to reply on you other mail, but your email just showed me something
>awful!!
>
> > > table type possible_keys key key_len ref rows Extra
> > > Edit Delete co ALL PRIMARY,id 2596 where used
> > > Edit Delete cpo ALL 4431 where used
> >
> > That's bad. No index will be used. The only reason that comes to my
> > mind is that co.id and cpo.orderid are not of the same column type, or
> > if they are, not of the same length. Could you check this?
>
> I checked the tables.... And I got a cold feeling!!!
> The table orderid is an Varchar(50)... :-( !!!
>
> Wondering why it is so slow.. ???
> How I missed it, I don't know...
>
> I do know that the ordernr field which I used to link to my articles
> is a varchar and that this was a design error when I made this
> database almost 2 years ago... But the orderid I can convert without
> a problem to an INT because
>
> all data only are numbers... :-(
It's not so important, that orderid is not of type VARCHAR (although
better), but more important, as I said, that co.id and cpo.orderid are
of the same type. If co.id is INT, than, of course, it would be ideal
to convert cpo.orderid to INT, too.
> I quickly checked my other tables for the same errors, but I don't
> see it.. :-) I do see though that I sometimes use INT and sometimes
> MediumINT as a field type while it will probably be better to choose
> one datatype and keep using that...
Yes. Especially, if you an use older MySQL version, because those had
a problem to use indexes if the column type was not exactly the same.
Btw, if you are done and when the above query runs blindingly fast ;),
please retry with your original query, it should be also a lot faster,
now.
Bye,
Benjamin.
---------------------------------------------------------------------
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