Web Analytics Made Easy -
StatCounter Searching and Indexes - CodingForum

Announcement

Collapse
No announcement yet.

Searching and Indexes

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Searching and Indexes

    Hi,

    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).

    In my first query type I also check two other fields: Disabled and Toplevel

    Code:
    SELECT * FROM files WHERE Disabled='0' AND TopLevel='clipart' AND MATCH (Keywords) AGAINST ('+tree' IN BOOLEAN MODE) ORDER by ID;
    In my first query type I do not have keywords but am rather checking to see if an image belongs to a particular category like so:

    Code:
    SELECT * FROM files WHERE Disabled='0' AND TopLevel='clipart' AND (Category='Food' OR Category2='Food' OR Category3='Food' OR Category4='Food') ORDER by ID
    My question is, are there any other indexes (or anything else) that I can do that will speed up the results?

    +----------------------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------------+------------------+------+-----+---------+----------------+
    | ID | int(20) unsigned | | PRI | NULL | auto_increment |
    | Disabled | char(2) | | | 0 | |
    | TopLevel | varchar(50) | | | | |
    | Keywords | text | | | | |
    | Category | varchar(100) | | | | |
    | Category2 | varchar(20) | | | | |
    | Category3 | varchar(20) | | | | |
    | Category4 | varchar(20) | | | | |
    Last edited by pootlecat; Oct 8, 2006, 01:14 AM.
Working...
X