MySQL Myisam Table Performance Problem Revisited

MySQL MyISAM table performance problem revisited

Did you try to enforce the use of the index? Like:

SELECT sourceid FROM page USE INDEX (sourceid_index)

Like sgehrig comments, check using EXPLAIN if the index is used? And share the result?

EXPLAIN select sourceid from page

It could also help to share the definiton of the indexes:

SHOW INDEX FROM page

MySQL MyISAM table performance... painfully, painfully slow

What's the data type and purpose of columnX in the userdata table? It should be noted that any text data type (i.e excluding char, varchar) forces any temporary tables to be created on disk. Now since you're doing a straight join without conditions, grouping or ordering, it probably won't need any temporary tables, except for aggregating the final result.

I think it would also be very helpful if you show us how your indexes are created. One thing to remember is that while InnoDB concatenates the primary key of the table to each index, MyISAM does not. This means that if you index column name and search for it with LIKE, but still want to get the id of the page group; Then the query would still need to visit the table to get the id instead of being able to retrieve it from the index.

What this means, in your case, if I understand your comment to apphacker correctly, is to get the name of each users pagegroups. The query optimizer would want to use the index for the join, but for each result it would also need to visit the table to retrieve the page group name. If your datatype on name is not bigger than a moderate varchar, i.e. no text, you could also create an index (id, name) which would enable the query to fetch the name directly from the index.

As a final try, you point out that the whole query would probably be faster if the mediumtext was not in the page table.

1. This column is excluded from the query you are running I presume?
2. You could also try to separate the page data from the page "configuration", i.e. which group it belongs to. You'd then probably have something like:
   
   
   • Pages
     
     
     • pageId
     • pageGroupId
   • PageData
     
     
     • pageId
     • data

This would hopefully enable you to join quicker since no column in Pages take up much space. Then, when you needed to display a certain page, you join with the PageData table on the pageId-column to fetch the data needed to display a particular page.

MySQL Query performance - huge difference in time

If it's a data problem, I can't tell you what the exact problem is, but here's my favorite strategy to solve this kind of problem:

Try to remove half of your joins. Repeat recursively, until the query runs fast. Then add half of the joins you removed in the last step... (This strategy will require much fewer steps than removing and adding join by join.)

Once you've found out the "bad" join, you can try restricting its values with an additional "where" clause, until the query runs fast again... In each step, always try to reduce the problem in half.

Note: It can very well be the case, that you get a lot more records for the intermediate results of your joins, even if the total amount of data is smaller in database B.

MySQL cannot convert its engine from MyISAM to ARCHIVE

Looks like it definitely is a PK problem since archive does not support primary keys. You can simply drop the primary key and it should work.

Refer to this post for examples on how to convert your db

---

Related Topics