Help with Indexes, Unions and Joins greatly appreciated!

[pootlecat]

Hello everyone,

I have a fair sized table now (1,955,041 rows) and it currently has two indexes:
PRIMARY is the ID number
and
Keywords is a FULLTEXT index of the Keywords column (Text).

+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| ID                   | int(20) unsigned |      | PRI | NULL    | auto_increment |
| Disabled             | char(2)          |      |     | 0       |                |
| Keywords             | text             |      |     |         |                |
| Category             | varchar(100)     |      |     |         |                |
| Category2            | varchar(20)      |      |     |         |                |
| Category3            | varchar(20)      |      |     |         |                |
| Category4            | varchar(20)      |      |     |         |                |
 

Edited for readability - ronverdonk

In my first query type I check to see it isn't Disabled and then check the Keywords:

SELECT * FROM files WHERE Disabled='0' AND MATCH (Keywords) 
AGAINST ('+tree' IN BOOLEAN MODE) ORDER by ID;

In my second query type I do not have keywords but am rather checking to see if an image belongs to a particular category like so:

SELECT * FROM files WHERE Disabled='0' AND (Category='Food' 
OR Category2='Food' OR Category3='Food' OR Category4='Food') ORDER by ID

I am very much a beginner with MySQL. My question is, are there any other indexes (or anything else) that I can do that will speed up the results, some of which are appallingly slow!?

For the first query I have a feeling self-joining the table might help but it rather makes my head spin trying to figure out how that works. (I guess I really should take a course if I have to do much more of this stuff lol!)

I was playing with the UNION command for the second query and came up with this:

(SELECT * FROM files WHERE Disabled='0' AND Category='Food')
UNION
(SELECT * FROM files WHERE Disabled='0' AND Category2='Food')
UNION
(SELECT * FROM files WHERE Disabled='0' AND Category3='Food')
UNION
(SELECT * FROM files WHERE Disabled='0' AND Category4='Food')
 ORDER by ID;

If this is correct then should I also have 4 corresponding indexes?

Any help you can give me would be greatly appreciated!
Thanks,
Alison

[miller]

First query first: Are you sure that you have an index on "keywords". Your DESC table isn't showing one. Do a "SHOW INDEX FROM files;" to make sure.

Secondly, yes, the only way to increase the speed of the second query would be to add 4 individual indexes on each of the category columns. You can try this, but I would suggest that you limit the number of characters in each index so as to prevent the size of the index and there subsequent hit on update and insert performance:

ALTER TABLE files ADD INDEX Category (Category(5));
ALTER TABLE files ADD INDEX Category2 (Category2(5));
ALTER TABLE files ADD INDEX Category3 (Category3(5));
ALTER TABLE files ADD INDEX Category4 (Category4(5));

Finally, you may not have to split up your query with UNION's. MySQL may end up doing that translation for you. But to make sure, do an EXPLAIN call on your query to verify that indexes are being utilized.

And of course, this might be the point at which you should learn more about optimization of sql queries on your own. There's plenty to learn, but even a little bit would help, such as a healthy use of EXPLAIN each time you make a query.