Is there any way my table structure and access times can be improved?

[HowdeeDoodee]

Is there any way I can improve anything below to get quicker access times?

 

I just ran optimize table which did not help.

 

Here is the query run in phpMyAdmin.

 

SELECT * FROM `View2_DTTENNFT` WHERE MATCH(Topic, Subtopic, Theswords) AGAINST ('apples' IN BOOLEAN MODE) AND MATCH(Topic, Subtopic, Theswords) AGAINST ('johnny' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

Here is the access time.

 

24 seconds

 

Here is the table structure.

 

Keyname  Type  Cardinality  Action  Field

Source             INDEX 29693  Edit Drop Source 3

Lnum                INDEX 29693  Edit Drop Source 3

Topic             FULLTEXT 29693  Edit Drop Topic

Subtopic FULLTEXT 29693  Edit Drop Topic

Theswords FULLTEXT 29693  Edit Drop Topic

 

Statements  Value

Format dynamic

Collation latin1_swedish_ci

Rows 29,693

Row length ø 4,916

Row size ø 7,054 B

 

Thank you in advance for any replies.

[Wildbug]

It may help to combine the two words, "+johnny +apple", into a single MATCH...AGAINST query.

[HowdeeDoodee]

Thanks for the response. However, using one statement resulted in an access time of 40.4610 sec. compared the 24 seconds shown above.

 

Things are not looking good at the moment.

 

[fenway]

You need to have a single fulltext index across all 3 columns -- not 3 separate indexes.

[HowdeeDoodee]

Thanks fenway. I have that, I did not know how to display it. I had to copy and paste unformatted, etc. etc.

[fenway]

Thanks fenway. I have that, I did not know how to display it. I had to copy and paste unformatted, etc. etc.

OK... you can always post the show indexes output as well.  What does EXPLAIN have to say?