Hi.
You didn't mention your MySQL version. I assume some 3.22.x?
This 30% reatio is based on empirical data, and on your system, it
seems to be not ideal.
On Sat, Mar 31, 2001 at 09:50:57PM -0000, [EMAIL PROTECTED] wrote:
[...]
> 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 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)
Did you run these statements several times, to rule caching out?
> Ok so obviously mysql's choice to not use indexes is completely silly.
Not, it isn't. You would be far more bothered if MySQL wouldn't make
this choice and therefore some other queries would get slower in an
order of magnitudes.
Another thing is, that the time difference you cite looks a bit off to
what I am used to. Maybe there is room to tune your system.
> Is there anyway for me to force it to change the 30% to 40%?
AFAIK, your only choice is to change it in the source and recompile.
> 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%.
Well, changing the table design due to a special case doesn't sound
like a reasonable measure to me.
> Or is that then gonna seriously affect other queries which go over
> the 30%.
Yes.
> Should I just always add a limit 1 statement since our queries are
> always based around the sum total of the rows.
No. The LIMIT trick has to be considered a bug and probably will be
(or already is) fixed in new versions.
> Can someone more experienced with index's please suggest to me the
> best way to go.
If you often run queries like the above, I would add an index on
(acct_start_time,acct_session_time)
Adding the selected value acct_session_time to the index will enable
MySQL to solve the query without even touching the data file and only
reading by index. IIRC, it will avoid full table scans at all, then,
but maybe only with version 3.23.x.
> Is there any other way I can bring the select times down. Less than one
> minute would be beautiful.
Well, you are selecting and evaluating almost a million rows. That
just takes its times. You may want to consider regularly building
summary tables which contains the same info group by days (or another
reasonable group). You will then need two queries each time: one on
the summary table and one on the current data, but it will be lighting
fast in comparison.
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
