Searching data in many mySql tables with the same structure

Question

I divided a table that had a huge amount of lines and now I have 16 tables that have the same structure. It works well when I try to find something in one of the tables, but when I don't know exactly where it should be, I must to search it in all tables.

I'm doing like this:

SELECT id, name, surname, age, sex, telephon, fax, email
FROM
    `out_sim_valuesmatrix_ep_ma13_1_16`,
    `out_sim_valuesmatrix_ep_ma13_2_16`,
    `out_sim_valuesmatrix_ep_ma13_3_16`,
etc
`    out_sim_valuesmatrix_ep_ma13_16_16`
WHERE
    `appln_idNum1` IN (24842422,24918676,24771783,24908804,24960166,25041955,25017764,25104985,25039175,25089121,24897200,25038905,24907508,24856413,24856413,25001485,24873551,24873551,24747119,25055354,25068104,24969433,24736299,24908799,24947820,24861202,24861202,25101573,24811406,24811406,24939062,25072049,24899024,24908795,25045775,24741197,25033220,24862936,24862936,24926838,24872619,24872619,25011020,24791353,24791353,24983796,24975187,25087564,25044301,24969428,24941046,24944919,24835727,24835727,25015010,25001957,24976200,25057258,25064419,25040849,25063554,25061255,24863241,24863241,24749445,24885880,24751246,24882026,25095717) // Here there are actually 1000 values!!!

but then I receive following message:

#1052 - Column 'appln_idNum1' in where clause is ambiguous

how should I write my query to find the right values?

When I do this https://stackoverflow.com/a/409720/3499881 it's very slow till I receive an answer.

This https://stackoverflow.com/a/21260560/3499881 would not be a good solution because my select should have 16 * 8 lines [16 tables * 8 columns]. I think it is too much, isn't it?

Any other possibilities?

Many thanks in advance!!!

Gordon Linoff · Answer 1 · 2014-06-29T20:41:20.103

If the tables have the same structure, then you want to use union all, not a comma in the from clause:

SELECT id, name, surname, age, sex, telephon, fax, email
FROM (select * from `out_sim_valuesmatrix_ep_ma13_1_16` union all
      select * from `out_sim_valuesmatrix_ep_ma13_2_16` union all
      select * from `out_sim_valuesmatrix_ep_ma13_3_16` union all
      . . .
      select * from `out_sim_valuesmatrix_ep_ma13_16_16`
     ) t
WHERE
    `appln_idNum1` IN (24842422,24918676,24771783,24908804,24960166,25041955,25017764,25104985,25039175,25089121,24897200,25038905,24907508,24856413,24856413,25001485,24873551,24873551,24747119,25055354,25068104,24969433,24736299,24908799,24947820,24861202,24861202,25101573,24811406,24811406,24939062,25072049,24899024,24908795,25045775,24741197,25033220,24862936,24862936,24926838,24872619,24872619,25011020,24791353,24791353,24983796,24975187,25087564,25044301,24969428,24941046,24944919,24835727,24835727,25015010,25001957,24976200,25057258,25064419,25040849,25063554,25061255,24863241,24863241,24749445,24885880,24751246,24882026,25095717)

The , does a cartesian product of all the tables, which is a lot of wasted effort. You just want all the rows, so use union all.

Thank you for your answer @Gordon-linoff. It worked, but takes a very long time... :-( Any faster solution possible? If I had only one table would be better? — Aloysia de Argenteuil, Jun 29 '14 at 20:31
@AloysiadeArgenteuil . . . You can move the `where` clause into each subquery. Some database engines will do that optimization for you, but MySQL doesn't — Gordon Linoff, Jun 29 '14 at 20:40

Searching data in many mySql tables with the same structure

1 Answers1