Hi all, I recently had cause to drop and re-create a merge table that maps 80 or so tables (I'd added an index to all the tables whilst they were mapped, so causing me to get incorrect results. Duh!). However, upon re-creating it, I find that I'm now not getting any results back (or only one or two rows) from any queries I'm doing on the merge table that use indexes from the mapped tables. Queries on columns without an index give correct results. Below is an example that shows where it's going wrong: mysql> select count(*) from eventlog_36; +----------+ | count(*) | +----------+ | 389959 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from eventlog_all where bannerid = 36; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.01 sec) (The table 'eventlog_36' is one of the tables mapped to 'eventlog_all' which holds only records with a 'bannerid' column value of 36.) They don't match, so it's clearly not correct! It seems as though it's not looking at the indexes of the mapped tables correctly, although I have no idea why! If it helps, below is what I'm using to create the merge table. All the field and key declarations are exactly the same, apart from not declaring 'eventid' as a primary key like in the mapped tables (because there will be clashes otherwise). CREATE TABLE eventlog_all ( eventid int unsigned NOT NULL, bannerid int unsigned NOT NULL, impression datetime NOT NULL, click datetime, ipaddr int unsigned NOT NULL, browser char(255) NOT NULL, KEY (bannerid), KEY (impression) ) TYPE=MERGE UNION=(eventlog_11, <...many other tables...>, eventlog_81); Can anyone help me figure out what's going on here? Regards, ------------------------------------------------ Basil Hussain ([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
