MySql“准备”花费了太多时间

Question

I am new to MySQL performance analysis. I find a join query in my system becomes slow recently (3.52s for ~4 tables, each table has ~1000-2000 rows). And I viewed the performance analysis with phpmyadmin. The result is shown below:

1 Starting 170 µs
2 Opening Tables 44 µs
3 System Lock 7 µs
4 Table Lock 7 µs
5 Init 65 µs
6 Optimizing 20 µs
7 Statistics 92 µs
8 Preparing 31 µs
9 Creating Tmp Table 236 µs
10 Executing 2 µs
11 Copying To Tmp Table 68 µs
12 Optimizing 10 µs
13 Statistics 14 µs
14 Preparing 3.5 s
15 Creating Sort Index 2.6 ms
16 Copying To Group Table 15.9 ms
17 Sorting Result 2.1 ms
18 Sending Data 391 µs
19 End 7 µs
20 Removing Tmp Table 1.3 ms
21 End 3 µs
22 Removing Tmp Table 819 µs
23 End 10 µs
24 Query End 2 µs
25 Freeing Items 20 µs
26 Removing Tmp Table 6 µs
27 Freeing Items 365 µs
28 Logging Slow Query 3 µs
29 Cleaning Up 6 µs

I don't understand why the Preparing phase costs 3.5s. How can I reduce it?

Answer 1

I'm going to throw a wild guess in my answer, you probably don't use any index.

In SQL Optimization, Indexing is a broken cheat, like the konami-code in contra, or the doors in age of empire.

Anyway, try putting some indexing in your database, then come back to us, and be amazed by how fast your preparing phase goes now.