Bad MySQL performance for quite simple select

Question

I've got a problem with perfomance of my mysql queries. I've got a very big table

create table query(
    id  Integer,
    session Integer,
    time    Integer,
    name    Integer,
    region  Integer);

Volume of data - 2 gb .I've made index on "name" - 7 gb.

My queries look like:

select count(id) from query where name=somevalue;

I wouldn't add any new data, and I used standard "my-huge.cnf". Still, I spend about 4-5 seconds per query, I'm going to do about 9-10*45000 queries. Which options should I change to increase speed, if my computer has 2gb memory.

Answer 1

A count(*) may run marginally faster.

select count(*) as rowcount from query where name=somevalue;

Also you may consider caching the counts in a separate table and querying from that.

Answer 2

If you're never changing the data, then you should consider running the queries for all possible names (SELECT DISTINCT(name) FROM query) once, then storing the value of COUNT() in a cache. For that purpose, you could create a table cache with name and total as columns, and populate it with the results of running SELECT name, COUNT(*) AS total FROM query WHERE name = 'name' for each name.

You will then simply SELECT total FROM cache WHERE name = 'name', which will be very fast.