8

After much work I finally got a rather complicated query to work very smootly and return results very quickly.

It was running well on both dev and testing, but now testing has slowed considerably. The explain query which takes 0.06 second on dev and was about the same in testing is now 7 seconds in testing.

The explains are slightly different, and I'm not sure why this would be The explain from dev

-+---------+------------------------------+------+------------------------------
---+
| id | select_type | table      | type   | possible_keys           | key
 | key_len | ref                          | rows | Extra
   |
+----+-------------+------------+--------+-------------------------+------------
-+---------+------------------------------+------+------------------------------
---+
|  1 | PRIMARY     |  | ALL    | NULL                    | NULL
 | NULL    | NULL                         |    5 |
   |
|  1 | PRIMARY     | tickets    | ref    | biddate_idx             | biddate_idx
 | 7       | showsdate.bid,showsdate.date |   78 |
   |
|  2 | DERIVED     | shows      | ALL    | biddate_idx,latlong_idx | NULL
 | NULL    | NULL                         | 3089 | Using temporary; Using fileso
rt |
|  2 | DERIVED     | genres     | ref    | bandid_idx              | bandid_idx
 | 4       | activehw.shows.bid           |    2 | Using index
   |
|  2 | DERIVED     | artists    | eq_ref | bid_idx                 | bid_idx
 | 4       | activehw.genres.bid          |    1 | Using where
   |
+----+-------------+------------+--------+-------------------------+------------

and in the testing

| id | select_type | table      | type   | possible_keys           | key         | key_len | ref                          | rows   | Extra                                        |
+----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     |  | ALL    | NULL                    | NULL        |    NULL | NULL                         |      5 |                                              |
|  1 | PRIMARY     | tickets    | ref    | biddate_idx             | biddate_idx |       7 | showsdate.bid,showsdate.date |     78 |                                              |
|  2 | DERIVED     | genres     | index  | bandid_idx              | bandid_idx  |     139 | NULL                         | 531281 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | artists    | eq_ref | bid_idx                 | bid_idx     |       4 | activeHW.genres.bid          |      1 |                                              |
|  2 | DERIVED     | shows      | eq_ref | biddate_idx,latlong_idx | biddate_idx |       7 | activeHW.artists.bid         |      1 | Using where                                  |
+----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+
5 rows in set (6.99 sec)

The order of the tables is different, even though the queries are exactly the same. Is this what would cause the slowdown? if so, how would I fix it? The dev is windows, testing is centOs. both running same version of mysql 5.0, and like I said, testing was running perfectly and I haven't made any structural changes to the database.

I ran mysqlcheck and all tables came back ok.

Riedsio
  • 9,758
  • 1
  • 24
  • 33
pedalpete
  • 21,076
  • 45
  • 128
  • 239

5 Answers5

7

MySQL looks at the data in the tables as well as the query itself to decide which execution plan to use.

If the data is the same in both databases, I'd suggest using ANALYZE or OPTIMIZE on all the tables in your query.

Greg
  • 316,276
  • 54
  • 369
  • 333
  • i ran an analyze and everything came back ok. ran optimize anyway and everything was fine. – pedalpete Feb 26 '09 at 16:42
  • I had a similar problem. Mysql on 2 server both with debian 10. Same database (mysqldump on first server loaded on second server). Explain on same query was different and execution on one was 100 times slower. Otimize on the tables worked fine and corrected the issue. Mysql sometimes makes me.... – Donapieppo Dec 03 '20 at 09:14
5

The first plan doesn't use index on shows.

If you are sure this index will help you, force it:

SELECT ...
FROM ..., shows FORCE INDEX (biddate_idx) , ...
WHERE ...

Meanwhile, collect statistics for your tables.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • you amaze me! i am curious as to why one db wouldn't use the index while another would (or why it did originally but not anymore). Any ideas? – pedalpete Feb 26 '09 at 16:38
  • Optimizer uses statisics that may be collected in one table but not in another. – Quassnoi Feb 26 '09 at 16:39
3

I would try regenerating statistics and rebuilding the indexes for all the tables and see if that fixes your problem - it's likely that is why the plans would be different.

There are lots of other things it could be (memory, disk, os differences, other loads, etc) but I'm assuming those probably aren't the issue since you mentioned that it ran fine before.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
0

Are you sure these are from the same query? The explains aren't just slightly different, there are considerable differences between them:

  1. The WHERE clause is hitting different tables (artists on dev, shows on testing)
  2. The number of rows it's hitting in genres is different (2 on dev, 531281 on testing).
  3. Other miscellaneous differences between the first and second explains (stuff in EXTRA mainly).
Powerlord
  • 87,612
  • 17
  • 125
  • 175
  • yeah, they are the same query. I copied it directly from the testing (which was slow) and pasted it into dev (which was fast). – pedalpete Feb 26 '09 at 16:44
0

We just experienced a very similar problem with a newly built master taking several minutes to execute the same query that old master (with less power) completed in a fraction of a second. We ran repair table quick on two of the myisam tables in the query and now the new master executes the query at least as fast as the old one.

Thanks!