Hi Benjamin,
Sorry for the late reply and not giving you any more replies..
Was away some days...
Thanks for all the help on my query question..
Indeed like you said, the query now probably is as fast as he will ever get...
And that is fast indeed, there isn't any waiting period anymore...
I also had some other queries which were quite handy but was rarely used because of
the slow speed (few seconds)..
But this has changed now... Amazing what a few indexes (+ query optimisations) can
do...
I also grabbed my MySQL manual and looked at the chapter performance.... There is
quite a nice example in which they also add
indexes using the explain
method.. The eq_ref was indeed described as the (second) best linking option.. It's
the best type of datarows we which I was
linking..
I underestimated the power of indexes... Also found some other tables in which I could
add them and speed things up A LOT..
Still aren't sure how the database can built a index which speed things up so much,
but there is a story in the manual which
described making a index of the first 3 chars
out of a varchar(50) column... When a query is made the index table, this is scanned
and all records which don't match the first 3
chars can be eliminated
(which speed things up a lot..)
This also makes sence, because when doing the LIKE 'abc%' sql syntax an index can be
used, when you do LIKE '%abc%' index is useless
(if I understand the manual
correctly....)... I also saw an example where it should be possible to make a index in
which you decide yourself how many chars are
indexed..??
Powerful stuff... I looks that the speed of your database is 30% design en .70%
placing indexes...
Thanks a lof for the help... Still need to learn some stuff, but I think that I know
now how to optimize the query to about 97%....
Bye Bye
David Bouw
> > 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..
> > 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!! :-)
> Hm. Well now I am angry with myself... My very first idea was that the
> EXPLAIN should not show 'ALL' as link method for all the joins and
> that this could be due to the column types, but I use LEFT JOIN
> rarely, therefore I am not familiar with them and so I discarded that
> idea quickly again, without trying out.
>
> This means, I have to correct a former statement from myself, namely
> that LEFT JOIN requires full table scans always. These are not needed
> and LEFT JOINs are usually simply slower, because they do not
> eleminate as much rows as a normal join from the result set, and the
> next join in the queue will have to do more matches therefore. This
> can multiply itself quite fast.
>
> [...]
> > I enclosed the original gif picture of the explain command used in the original
>version, and in my current fast 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.
>
> Yes. And the current version is quite as fast as one can get, I think.
> "eq_ref" means that it links the two tables with a unique key,
> e.g. knowing that there will be one match at most. "ref" means the
> same as "eq_ref" except that there can be more than one match (the key
> is not unique).
---------------------------------------------------------------------
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
