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;
or
SHOW INDEX FROM tblname;
Also, which version of MySQL do you use?
> 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.
EXPLAIN SELECT * FROM accountings WHERE start >=
UNIX_TIMESTAMP('2001-03-23') AND login = 'anyname'
> 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.
> 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?
> 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?
> 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.
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
---------------------------------------------------------------------
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
