optimize simple query

MySQL & MariaDB Other MySQL® Questions
1

Hi there.

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

too much such queries generating web page, so visitors have to wait for 2-3 minute.
Can I optimize such queries without programmer?
maybe place this table in memory?

2
[B]silent-ion wrote on Tue, 09 November 2010 21:22[/B]

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.

3

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.

4

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.