Fred van Engen wrote:
>
> Hi,
>
> On Fri, Mar 23, 2001 at 07:46:48PM +0100, Federico Giannici wrote:
> > We have a table where we register the data of all the modem connections
> > of the users of a small ISP.
> >
> > The table contains 2 million records and has the following structure:
> >
>
> ...
>
> What indexes does the table have? Looking at your description below
> I would guess at least an index on start and an index on login.
>
> SHOW CREATE TABLE tblname;
CREATE TABLE `accountings` (
`start` int(10) unsigned NOT NULL default '0',
`duration` int(10) unsigned NOT NULL default '0',
`login` char(8) NOT NULL default '',
`nas` int(10) unsigned NOT NULL default '0',
`port` int(10) unsigned NOT NULL default '0',
`ip` int(10) unsigned NOT NULL default '0',
`bytesrx` int(10) unsigned NOT NULL default '0',
`bytestx` int(10) unsigned NOT NULL default '0',
`speedtx` int(10) unsigned NOT NULL default '0',
`speedrx` int(10) unsigned NOT NULL default '0',
`telto` char(15) NOT NULL default '',
`telfrom` char(15) NOT NULL default '',
`multilinkid` int(10) unsigned NOT NULL default '0',
`sessionid` char(8) binary NOT NULL default '',
KEY `login`(`login`),
PRIMARY KEY (`start`,`nas`,`port`)
) TYPE=MyISAM PACK_KEYS=1
> Also, which version of MySQL do you use?
MySQL 3.23.32 on OpenBSD 2.7 i386.
> > According to us there is a bad optimization with queries like the
> > following one:
> >
> > SELECT * FROM accountings WHERE start >= UNIX_TIMESTAMP('2001-03-23')
> > AND login = 'anyname'
> >
>
> What does EXPLAIN say? I.e.
+-------------+------+---------------+-------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------------+------+---------------+-------+---------+-------+------+------------+
| accountings | ref | login,PRIMARY | login | 8 | const | 2428 |
where used |
+-------------+------+---------------+-------+---------+-------+------+------------+
> > If we issue a query with only the first part of the condition ("SELECT *
> > FROM accountings WHERE start >= UNIX_TIMESTAMP('2001-03-23')") the
> > result is almost immediate.
> >
>
> Depending on your indexes, MySQL will scan only the records that were
> added since the 23rd. Because you probably insert the records more or
> less on start time, they should be close together, which makes this fast.
>
> > If we issue a query with only the second part of the condition ("SELECT
> > * FROM accountings WHERE login = 'anyname'") the result is given after
> > about 20 seconds.
> >
>
> I guess you have an index on login as well, but if there are many records
> for this login (you keep a long history?), MySQL needs to fetch all these
> records. These are spread all over the database file, which can make this
> even slower than the number of records would suggest.
>
> > The complete query, with both the conditions, lasts almost 20 seconds
> > too, even if the result was only a few rows!
> >
>
> MySQL uses the wrong index. EXPLAIN may show why it does that.
Does the previous EXPLAIN shows the reason?
Perhaps because possible_keys lists "login" before "PRIMARY"?
> > I don't know why the second condition is much slower than the first one,
> > even if both the columns are indexed. Perhaps because "login" is a MUL
> > index?
> >
>
> Is there anything special about this login? Is it used more often
> than average?
This behaviour occurs with all the logins that have a good presence in
the table. The result is almost immediate with the logins that have
almost no records, but they are not the average...
> > Anyway, the most important question is another. If the result of the
> > first condition is only a few rows, we expected that the combination of
> > both conditions was not sensibly slower than the first condition alone.
> > Why were we wrong?
> >
>
> I've only seen this on MERGE tables. Do you use them?
No.
> > Is it possible to optimize the current behavior?
> >
>
> Yes, you can use this:
>
> SELECT * FROM accountings USE INDEX (myindex)
> WHERE start >= UNIX_TIMESTAMP('2001-03-23') AND login = 'anyname'
>
> Replace myindex with the index on start.
Wow, it worked!
The query time sensibly decreased specifing "USE INDEX(PRIMARY)".
I wasn't able to find the documentation about the "USE INDEX()"
parameter of the SELECT command in the on-line manual...
> Regards,
>
> Fred.
>
> --
> Fred van Engen XO Communications B.V.
> email: [EMAIL PROTECTED] Televisieweg 2
> tel: +31 36 5462400 1322 AC Almere
> fax: +31 36 5462424 The Netherlands
Thank you!
___________________________________________________
__
|- [EMAIL PROTECTED]
|ederico Giannici http://www.neomedia.it
___________________________________________________
---------------------------------------------------------------------
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
