[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