CrustyDOD

mysql> explain SELECT * FROM entries WHERE active = 1 AND points <= 2 ORDER BY date_added DESC; +----+-------------+------------+-------+--------------------------------+---------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+--------------------------------+---------+---------+------+------+-----------------------------+ | 1 | SIMPLE | entries | range | points, ind_active_points_date | points | 4 | NULL | 1 | Using where; Using filesort | +----+-------------+------------+-------+--------------------------------+---------+---------+------+------+-----------------------------+ 1 row in set (0.01 sec)

Oops, forgot to post it :S Sorry about that. mysql> explain SELECT * FROM entries FORCE INDEX (ind_active_points_date) WHERE active = 1 AND points <= 2 ORDER BY date_added DESC; +----+-------------+------------+-------+------------------------+------------------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+------------------------+------------------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | entries | range | ind_active_points_date | ind_active_points_date | 4 | NULL | 1 | Using where; Using filesort | +----+-------------+------------+-------+------------------------+------------------------+---------+------+------+-----------------------------+ 1 row in set (0.01 sec)

CREATE TABLE IF NOT EXISTS `entries` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `date_added` datetime NOT NULL default '0000-00-00 00:00:00', `source_url` varchar(200) collate utf8_unicode_ci NOT NULL, `views` int(11) NOT NULL default '0', `active` tinyint(1) NOT NULL default '0', `points` int(11) NOT NULL default '0', `approved_by` int(11) NOT NULL, `approve_date` datetime NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `points` (`points`), KEY `ind_active_points_date` (`active`,`points`,`date_added`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2065 ; Don't think indexes will be important here since i have 'range' on 1 field and order by on different field. As far as i know, this is a NO-GO situation and filesort will always be used.. Am i wrong?

Hey! Got a bit of a problem that i cannot solve. I've got a table, and i need to get some results from it. The problem is of course filesort. I know why filesort is in use but cannot get rid of it. Important fields: - active - points - date_added Index's are set.. SELECT ... WHERE active = '1' AND points <= '2' ORDER BY date_added DESC Of course the problem is that i want results that have 2 or less in points field, and use ORDER BY on different field. I'm stuck here.. I even tried with ALTER table to first change the order of the table, and then use the above query without ORDER BY. Since right now table is at 2000 rows only, ALTER table worked quite fast. I'm not too sure about it when it comes to 2M+ rows. I know why the problem is there, but since i'm not too advance with sql i can't solve it really. Any suggestions how to solve this? Even if 3 queries are needed i would be happy.

Yeah, its actually my bad. Before going live i wanted to see how queries will hold againts large amount of data, so i inserted fake data to tables and forgot to optimize. Shit happens i guess. Thanks for the GROUP_CONCAT solution!

Yeah. Running optimize solved it Takes half a second now.

That is exactly what i want! 1 problem tho. Limit was set to 5, tables contain (total records): CD table: 129,997 genre table: 21 CD_Genres: 324,516 Index keys are set to fields that i'm looking... Any ideas? Oh and i tried this on mysql 5 version

Hello! I have a slight problem that i just figured out. Since i'm naming fields in my own language, i'll just use a general example. Same structure tho. table 1: CD info (fields) - id - cd_title - date_added table 2: Music Genres (fields) - id - genre_name table 3: CD Genres (fields) - id - cd_id (ID of CD record) - genre_id (ID of genre record) Table 1 contains list of CD's and each CD has 1 or more genres. Is it possible to get genre_name for each record with 1, 2 or 3 queries in total? Basically table 3 is just a link between table 1 and table 2. Right now, i first get let's say 50 CD's from table 1 and then inside a loop i do another query that get's CD Genres. NOT ACTUAL CODE.. But it shows the current way "Select * from table1 limit 50" LOOP ( "select * from table3 left join table 2 on table2.id = table3.genre_id where table3.cd_id = 1" ) This is just bad! I get 51 queries per page load.

SELECT FROM ... WHERE console = 'PS2' AND difficulty = 'HARD' That's about it

Before mysql_query line put this: echo "select photo8url from properties where id='$id'"; Then you will see if $id even is set to something.

select photo8url from properties where id='$id' AND photo8url <> '' if "photo8url" contains something the mysql_num_rows will be 1 else 0

Yeah but that means that query will look like this: WHERE field NOT LIKE 'a%' AND field NOT LIKE 'b%' AND field NOT LIKE 'c%' ..all the way down to.. AND field NOT LIKE 'z%' That's a bit overkill isn't it?

Hey! I have a question that is a bit more related to mysql then PHP so i hope i didn't miss the board too much. 1st Question: I have an array of english alphabet and a foreach loop that goes over array 1 by 1. Inside loop i have mysql query that searches for records (20 records per letter) that start with n letter ... LIKE 'A%' .... The question is how on earth do i get records that DO NOT start with alphabet letter? Basically before A output is done i would like to get 20 records that start with 0-9, _, -, etc ANYTHING but those 26 alphabet letters. Hope its clear 2nd question, doing the above method it means that i will have to execute 26 + 1 non-alphabet mysql queries (each query gets 20 records) (since they are inside loop). Is there any other way to do this without so many queries?

Almost forgot about this Right, maybe i wrote this in the wrong way. 0 is allowed to be after the first digit! - 20 - 200162100 and so on.. All i want is that 0 is not in the first place but it can be anywhere else. Yeah i know about other options i can use but if possible i would like to do this with regex..

Ah i see, even this forums have this 'feature', pulling int out of string, if you put some letter behind numbers.... Would be better if it would throw out 0 rows instead of this.. thanks