There are a lot of the same table (about 100) structure, including a device id field and built the index, now need a query about 2000 device id, how to query the data more quickly in the 100 table accords with a condition? I query now is
SELECT * FROM table_name1 where (in device id1, id2 equipment,... , equipment id2000)
UNION ALL
SELECT * FROM table_name2 where (in device id1, id2 equipment,... , equipment id2000)
.
UNION ALL
SELECT * FROM table_name100 where (in device id1, id2 equipment,... , equipment id2000)

But writing the query efficiency is too slow, often require several minutes, excuse me, how to optimize the better,
Each table 1, 2, about one million data,
Using a PostgreSQL database, but feel the man section is too little, so can only be sent to the plate,

CodePudding user response:

Try to find the values in a table, then the JOIN see efficiency

CodePudding user response:

The estimates that it is difficult to optimize the left index, the 100 * 2000 records of IO in this, need time,

CodePudding user response:

reference 1st floor ZJCXC response:

try to check the values in a table, then the JOIN see efficiency

On the temporary table, because the device id is the search term, the user may choose 1, 2, a device id check, may choose 1000, 2000 or so, language is the most

CodePudding user response:

refer to the second floor liuzhijian2008x response:

this estimate is difficult to optimize the, the index, the 100 * 2000 records of IO right this, need time,

That do not add index will be faster

CodePudding user response:

Not add index go full table is slower, the key is overhead IO

CodePudding user response:

reference baidu_17319793 reply: 3/f

Quote: refer to 1st floor ZJCXC response:

Try to find the values in a table, then the JOIN see efficiency

On the temporary table, because the device id is the search term, the user may choose 1, 2, a device id check, may choose 1000, 2000 or so, language is the most

Of course the temporary table