pootlecat

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?

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?

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?

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

Thanks! I'll try that :)

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

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.

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

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

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!

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!

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.

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