mysql中a b为什么是假,当联接另一个表B时，为什么Mysql对表A使用全表扫描？

最新推荐文章于 2022-07-02 08:00:00 发布

真魅

最新推荐文章于 2022-07-02 08:00:00 发布

阅读量109

点赞数

文章标签： mysql中a b为什么是假

I have a table A and a table B. I am selecting information from table A where I need only the information from table A where tabel B joins table A. I have indexes on columns that are joint and on the WHERE clause.

This is the select code:

SELECT *

FROM tableA

INNER JOIN tableB AS tableB

ON tableB.id = tableA.id

AND tableB.type = 'car'

When I use explain I can see that mysql is using a full table scan for all the rows in tableA. Further it properly uses the index to join tableB.

So what Mysql seems to be doing is scan all the rows in talbeA to see if there is a match with tableB. However as I have indexes on id of both tables I don't expect a tablescan. As my table has 50.000 rows this query is taking a few seconds (which is really too long for my application).

This is the explain:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE tableA ALL tableA.id NULL NULL 49898

1 SIMPLE tableB eq_ref tableB.type tableB.type 4 1 Use WHere

My question: How can I optimize this query and indexes such that Mysql directly selects those rows from tableA that have a match with tableB without scanning all rows in talbeA?

解决方案