1 index Per table per query = SLOW QUERY!! Pls help

[jpo245]

Hi everyone

I have a query on 4 tables (via various joins).
MYSQL automatically uses uses primary keys for the joins, so that part is superb.

So for example, TABLE A uses A.id primary key as index for the join.

However, because of this,
if I do an ORDER BY DATE on table A, MYSQL resorts to using filesort. (EXTREMELY SLOW!!!), because (if im not wrong?) u can't use more than 1 index per table.

THis happens even though the DATE column is indexed.

Is there any workaround for this via unions or subselects or something?

Appreciate any advice on which direction i should head.

Many thanks!

 

[TonyGroves]

You can have as many indexes as you like on a table. If you have an index on DATE (not a great field name, change it if you can), then that index would be used if MySQL decides that using it would be beneficial.

If you're still having speed problems, maybe you could post the text of your query, and also the results of "SHOW CREATE TABLE tablename" for each table involved.

 

[jpo245]

Hi tony, i understand that u can specify as many indexes per table as u want.
However, when a query is run, from what i understand, MYSQL only allows you to use ONE index per table.

So tahts the source of my problems...

 

[TonyGroves]

When you write a query, you don't (normally) specify any indexes; you just tell MySQL what joins to do, what ordering to use, and so on. Then MySQL will check the database to see what indexes are available, and use whichever ones it decides it needs.

Again, if you can let us see your query and the structure of the relevant tables, it might be easy to see where the problem lies.