silent-ion wrote on Tue, 09 November 2010 21:22

I have a strange website, which do a lot of queries of the same type: select * from table where a='$variable'

Do you have an index on the "a" column?

If you look at the create table statement (the output from SHOW CREATE TABLE tablename). Do you have a row that says something like:

... [PRIMARY] KEY 'something' ( 'a' ),...

If you don't then it means you don't have an index and that MySQL needs to scan the entire table to find records matching your condition.

You can create an index with something like:

ALTER TABLE table ADD INDEX table_ix_a(a);

That will create an index on the a column (withing the parenthesis), the table_ix_a is just a name of the index that you are creating.

Yes. It has the index.
explain select * from t1 where a = 0023;

+----+-------------+----------+-------+--------------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+-------+--------------------+---------+---------+-------+------+-------+| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |+----+-------------+----------+-------+--------------------+---------+---------+-------+------+-------+


And I also want to mention that this table is very small only 500 rows. But to generate one webpage - script does 40k queries from this table.

silent-ion wrote on Tue, 09 November 2010 22:40

And I also want to mention that this table is very small only 500 rows. But to generate one webpage - script does 40k queries from this table.

Why? What the heck happened to caching?
I know that you might not be able to answer that since it sounds like you didn't develop this site.
But it really sounds like the worst design decision I've heard in a long time if it means 40k queries to produce one page.

If this 500 row table is pretty static you could try the query_cache built into MySQL and see if it speeds things up for you. Since if you use the query cache you also avoid the parsing step etc from the normal query execution which could be a substantial amount of time if the queries are very frequent and the table is very small.

But seriously that application should be looked at if it needs 40k queries to produce one page.

Find a programmer to fix this for you. Retrieving every row 80 times (on average) with seperate queries is not efficient; you might be looking for a join.