[Searching and Indexes]

Thanks again for all your help! :)
Everything seems to work ok at the moment. I am just going to do a little testing with EXPLAIN and timing the results to see how it is all working.

One other quick question - I've taken to sorting by "rand(2)" to mix things up a little and I was wondering if this is known to cause a performance hit with larger tables/results? Is there a better way to "randomise" results?

[Searching and Indexes]

Thanks - that seems to work really well.

Now I am just left with one more problem. The second query just got a lot more complicated and I have tried going the UNION route with it too but I am not sure that is the best way. This is what I have now:

[code](SELECT * FROM files WHERE Disabled='0' AND Colour='1' AND MATCH (Keywords)
AGAINST ('cat' IN BOOLEAN MODE) AND Category='Animals')
UNION
(SELECT * FROM files WHERE Disabled='0' AND Colour='1' AND MATCH (Keywords)
AGAINST ('cat' IN BOOLEAN MODE) AND Category2='Animals')
UNION
(SELECT * FROM files WHERE Disabled='0' AND Colour='1' AND MATCH (Keywords)
AGAINST ('cat' IN BOOLEAN MODE) AND Category3='Animals')
UNION
(SELECT * FROM files WHERE Disabled='0' AND Colour='1' AND MATCH (Keywords)
AGAINST ('cat' IN BOOLEAN MODE) AND Category4='Animals') ORDER by rand(2)[/code]

Colour can only be 1 or 0 so I assume I should just ignore this too?

[Searching and Indexes]

Is this what you mean?

(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

and I should have 4 corresponding indexes?

[Searching and Indexes]

That sounds more interesting :)
It is rather over my heard though - could you possibly give me an example?
Thanks!

[Searching and Indexes]

Thanks! I'll try that :)

[Searching and Indexes]

I changed things around a little. Here is the one query and its explain statement:

Current Indexes
===============
Keyname Type Cardinality Action Field
PRIMARY  PRIMARY 1788750      ID 
disab_cat  INDEX 11      Disabled  Category 10 
disab_cats  INDEX 15      Disabled  Category 10  Category2 10  Category3 10  Category4 10 
Keywords  FULLTEXT 1      Keywords 

Explain Results
===============
EXPLAIN SELECT * FROM files_webgraphics WHERE Disabled='0' AND Category='Arrows' ORDER by ID

id  select_type  table            type  possible_keys        key      key_len  ref        rows  Extra 
1  SIMPLE      files_webgraphics ref  disab_cat,disab_cats disab_cat 14      const,const 121438 Using where; Using filesort

Other Info
==========
Disabled is either 1 (disabled) or 0 (not disabled).
Category is one of the following:
  Animations, Buttons, Rules
  Arrows, Bullets, Textures
  Backgrounds, Objects, Words

[Update auto_increment Column]

The problem I have is that I split one massive table into 3 smaller ones and I want the auto_increment column to start from '1' on 'line 1' and increase from there on all 3 tables. I was just hoping there would be some simple command I could do that would recalculate all the auto_increment values for me.

[Update auto_increment Column]

I just noticed your reply to me - thanks!
So doign this command will update every row in the table?

[Changing Auto-Increment Value]

I don't know about elioncho but I want to keep my data and just redo the auto_increment column :)

[Changing Auto-Increment Value]

It seems you want to know the same thing as me :)
I haven't found anywhere on Google that suggests it is even possible but hopefully someone here will know!

[Update auto_increment Column]

I have a table where the auto_increment column starts at 1237333 and I want to change it so that it starts at 1 instead. Is there an easy way to do this at all?
Thanks for any help you can give me!

[Searching and Indexes]

Thanks for your reply :)
I am just splitting the main table into 3 separate ones so that there will not be quite so much data in them. Once I have done that I will update my post.

[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;[/CODE]

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[/CODE]

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!?

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