how to speed up mysql queries

mysql selects slow

[jantham]

I have a mysql select query that scans a range of data in a large table. It is very slow. I have created the necessary indexes for that query, optimized and analyzed the table already. Joins are by indexed columns.

Still, the mysql query is far too slow. Anyway to speed it up?

[yowza]

Could you post your query and table structure?

[PhraSEOlogy]

What is the query? Indexes? and range of data?
<edit>Yowza got there first</edit>

[jantham]

The table is something like this

Create table table1(
a varchar(100),
b varchar(100),
c char(1),
d varchar(255);

The query is

select a, b, c from table1 where b like '%happiness%' and a in ('algeria', 'china') and c = 'N';

There is an index on a and an index on c and an index on a and c but it is still slow.

After creating the index, table1 is analyzed and optimized.

[dive into perl]

jantham

From my understanding MySQL will only ever use 1 index during a query, thats why its usefull sometimes to use 1 index to index mulitiple columns.

From the manual :-

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

Plus MySQL will not use an index if a LIKE '%SEARCHSTRING%' is used.

[jantham]

Thanks.

[jamesa]

>> like '%happiness%

Regular indexes (for lack of a better term) won't help when you use a wildcard in the beginning like that. Try using a fulltext index instead.