This email was delivered to you by The Free Internet,
a Business Online Group company. http://www.thefreeinternet.net
---------------------------------------------------------------
Hi,
I realise this mailing list must get bombarded with questions about
indexes but can someone please spare a moment to help me out here.
Here is my database and index's and table size.
mysql> desc accounting;
+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| NAS_IP_Address | varchar(16) | | PRI | | |
| Called_Station_Id | varchar(22) | | PRI | | |
| Acct_Session_Time | int(11) | YES | | NULL | |
| Acct_Start_Time | timestamp(14) | YES | PRI | NULL | |
| Acct_Stop_Time | timestamp(14) | YES | PRI | NULL | |
| Timestamp | timestamp(14) | YES | | NULL | |
| GW_IP_Address | varchar(16) | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> show index from accounting;
+------------+------------+----------+--------------+-------------------+---
--------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+----------+--------------+-------------------+---
--------+-------------+----------+--------+---------+
| accounting | 0 | PRIMARY | 1 | NAS_IP_Address | A
| 10 | NULL | NULL | |
| accounting | 0 | PRIMARY | 2 | Called_Station_Id | A
| 616136 | NULL | NULL | |
| accounting | 0 | PRIMARY | 3 | Acct_Start_Time | A
| 2464545 | NULL | NULL | |
| accounting | 0 | PRIMARY | 4 | Acct_Stop_Time | A
| 2464545 | NULL | NULL | |
| accounting | 1 | date | 1 | Acct_Start_Time | A
| 1232272 | NULL | NULL | |
| accounting | 1 | date | 2 | Acct_Stop_Time | A
| 2464545 | NULL | NULL | |
+------------+------------+----------+--------------+-------------------+---
--------+-------------+----------+--------+---------+
6 rows in set (0.01 sec)
mysql> select count(*) from accounting;
+----------+
| count(*) |
+----------+
| 2464545 |
+----------+
1 row in set (0.00 sec)
=======================
Ok so I am trying to do Acct_Session_Time sum's under Time ranges. for
example,
select sum(acct_session_time) from accounting where acct_start_time between
'20010319000000' AND '20010325235959';
mysql> explain select sum(acct_session_time) from accounting where
acct_start_time between '20010319000000' AND '20010325235959';
+------------+------+---------------+------+---------+------+---------+-----
-------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+------------+------+---------------+------+---------+------+---------+-----
-------+
| accounting | ALL | date | NULL | NULL | NULL | 2464545 |
where used |
+------------+------+---------------+------+---------+------+---------+-----
-------+
1 row in set (0.00 sec)
So mysql has decided not to use the date index. A quick count(*) under the
range tells me,
mysql> select count(*) from accounting where acct_start_time between
'20010319000000' AND '20010325235959';;
+----------+
| count(*) |
+----------+
| 793651 |
+----------+
1 row in set (1 min 22.97 sec)
this is 32% of the total size so mysql chooses to ignore my index.
However by adding a limit statement to the query to force use of the index
the following results appear when using the index and when not using the
index
mysql> select sum(acct_session_time) from accounting where acct_start_time
between '20010319000000' AND '20010325235959';
+------------------------+
| sum(acct_session_time) |
+------------------------+
| 54794345 |
+------------------------+
1 row in set (5 min 54.17 sec)
mysql> select sum(acct_session_time) from accounting where acct_start_time
between '20010319000000' AND '20010325235959' limit 1;
+------------------------+
| sum(acct_session_time) |
+------------------------+
| 54794345 |
+------------------------+
1 row in set (1 min 46.64 sec)
Ok so obviously mysql's choice to not use indexes is completely silly. Is
there anyway for me to force it to change the 30% to 40%?
We keep some of our data in an archive table because initial logic told us
that a smaller table would mean faster selects. However with index's this
isn't true right? so should I copy the archive data back into the current
table and that way the query is more likely to be less than 30%. Or is that
then gonna seriously affect other queries which go over the 30%. Should I
just always add a limit 1 statement since our queries are always based
around the sum total of the rows. Can someone more experienced with index's
please suggest to me the best way to go.
Is there any other way I can bring the select times down. Less than one
minute would be beautiful.
Richard Clarke // icespirit // [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
