The query planner estimates that
each scan will return 21 rows.
The index scan is a bitmap scan, so instead of returning the rows themselves,
this step just generates a list of the matching rows to be retrieved later. The
width is listed as 0 bytes per row because the actual rows are not returned.
2. The BitmapOr step takes the bitwise OR of the three bitmaps returned from the
lower steps, effectively returning their union. It returns a bitmap itself, so the width
is still 0.
3. The Bitmap Heap Scan uses the bitmap to retrieve the actual rows from the
table. The number of rows stays the same, but the width is now 12.
4. A HashAggregate performs the grouping and processes the aggregate functions??”
the min( ) and count( ) functions in the SELECT clause.
Architectural Scalability | 173
We can see from this example that there are already sufficient indexes on the table,
and they are being used properly. This is probably as fast as the query will get given
the table size, so there is not much to be gained from more tweaks.
In MySQL, the syntax is the same (without the ANALYZE keyword), but the output is
substantially different. MySQL??™s syntax is more simplistic, but it is arguably easier to
understand.
mysql> EXPLAIN SELECT * FROM default_en_listingsdb ldb
INNER JOIN default_en_listingsdbelements ldbe
ON ldb.listingsdb_id = ldbe.listingsdb_id
WHERE ldb.listingsdb_id = 141054;
+----+-------------+-------+-------+----------------+----------------+-------+------+
| id | select_type | table | type | possible_keys | key | ref | rows |
+----+-------------+-------+-------+----------------+----------------+-------+------+
| 1 | SIMPLE | ldb | const | PRIMARY | PRIMARY | const | 1 |
| 1 | SIMPLE | ldbe | ref | idx_listing_id | idx_listing_id | const | 40 |
+----+-------------+-------+-------+----------------+----------------+-------+------+
2 rows in set (0.
Pages:
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269