MySQL table in memory

[guest2013-1]

I know you can create a table in MySQL with the "=MEMORY" which denotes it should load in memory (duh)

I'm thinking of implementing this into a search stored routine I wrote.

Do you think the following would work?

*check if table exists*
*create table if not*
*load data from myisam table into memory table*
*don't load anything if it's already created*

and then reference the in-memory table instead of referencing the myisam table.

The products table (which this is for) won't really change (maybe once a week) and i was wondering if this will speed up searches done if I load all 6 tables I have in memory (all of them inner join's) ?

 

[Other Pineapple Smurf]

Bit of an overkill considering that the DB is already optimised to cache frequently accessed tables and results (cursors). Rather look at optimising the schema and the amount of memory MySQL server has access to.


EDIT: How many rows are in each table?

 

[DarkStreet]

Apparently memcache is great for optimisation, can't speak from experience though.

 

[bin3]

It's very seldomly worth it.

Databases are designed to retrieve data as efficiently as possible. In my experience you will get way more performance by reviewing your database model and reviewing your indexes.

The only time I have ever had to use in memory tables were doing intensive data processing across gigabytes or terabytes of data.

IN ALL OTHER CASES I had way better performance benefit by reviewing the data structures used and ensuring proper indexes were in place.

 

[hof]

You could do it. But if you have the memory available, it'd be way simpler to just increase your buffers.

If you do do it, remember that the MEMORY engine uses HASH indexes by default not B-trees, which could affect your performance in certain situations. So set them explicitly.

I gather you know this already but to make sure, the MEMORY engine doesn't load a table into memory, the table structure is stored on the disk and the table data is kept in memory. If MySql or your server shuts down all the table data will be lost, in which case you'd need to rebuild the MEMORY table.

Memcache would work if you had frequently requested queries for which you could store the results - it has little use for structured table data.

How many rows/columns are you dealing with? Can you post a DESCRIBE table and an EXPLAIN of a slow query? Reviewing your index strategy will almost always shave off some time.

 

[James]

Well, as bin3 suggested I would first make sure the appropriate indexes are used first, if not add the indexes and optimise the queries. Loading a table into memory sounds like a overkill to me. Optimise then if that fails throw the kitchen sink at it, not the other way around. Memcached could also help you out a great deal in regards to caching which can be deployed alongside the optimisations.

 

[guest2013-1]

dequadin hit the nail right on the head. I'm a consultant for the client who has commissioned his website by another company who did the programming. Currently there's several things I could do to the actual structure of the database itself to improve performance significantly. However, I'm pretty inexperienced when it comes to MySQL itself. I did push up the cache/buffer's MySQL is allowed to use (we're running a server with 6gb RAM dedicated just to this 1 site, MySQL db is less than 2gb at this stage and it's not using 1gb of RAM yet). By review of the indexes and learning EXPLAIN/DESCRIBE etc, I could get the search query roughly 3000% faster than what it was running at. Searches now, on average, takes about 5 seconds.

However, since I want it FASTER (and seeing as though that query is on 3 million rows or less, we're planning to have 6+million soon) I was thinking loading it directly into memory would give it a boost.

I would love to change the structure of the DB (which, each table that joins on the product table has more or less the same amount of records in, because it was designed one-to-one and no normalization), but if I can squeeze a second or two out of it still, by launch, the client would be happy (he's already very happy, but I'm unhappy with the speed)

So while he then has his website running, I can then do with the time to actually change things and design it properly. I must say, since I've started to work with MySQL, I'm liking it more and more

 

[Other Pineapple Smurf]

Are you doing partial searches or trying to match the full contents of the field?

ie SELECT * FROM table_a WHERE field_a LIKE '%bob%'

or

SELECT * FROM table_a WHERE field_a ='Bob';

 

[guest2013-1]

It's a mix, but if you boil it down to the core: select field_a,field_b, etc from table_a where field_a like '%bob%' is correct assumption. I also do an order by the date (client request) which incurs the "temporary table" thing in the EXPLAIN. But at least it's not using filesort or any other weird **** anymore.

I had it do where match field_a against ('bob') but that query was actually slower than the normal like (I did hundreds of tests) so the fulltext indexing didn't help much there. I did optimize the indexes though from a tutorial on hackmysql.com (?) which helped a lot, including a stored routine used as a "static" query coming through (with just the variables changing) it was went to a solid 5 second run or so.

 

[DarkStreet]

This article provides some nice info on profiling your queries: http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

 

[Other Pineapple Smurf]

We uses partitioning at work to improve the performance of our one database. Has millions of rows in it.

http://dev.mysql.com/tech-resources/articles/performance-partitioning.html