[mythtv] innodb vs myism preformance

Mark Edwards irish at irishmark.co.uk
Tue Feb 22 18:16:01 UTC 2005


> > > Stick an EXPLAIN in front of the query
> > 
> > Great idea. I would never have thought of that, although I 
> don't know 
> > what to do with the output =) .
> 
> Your output isn't very disimilar than mine, I use one more 
> index, but it's on a small table so unlikely to cause the 
> discrepancies.
> I suggest running ANALYSE and OPTIMISE on all tables in your 
> DB. (I run them every morning @ 5am) Then check you are using 
> query cache etc.
> 
> Something is wrong with your DB, but I'm afraid I dunno what, 
> at least we've discounted Myth and indexes from your search...

Apologies for replying to myself.
I did some analysis on this query and have managed to get it so it's no
longer performing any full scans.
I'm a little nervous about submitting patches to add indexes tho, I can't
prove somewhere else won't be affected badly.

One problem is that some of the data types need adjusting as there are joins
between non-matching types occasionally.

AFAIK the only table that really shouldn't have much (if any) indexing is
the recordedmarkup table, most the other tables are hit by big complex
queries and do not have large numbers of inserts.

Heres my explain from the statement now, tho it only shaves my 0.67 secs to
0.51 secs!

+-------------+--------+-----------------------------+---------+---------+--
----------------------------------------+------+----------------------------
--+
| table       | type   | possible_keys               | key     | key_len |
ref                                      | rows | Extra
|
+-------------+--------+-----------------------------+---------+---------+--
----------------------------------------+------+----------------------------
--+
| capturecard | index  | PRIMARY,cardid              | PRIMARY |       4 |
NULL                                     |    3 | Using index; Using
temporary |
| cardinput   | ref    | cardid,sourceid             | cardid  |       4 |
capturecard.cardid                       |    1 |
|
| recordmatch | index  | PRIMARY,recordid            | PRIMARY |      16 |
NULL                                     |  165 | Using index
|
| record      | eq_ref | PRIMARY                     | PRIMARY |       4 |
recordmatch.recordid                     |    1 |
|
| program     | eq_ref | PRIMARY,id_start_end,chanid | PRIMARY |      12 |
recordmatch.chanid,recordmatch.starttime |    1 |
|
| channel     | eq_ref | PRIMARY,sourceid            | PRIMARY |       4 |
program.chanid                           |    1 | Using where
|
| oldrecorded | ref    | title,programid             | title   |     128 |
program.title                            |    3 |
|
| recorded    | ref    | programid,findid,title      | title   |     128 |
program.title                            |    5 |
|
+-------------+--------+-----------------------------+---------+---------+--
----------------------------------------+------+----------------------------
--+



More information about the mythtv-dev mailing list