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:
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| start | int(10) unsigned | | PRI | 0 | |
| duration | int(10) unsigned | | | 0 | |
| login | char(8) | | MUL | | |
| nas | int(10) unsigned | | PRI | 0 | |
| port | int(10) unsigned | | PRI | 0 | |
| ip | int(10) unsigned | | | 0 | |
| bytesrx | int(10) unsigned | | | 0 | |
| bytestx | int(10) unsigned | | | 0 | |
| speedtx | int(10) unsigned | | | 0 | |
| speedrx | int(10) unsigned | | | 0 | |
| telto | char(15) | | | | |
| telfrom | char(15) | | | | |
| multilinkid | int(10) unsigned | | | 0 | |
| sessionid | char(8) binary | | | | |
+-------------+------------------+------+-----+---------+-------+
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'
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.
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.
The complete query, with both the conditions, lasts almost 20 seconds
too, even if the result was only a few rows!
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?
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?
Is it possible to optimize the current behavior?
Thanks,
___________________________________________________
__
|- [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
