Members   Search   Help    Register    Login   Home

Show: Today's Messages  :: Show Polls :: Message Navigator

faster not using Index???? [message #601] Wed, 17 January 2007 12:13  mysted Messages: 3 Registered: January 2007 Junior Member One table VAL_FAKTA_VH containing 5.000.000 rows and one table VAL_DIM_AVTAL containing 34 rows. The query takes 15 s using Index and 7 s not using index, how is this possible!!! It is not a question about IO, no IOWAIT but alot of CPU 99% (on machines with single CPU and 49.9 om machines with 2 CPU, MySQL does not seems to utilize both CPU:s) HW 2*2Ghz CPU 8G RAM I have used huge-conf with the following add: join_buffer_size 131072 key_buffer_size 3221225472 tmp_table_size 67108864 read-only and some more DB: VAL_FAKTA_VH.MYD ~ 600M VAL_FAKTA_VH.MYI ~ 400M VAL_DIM_AVTAL.MYI ~ 2M VAL_DIM_AVTAL.MYD ~ 1M mysql> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V force index(Index__avtalid) , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = 'Huvud' group by avtal.avtal; +----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+ | 1 | SIMPLE | avtal | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where | | 1 | SIMPLE | V | ref | Index__avtalid | Index__avtalid | 5 | carro.avtal.avtalid | 138929 | Using where | +----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+ 2 rows in set (0.00 sec) This query takes 15 s If I ignore index: mysql> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V ignore index(Index_2, Index__avtalid) , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = 'Huvud' group by avtal.avtal; +----+-------------+-------+--------+---------------+------- --+---------+-----------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+------- --+---------+-----------------+---------+-------------+ | 1 | SIMPLE | V | ALL | NULL | NULL | NULL | NULL | 4723596 | | | 1 | SIMPLE | avtal | eq_ref | PRIMARY | PRIMARY | 4 | carro.V.avtalid | 1 | Using where | +----+-------------+-------+--------+---------------+------- --+---------+-----------------+---------+-------------+ 2 rows in set (0.00 sec) mysql> SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V ignore index(Index_2, Index__avtalid) , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = 'Huvud' group by avtal.avtal; +-------+-------------------+ | avtal | sum(utfallkronor) | +-------+-------------------+ | Huvud | 20396597.210337 | +-------+-------------------+ 1 row in set (7.98 sec) I have tried this on several machines with different architectures and different configurations the result is the same .... please help me with this problem? /Ted Report message to a moderator

	faster not using Index???? By: mysted on Wed, 17 January 2007 12:13
	Re: faster not using Index???? By: Peter on Wed, 17 January 2007 12:33
	Re: faster not using Index???? By: mysted on Thu, 18 January 2007 04:41
	Re: faster not using Index???? By: Peter on Thu, 18 January 2007 05:12
	Re: faster not using Index???? By: mysted on Thu, 18 January 2007 05:50
	Re: faster not using Index???? By: Peter on Thu, 18 January 2007 07:48

Previous Topic:	Replication : How to load balance queries ?
Next Topic:	Out of memory when query big table

Goto Forum:

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]