The 3 table and index query found a table do not index the why.

SQL statement execution:
select * from lib a,down b,more c where a.id=b.mid and a.id=c.mid

explain
+----+-------------+-------+--------+----------------+----------------+---------+-------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------+----------------+---------+-------------+-------+-------------+
| 1 | SIMPLE | c | ALL | moremid_index | NULL | NULL | NULL | 49018 | |
| 1 | SIMPLE | b | ref | downsmid_index | downsmid_index | 4 | films.c.mid | 1 | |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | films.b.mid | 1 | Using where |
+----+-------------+-------+--------+----------------+----------------+---------+-------------+-------+-------------+


The 3 table structure
A ID is the primary key index
B mid and C mid have established the index BTREE
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| down | 0 | PRIMARY | 1 | id | A | 49018 | NULL | NULL | | BTREE | |
| down | 1 | downsmid_index | 1 | mid | A | 49018 | NULL | NULL | | BTREE | |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+-------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| more | 0 | PRIMARY | 1 | id | A | 49018 | NULL | NULL | | BTREE | |
| more | 1 | moremid_index | 1 | mid | A | 49018 | NULL | NULL | | BTREE | |
| more | 1 | morepush_index | 1 | push | A | 2 | NULL | NULL | YES | BTREE | |
+-------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Started by Jessie at February 01, 2016 - 3:21 PM

Abiao and C tables have a look whether statements connected field data type is not consistent

Posted by Cheryl at February 11, 2016 - 4:01 PM

Int is a type of

Posted by Jessie at February 26, 2016 - 4:32 PM

Type of modified other fields in the database development have not modified the field the index ID mid do not know to have an effect

Posted by Jessie at December 18, 2016 - 1:30 PM

Use the more table separate index is successful if and other table combined index failure

Posted by Jessie at January 02, 2017 - 2:08 PM

Not what the problem ah. where a.id=b.mid and a.id=c.mid
Always from a table full scan.

Posted by Earl at January 14, 2017 - 3:07 PM

Well I know later, also want to understand
Discovery is the process of long time not own MySQL


Found that a single select table like is slow with the order by then more slowly me order field to join index found that speed is more slow unexplained
The select statement
select * from lib order by time limit 10

Here the time with index index more slowly than do not know what it looks like a lot of people say the basic order plus will accelerate time int types

Posted by Jessie at January 15, 2017 - 3:16 PM