Short version: I have 2 identical tables and I created a merge table to query them all
at once (v 3.23.47). I can select on only two of the four keys in the table.
Attempts to select data from the merge table "WHERE column = xxx" always return 0 rows
for the other two keys. Queries using "WHERE column LIKE xxx" do work, but of course
they're very, very slow. The problem seems to arise when the total line count of the
two tables reaches 20.
Long version: I have 2 tables that look like this (it's dhcp lease polling
information):
CREATE TABLE t1 (
ip_address_id INT UNSIGNED,
cm_mac_id MEDIUMINT UNSIGNED,
cpe_mac_id MEDIUMINT UNSIGNED,
hostname_id INT UNSIGNED,
starts DATETIME,
ends DATETIME,
KEY (ip_address_id),
KEY (cm_mac_id),
KEY (cpe_mac_id),
UNIQUE KEY (ip_address_id, starts)
) TYPE=MyISAM RAID_TYPE=STRIPED RAID_CHUNKS=2 RAID_CHUNKSIZE=1024;
I'm merging them together like this:
CREATE TABLE t_merge (
ip_address_id INT UNSIGNED,
cm_mac_id MEDIUMINT UNSIGNED,
cpe_mac_id MEDIUMINT UNSIGNED,
hostname_id INT UNSIGNED,
starts DATETIME,
ends DATETIME,
KEY (ip_address_id),
KEY (cm_mac_id),
KEY (cpe_mac_id),
KEY (ip_address_id, starts)
) TYPE=MERGE UNION=(t1, t2);
These queries work fine:
SELECT * FROM t_merge WHERE ip_address_id = 1234;
SELECT * FROM t_merge WHERE ip_address_id = 1234 AND starts = '2002-03-02 00:59:05';
SELECT * FROM t_merge WHERE ip_address_id = 1234 AND cm_mac_id = 5678;
SELECT * FROM t_merge WHERE ip_address_id = 1234 AND cpe_mac_id = 5678;
DELETE FROM t_merge WHERE cm_mac_id = 5678;
DELETE FROM t_merge WHERE cpe_mac_id = 5678;
These queries return 0 rows:
SELECT * FROM t_merge WHERE cm_mac_id = 5678;
SELECT * FROM t_merge WHERE cpe_mac_id = 5678;
These queries work correctly, but of course very slowly:
SELECT * FROM t_merge WHERE cm_mac_id LIKE 1234;
SELECT * FROM t_merge WHERE cpe_mac_id LIKE 1234;
In testing, the problem only arises when the total line count of the two mapped tables
reaches 20.
Anyone have any ideas?
- Matt
---------------------------------------------------------------------
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
