Hello.
On Thu, Apr 25, 2002 at 01:49:39PM +0200, [EMAIL PROTECTED] wrote:
[...]
> mysql> show index from cpu;
>
>+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
>Cardinality | Sub_part | Packed | Comment |
>
>+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | cpu | 1 | timecode | 1 | timecode | A |
>4199933 | NULL | NULL | |
> | cpu | 1 | timecode | 2 | systemid | A |
>4199933 | NULL | NULL | |
>
>+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> 2 rows in set (0.13 sec)
Are the pairs (timecode,systemid) unique? If so, tell MySQL so.
> ...and I execute a query like this (here with explain first) :
>
> mysql> explain SELECT substring_index(elisa.timecode,':',1),
> avg(elisa.usertime + elisa.systemtime) AS elisa,
> avg(asuncion10.usertime + asuncion10.systemtime) AS asuncion10,
> FROM cpu AS elisa, cpu AS asuncion10, cpu AS asuncion20
> WHERE elisa.systemid='44'
> AND asuncion10.systemid='47'
> AND substring_index(elisa.timecode,':',1) =
>substring_index(asuncion10.timecode,':',1)
> AND elisa.timecode > now() - INTERVAL 7 DAY
> AND asuncion10.timecode > now() - INTERVAL 7 DAY
> GROUP BY substring_index(elisa.timecode,':',1)
> ORDER BY substring_index(elisa.timecode,':',1);
>
>+------------+-------+---------------+----------+---------+------+--------+-----------------------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
>
>+------------+-------+---------------+----------+---------+------+--------+-----------------------------+
> | elisa | range | timecode | timecode | 8 | NULL | 612892 | where
>used; Using temporary |
> | asuncion10 | range | timecode | timecode | 8 | NULL | 612892 | where
>used |
>
>+------------+-------+---------------+----------+---------+------+--------+-----------------------------+
> 2 rows in set (0.02 sec)
Sorry, but the query and the explain don't fit together. Where is "cpu
AS asuncion20"? I presume that the part in the SELECT simply is left
by accident.
In this case, is MySQL's guess that it has to inspect about 612892
correct? It seems a bit high (more than 1/10th of the rows of the
table?) If not, try running ANALYZE TABLE.
If it is correct, the query will run forever, because you are going to
inspect 612892*612892 rows, including calling substring_index twice
that much.
Another thing: If you need that substring stuff regularly, your table
is not normalized enough and you should split timecode. This would
would enable MySQL to use indexes to solve the
substring_index(elisa.timecode,':',1) = substring_index(asuncion10.timecode,':',1)
part (which wouldn't call substring_index anymore)
> This query takes quite a long time to execute, and I'm sure it can
> be done more elegant and faster....just don't know how.
>
> Anyone ?
>
> It's MySQL 3.23.49 with InnoDB tables.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
