pootlecat
Members-
Posts
13 -
Joined
-
Last visited
Never
Everything posted by 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) | | | | |