[mythtv] innodb vs myism preformance

Mark irish at irishmark.co.uk
Wed Feb 23 09:10:30 UTC 2005


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Daniel Manjarres wrote:
| New resuts:
|
| mysql> CREATE INDEX title ON recorded (title);
| Query OK, 738 rows affected (0.27 sec)
| Records: 738  Duplicates: 0  Warnings: 0
|
| mysql> CREATE INDEX findid ON recorded (findid);
| Query OK, 738 rows affected (0.25 sec)
| Records: 738  Duplicates: 0  Warnings: 0
|
| reduced the giant query from 19.19 seconds to 5.90 seconds. Now that's
| what I'm talking about!
|
| My EXPLAIN output is pretty different from your still. Don't know yet
| if adding indexes will improve things much.
|
| Thanks, a lot man.
|
|
+-------------+--------+------------------------+----------+---------+------------------------------------------+------+-----------------+
| | table       | type   | possible_keys          | key      | key_len |
| ref                                      | rows | Extra           |
|
+-------------+--------+------------------------+----------+---------+------------------------------------------+------+-----------------+
| | record      | ALL    | PRIMARY                | NULL     |    NULL |
| NULL                                     |  356 | Using temporary |
| | cardinput   | ALL    | NULL                   | NULL     |    NULL |
| NULL                                     |    1 |                 |
| | recordmatch | ref    | recordid               | recordid |       5 |
| record.recordid                          |    9 | Using where     |
| | program     | eq_ref | PRIMARY,id_start_end   | PRIMARY  |      12 |
| recordmatch.chanid,recordmatch.starttime |    1 |                 |
| | channel     | eq_ref | PRIMARY                | PRIMARY  |       4 |
| program.chanid                           |    1 | Using where     |
| | capturecard | eq_ref | PRIMARY                | PRIMARY  |       4 |
| cardinput.cardid                         |    1 | Using index     |
| | oldrecorded | ref    | title,programid        | title    |     128 |
| program.title                            |    7 |                 |
| | recorded    | ref    | programid,title,findid | title    |     128 |
| program.title                            |   21 |                 |
|
+-------------+--------+------------------------+----------+---------+------------------------------------------+------+-----------------+

Another to try would be recordid on the record table.
Make sure they're all the same type as the others (recordmatch.recordid
etc)  unsigned int(10)

Then submit a patch to dbcheck.cpp ;)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQFCHEiGyPEuXJ2RxGYRAhV+AJkBQFNz5qtkOedaP7PtLz1gn1KaDACfe8TL
rcZKOWCrPlh0VjqwKUlBKAQ=
=45ti
-----END PGP SIGNATURE-----


More information about the mythtv-dev mailing list