Hi all,

I'm using MySQL 3.23.54, running on a Sun system 1GHZ with 1MB RAM. My table is about 600,000 records (each is about 50 bytes). When I run the following query it takes 5 secs (!) and returns 226,000:

SELECT count(*),tran_type from logrecord WHERE device_id=4000 and tran_datetime between '2003-12-23 16:36:09' and '2004-03-23 14:31:28' GROUP BY tran_type

I've already tries using count(indexed_col) but it didn't help. Also, if it using an index or not doesn't change the time.

Any idea?

Thanks,

Tal

make indexes for columns:
device_id, tran_datetime and tran_type

These are search or sort columns...

I have the following index:

CREATE INDEX type_rep on logrecord(device_id, tran_type, onus, tran_datetime)

shouldn't this work? on the explain I see it gets this index and uses it, but it takes the same time with or without it....

multiple column index could not work for single column from this index.
Create indexes for each column. It may help.

That didn't do much... it takes the same time...

If you are using InnoDB table type, this is a well known problem that can't be fixed for now...

How can I know it is? Also, if so, how can I change it?

Well, if you need transations, you have to stay with InnoDb. But if you can live without transactions , then:

You can tell the type of your tables by looking at: SHOW TABLE STATUS;
You can change from InnoDb to MyISAM by executing: ALTER TABLE yourtablename TYPE=MyISAM;

Hope this helps

Thanks bstjean, but it didn't.... My tables are already MyISAM.... any more ideas anyone? please?

Do you have a primary key on this table?

Then try with count(*) and count(yourprimarykey) to test which one is the fastest

I have... Same time... I'm really clue less... and it is using the index (or so it says on the explain)...

Perhaps you show post a DESCRIBE of your table as well as a SHOW INDEX and the EXPLAIN of your query so we can understand better what is going on...

Ok, here it is:

The discribe of the table:

mysql> describe logrecord;
+---------------+---------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------------------+-------+
| device_id | decimal(7,0) | | | 0 | |
| device_type | decimal(2,0) | | | 0 | |
| inst | varchar(15) | | | | |
| onus | decimal(1,0) | | | 0 | |
| tran_type | varchar(4) | YES | | NULL | |
| tran_time | decimal(2,0) | | | 0 | |
| tp_fee | decimal(4,0) | | | 0 | |
| chp_fee | decimal(4,0) | | | 0 | |
| tran_datetime | datetime | | | 0000-00-00 00:00:00 | |
| amount_auth | decimal(15,0) | YES | | NULL | |
| void_code | decimal(4,0) | | | 0 | |
+---------------+---------------+------+-----+---------------------+-------+

indexes:

mysql> show index from logrecord;
+-----------+------------+----------+--------------+---------------+-----------+
-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-----------+------------+----------+--------------+---------------+-----------+
-------------+----------+--------+---------+
| logrecord | 1 | type_rep | 1 | device_id | A |
55 | NULL | NULL | |
| logrecord | 1 | type_rep | 2 | tran_type | A |
344 | NULL | NULL | |
| logrecord | 1 | type_rep | 3 | onus | A |
487 | NULL | NULL | |
| logrecord | 1 | type_rep | 4 | tran_datetime | A |
576585 | NULL | NULL | |
| logrecord | 1 | onus_rep | 1 | onus | A |
1 | NULL | NULL | |
| logrecord | 1 | onus_rep | 2 | tran_type | A |
15 | NULL | NULL | |
| logrecord | 1 | onus_rep | 3 | void_code | A |
224 | NULL | NULL | |
| logrecord | 1 | onus_rep | 4 | inst | A |
655 | NULL | NULL | |
| logrecord | 1 | onus_rep | 5 | tran_datetime | A |
576585 | NULL | NULL | |
| logrecord | 1 | onus_rep | 6 | device_type | A |
576585 | NULL | NULL | |
| logrecord | 1 | avrg_rep | 1 | device_id | A |
55 | NULL | NULL | |
| logrecord | 1 | avrg_rep | 2 | void_code | A |
1337 | NULL | NULL | |
| logrecord | 1 | avrg_rep | 3 | tran_type | A |
2200 | NULL | NULL | |
| logrecord | 1 | avrg_rep | 4 | amount_auth | A |
28829 | NULL | NULL | |
| logrecord | 1 | avrg_rep | 5 | tran_datetime | A |
576585 | NULL | NULL | |
| logrecord | 1 | act_rep | 1 | inst | A |
15 | NULL | NULL | |
| logrecord | 1 | act_rep | 2 | tran_datetime | A |
576585 | NULL | NULL | |
| logrecord | 1 | act_rep | 3 | device_type | A |
576585 | NULL | NULL | |
| logrecord | 1 | resp_rep | 1 | tran_time | A |
10 | NULL | NULL | |
| logrecord | 1 | resp_rep | 2 | inst | A |
95 | NULL | NULL | |
| logrecord | 1 | resp_rep | 3 | tran_datetime | A |
576585 | NULL | NULL | |
| logrecord | 1 | resp_rep | 4 | device_type | A |
576585 | NULL | NULL | |
+-----------+------------+----------+--------------+---------------+-----------+
-------------+----------+--------+---------+

and the explain:

mysql> explain SELECT count(*),tran_type from logrecord WHERE device_id=4000 and
tran_datetime between '2003-12-23 16:36:09' and '2004-03-23 14:31:28' GROUP BY
tran_type;
+-----------+------+-------------------+----------+---------+-------+--------+--
-----------------------+
| table | type | possible_keys | key | key_len | ref | rows | E
xtra |
+-----------+------+-------------------+----------+---------+-------+--------+--
-----------------------+
| logrecord | ref | type_rep,avrg_rep | type_rep | 8 | const | 260164 | w
here used; Using index |
+-----------+------+-------------------+----------+---------+-------+--------+--
-----------------------+

Just for the jolly factor, could you try:If that doesn't help, then try using myisamchk to force that index order on the data rows themselves.

-PatP

...That's where you're hanging out, traitor!