0

I want to increase the speed of some database queries I am running on a MySQL MyISAM table that is one gigabyte in size.

The table has about 10 million rows and has about ten columns of INTs and VARCHARs. The table is used only for READs and there will be no updates or inserts into the table. The only query that I do on the table is:

SELECT name,age FROM myisamtable WHERE category1='example' AND (category2='example2' OR category4='example4') ORDER BY id DESC LIMIT 1000,2000

Should I convert the table to Memory for faster performance? (I don't care if I lose the table on system restart). Or is it better to keep it as MyISAM and increase some settings like the key buffer size, etc..?

trincot
  • 317,000
  • 35
  • 244
  • 286
eric
  • 1,453
  • 2
  • 20
  • 32
  • Personally, I'd recommend InnoDB. – iamandrus Apr 11 '11 at 22:31
  • @Itamake. Thanks. What is the benefit of InnoDB? – eric Apr 11 '11 at 22:51
  • clustered indexes = super fast queries... read http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html and http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601 – Jon Black Apr 11 '11 at 23:34

3 Answers3

2

If you put the whole table in memory, any rows you don't query will still be consuming space. I imagine it's unlikely you can tell for sure that all rows are always queried. OTOH, any you do query will be cached (as long as you allocate what would be required for the memory table to caching instead.

Memory tables are for really unusual circumstances; using them for manually allocated query caching is very unlikely to be productive.

I would expect this to be an easily testable situation too, since it seems to be a query problem you are currently encountering. How about another question describing the circumstances?

I question InnoDB. MyISAM tables have been wonderfully efficient for implicitly read-only tables in my experience. Where they suck is releasing and reusing disk blocks and twiddling associated indexes.

Temp tables are an antipattern. Search for query optimisation strategies and eliminating temp tables (auto or manual) is always one of the first priorities. When someone finds them helpful, it's usually an indication there are other important improvements to be made

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • this is all excellent advice. I wish I could give you more upvotes. Your comment about temp tables being an antipattern makes perfect sense, but at this point they were the fastest solution, so I went with that. (Query optimization will take some time, but is the long term solution, I know...) – eric Apr 12 '11 at 09:24
  • Can you post what indexes you have? There's no possible good reason for your query times if the table is properly indexed at 1G. – dkretz Apr 12 '11 at 16:33
1

As far as I know, the table stored in MEMORY will truncate if you run out of memory.

What you can do is create a temporary table with the subset defined on the WHERE statement and do the sequential queries using the temporary table. I've noticed a substantial increase of performance.

Raisen
  • 4,385
  • 2
  • 25
  • 40
  • Thanks Raisen. The temporary table was the best solution. It isn't the most elegant, but it improved performance by 10 times (in terms of CPU load). – eric Apr 12 '11 at 09:22
0

Using something like redis is perfect for this kind of key value store requirement.

martin blank
  • 2,184
  • 3
  • 17
  • 15