Geek Answers Handbook

Query results have been loaded into the 200K database for too long, speed up prompts?

I have a sql statement where I join about 4 tables, each with 200K rows. The request is executed, but it continues to freeze. When I make a join on 3 tables instead, it returns rows (takes about 10 seconds). Any suggestion why? suggestions to speed up?

Thank!

the code

SELECT *
FROM equipment, tiremap, workreference, tirework
WHERE equipment.tiremap = tiremap.`TireID` AND 
      tiremap.`WorkMap` = workreference.`aMap` AND
      workreference.`bMap` = tirework.workmap
LIMIT 5

ps

and if that helps, I use sql alchemy to generate this code, sqlalchemy code for this

query = session.query(equipment, tiremap, workreference, tirework)
query = query.filter(equipment.c.tiremap == tiremap.c.TireID)
query = query.filter(tiremap.c.WorkMap==workreference.c.aMap)
query = query.filter(workreference.c.bMap == tirework.c.workmap)
query = query.limit(5)
query.all()
+5
source share
4 answers

Make sure you have indexes:

  • equipment (bus)
  • bus (TireID)
  • tiremap (WorkMap)
  • workreference (aMap)
  • workreference (bMap)
  • tire fitting (workmap)

: , .

SQL , , . SELECT * FROM tab1, tab1, .

SELECT * FROM person WHERE lastname LIKE 'V%' , , lastname , .

, , , , , . . . WHERE ( - , ), , .

MySQL . , .

, , , , . , , MySQL .

, , MySQL , , , .

, . PHPMyAdmin . . Navicat Lite - .

+5

4 . , "" .

:

.

A, B C:

A = rowA1
    rowA2
    rowA3;
B = rowB1
    rowB2
    rowB3;
C = rowC1
    rowC2
    rowC3;

, , :

rowA1 - rowB1 - rowC1
rowA1 - rowB1 - rowC2
rowA1 - rowB1 - rowC3
rowA1 - rowB2 - rowC1
rowA1 - rowB2 - rowC2
rowA1 - rowB2 - rowC3
rowA1 - rowB3 - rowC1
rowA1 - rowB3 - rowC2
rowA1 - rowB3 - rowC3
...
...
...
rowA3 - rowB3 - rowC1
rowA3 - rowB3 - rowC2
rowA3 - rowB3 - rowC3

27 . :

rowA1 - rowB1 - rowC1
rowA2 - rowB2 - rowC2
rowA3 - rowB3 - rowC3

, 3 . 4 , .

, - ?

-, , , 5 . , , SELECT .

, . , , , :

SELECT *
FROM (SELECT * FROM equipment LIMIT 5) e, tiremap, workreference, tirework
WHERE e.tiremap = tiremap.TireID AND
      tiremap.WorkMap = workreference.`aMap` AND
      workreference.`bMap` = tirework.workmap

, , 3 , 4. , , . "", 5 . , , , .

, , , :

SELECT * FROM equipment 
INNER JOIN tiremap ON equipment.tiremap = tiremap.TireID
INNER JOIN workreference ON tiremap.WorkMap = workreference.aMap
INNER JOIN tirework ON workreference.bMap = tirework.workmap
LIMIT 5

: (mySQL, ), .

:

SELECT * FROM tirework, 
   (SELECT * FROM workreference, 
       (SELECT * FROM tiremap,
           (SELECT * FROM equipment) e
        WHERE e.tiremap = tiremap.TireID) t
    WHERE t.WorkMap = workreference.aMap) w
WHERE w.bMap = tirework.workmap
LIMIT 5

! , . , , , .

.

+1

, , , , . , , , .

0

SQL "EXPLAIN PLAN" "EXPLAIN", , , . , .

0

More articles:


All Articles