Hello.
On Sun, Mar 25, 2001 at 01:17:12AM -0000, [EMAIL PROTECTED] wrote:
> > -----Original Message-----
> > From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]]
[...]
> Here's from the real one :
>
> mysql> show index from proc;
> +-------+------------+-------------+--------------+-------------+-----------
> +-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
> | Cardinality | Sub_part | Packed | Comment |
> +-------+------------+-------------+--------------+-------------+-----------
> +-------------+----------+--------+---------+
> | proc | 1 | timecode | 1 | timecode | A
> | 4220 | NULL | NULL | |
> | proc | 1 | system_id | 1 | system_id | A
> | 1 | NULL | NULL | |
> | proc | 1 | processname | 1 | processname | A
> | 88 | NULL | NULL | |
> +-------+------------+-------------+--------------+-------------+-----------
> +-------------+----------+--------+---------+
>
> > A good index would probably be
> > (system_id,username,processname,timecode).
>
> The users hardly use 'username' in their where clause.
Then you could use (system_id, processname, timecode, username), of
course.
> That's why it's left out (the index started to get rather big also).
Well, that's an argument. You may want to consider a partial index on
username anyhow.
> > Maybe even
> > (system_id,username,processname,timecode,usertime,systemtime),
> > which should enable "using index" only.
>
> Why that, usertime & systemtime are data values, they are never used in any
> where clause. As far as I can see it's complete waste to put index on those.
If all columns of the SELECT clause are part of the index, MySQL will
solve the query only with reading from the index file and never touch
the data file itself.
Since the index is quite big already and usertime+systemtime only add
8 bytes per row, this may pay off regarding the speed win / size
increase.
> > You may want to use partial keys in order to reduce space
> > consumption, e.g.
> > Maybe even (system_id,username(5),processname(5),timecode),
>
> This is a no go since alot of processes are identical on the first 5
> characters.
> The username could be used, but I decided not to, since Oracle (which the
> solution also has to be compatible with) is smart enough to only store the
> characters in use. I actually think MySQL does the same.
MySQL only stores the bytes you use, but restricting it to 5 bytes (or
whatever you want), will never use more than 5 bytes.
For example, I have 291383 email addresses in one database with an
avarage length of 20.2 characters. Regarding the left hand characters,
they are different as follows:
1 47
2 1392
3 20791
4 78516
5 142787
6 191976
7 228078
and so on. This means, a key on only 5 keys, has only to values per
index entry (i.e. only half as good as a unique index) and needs 4.99 :-)
characters in avarages (just measured), i.e. one save about 5MB space
(out of 7MB) and now scale that up to some million rows. Okay, emails
are an extreme example. :-)
> > Depending on the Cardinality of the different columns you may want to
> > change the order to (username,processname,system_id,timecode)
> > or alike.
>
> Aha, I'll have to test more with the individual order of combined indexes to
> see what I can get from it.
Well, normally that is not really important, but in your case,
processname has a low cardinality and therefore one value has a lot of
matches in the index and it is worth a try to put it to the right.
[...]
> > No index is chosen, because you obviouly SELECTed a major part of the
> > table. This probably won't happen, if you got these 100 million rows
> > are talking about below (because you won't select about 20.000.000
> > rows, will you?).
>
> Well, that's the problem. I will ;-(
Well, in that case, no index will really help you (except the one I
cited above, which should avoid reading the data file, it could help a
bit). Selecting as much rows, just will take its time. :-/
[...]
> [clip]
> > > I have tried to combine the indexes, but then MySQL fails using them
> > > properly,
> >
> > Would you please elaborate on this? This is IMHO the main point of
> > your problem. You need these indexes.
>
> Problem is that MySQL seems to do a full table scan as soon as I start to
> combine the indexes, even after running optimize, analyze & sort after
> index.
Normally, this means, that the index did not really fit the query, but
without more information, I cannot say.
Did you keep the indexes you already have now? In that case, it should
use the other indexes, if it cannot use the combined index.
> This is my experience after looking at explain decisions and the time it
> takes to execute.
May I bother you to add the following indexes and post the EXPLAIN
after adding them:.
(system_id, processname, timecode)
(system_id, processname, timecode, username)
(system_id, processname, timecode, username, usertime, systemtime)
> Every explain ends up saying "where used" and the showing the full number of
> rows. This is in 3.22.32. My test setup is the latest, it might be different
> there.
Well, using two different systems isn't a good idea, except if you
plan to upgrade your 3.22.32 system, because if we find a solution on
your test setup (3.23.?), it won't behave the same on the older
MySQL. In most cases, 3.23 is faster than 3.22 (which is to be
expected).
Anothing thing: If you cannot get it work with combined indexes, can
you post some testcase (i.e. the SQL commands to build a sample
database), so that we can play around and see if we have more luck?
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
