Hello mysql,
Running Linux 2.4.x SMP/innodb.
This query works OK.
mysql> explain SELECT cp.counter_id, tg.tbl, cs.host
-> FROM counter.table_groups AS tg, counter.counterproperty AS cp, counter.servers
AS cs
-> WHERE tg.tblid=cp.tblid AND tg.server=1 AND cs.SERVER_ID=tg.server limit 10;
+-------+-------+----------------+---------+---------+----------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+-------+----------------+---------+---------+----------+------+-------------------------+
| cs | const | PRIMARY | PRIMARY | 2 | const | 1 |
| |
| tg | ref | PRIMARY,SERVER | SERVER | 2 | const | 120 | where used
| |
| cp | ref | tblid | tblid | 2 | tg.TBLID | 302 | where used;
|Using index |
+-------+-------+----------------+---------+---------+----------+------+-------------------------+
3 rows in set (0.00 sec)
Now I'm trying to select data only for some of counter_id:
mysql> explain SELECT cp.counter_id, tg.tbl, cs.host
-> FROM counter.table_groups AS tg, counter.counterproperty AS cp, counter.servers
AS cs
-> WHERE cp.counter_id IN
-> (138520,49237,56459,63677,152964,25710,25712,79327,108442)
-> AND tg.tblid=cp.tblid AND tg.server=1 AND cs.SERVER_ID=tg.server;
+-------+--------+----------------+---------+---------+----------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+----------------+---------+---------+----------+------+------------+
| cs | const | PRIMARY | PRIMARY | 2 | const | 1 | |
| cp | range | PRIMARY,tblid | PRIMARY | 4 | NULL | 9 | where used |
| tg | eq_ref | PRIMARY,SERVER | PRIMARY | 4 | cp.tblid | 1 | where used |
+-------+--------+----------------+---------+---------+----------+------+------------+
3 rows in set (0.00 sec)
It still work and return correct result.
But if I use long list of counter id's (30.000 counters) then I get
other explain output:
table type possible_keys key key_len ref rows Extra
cs const PRIMARY PRIMARY 2 const 1
tg ref PRIMARY,SERVER SERVER 2 const 120 where used
cp range PRIMARY,tblid PRIMARY 4 NULL 30522 where used
And the result is wieard:
counter_id tbl host
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
135824 st74.g09 ss01.local
P.S I can provide a copy of tables if indeed.
--
Best regards,
Peter mailto:[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
