Hi Benjamin... > > 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. Yes, I understood that clearly what you said! I meant by screaming out that it was a Varchar, was that the difference is huge for the same data.. (Using INT and Varchar(50) for the same data is quite dumb... ) Well, I must say that I am a bit shocked on the moment.... After I changed the Varchar(50) to an INT I retried (my) shorter query... The results came back immediatly!! (I couldn't clock it..) Previously it was 4 seconds.. Well, O.K, so far so good...!!! I retried the old original query, the one we started out with, with left joins... This took about 15 seconds.. Hmm. Not bad, this was more than 1 minute... Well, O.K, then I retried your straight join version.. About 6 seconds also not bad... (Looking what we had..) After this I start checking all my columns.. With this I found a column in the brandname table which was an MEDIUMINT in stead of a INT.. Converted this, then I did something which I should have had done long before.. I added indexes to all the columns which are used to link to other data.. I removed all indexes on columns which were already a primary key.. I placed quite a lot of indexes (but not more than necessary = max 3 per column and at least 1) Well, then I tried all the queries again!! All queries including the original left join version can't be clocked by hand... !!! I will have to make a small PHP script to time it for me in microseconds!! :-) For people reading this thread it emphasizes how important indexes are!!! (And also the matching data-types on joins..) Like I said initially, Indexes and Joins are not that well known by me.. But I see that they are very important.. I can see that *all* my bigger queries are running faster now that I have places some extra indexes on the most important columns.. It's unbelievable that this can make such huge differences.. I now still use a column which contains a varchar string to be linked.. (luckily now both columns are a varchar of the same length + there are indexes)... This can still be optimized.. Further I could place an index on the date column.. But I won't use this query that much so I would rather not waste any space to this... What I have done now is to put an index on both tables.. Maybe it's possible to get the same results by only placing the index on the table you are linking to...??? But I would rather keep an index on both tables... Because in a lot of cases I also sometime need to link back to the other table in another type of query.... I enclosed the original gif picture of the explain command used in the original version, and in my current fast version.. Sadly I see I cut of a small piece in the old version.... I still have troubles reading the column, but I can see eq_ref which looks to me that the database can optimize quite well looking at the text: ALL which you saw on the old version which probably means that the database has to do a full record scan to the 'to be linked' table for each record in the data table. Well, looks like some time to grab my printed MySQL manual!!! Thanks for all the help and suggestions.. Things are a lot better, and I still have a lot to learn, but I know a bit more what to look for... Bye Bye David > > 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 > > 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.
--------------------------------------------------------------------- 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
