
binindex
Members-
Posts
26 -
Joined
-
Last visited
Never
Everything posted by binindex
-
need help splitting where 2 carriage returns exist
binindex replied to binindex's topic in Regex Help
ok does anyone know how to grab all sentences that are all caps. I know preg_match_all but can't figure out the regex for it to stop at the end. There are not '.' at the end. -
need help splitting where 2 carriage returns exist
binindex replied to binindex's topic in Regex Help
ok I added {3} to the back of yours and I think it is working. Will test it out and let yea know. Thanks for helping me. -
need help splitting where 2 carriage returns exist
binindex replied to binindex's topic in Regex Help
ok I tried your exact code and that works. When I plug it into mine it does not. Does have anything to do with me reading the whole file in as one line with file_get_contents? I am totally lost here now. Does anyone know a text editor that will show returns on the screen when editing the file. -
I have a file that has multiple parts in it. each part is seperated by 2 carriage returns. I read the whole thing into one variable then do a split to get it into the chapters but from there I can not figure out how to find the 2 returns. Everything I have tried has failed. Can anyone help me. Here is a sample of what it might look like. HOW TO WATER ROSES Start by turning the water on.... HOW TO MOW Start the mower...
-
1, 'SIMPLE', 'nfo', 'fulltext', 'cid,nfo', 'nfo', '0', '', 1, 'Using where; Using temporary; Using filesort' 1, 'SIMPLE', 'collections', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'temp.nfo.cid', 1, ''
-
Here is the query I am currently using. It seems to take forever. It keeps wanting to use filesort and a temporary can anyone plz help me. I will send the creates for both tables. select collections.`id`,collections.`subject`,collections.`numfiles`,collections.`groups`, collections.`size`,collections.`from`,collections.`date`,collections.`nfo` FROM temp.collections,temp.nfo where match (nfo.nfo) against ('$searchterm' in boolean mode) and nfo.cid = collections.id order by `date` desc limit 0,50 CREATE TABLE `nfo` ( `id` mediumint(6) unsigned NOT NULL auto_increment, `cid` int(10) unsigned NOT NULL, `fid` int(10) unsigned NOT NULL, `nfo` mediumtext NOT NULL, `filename` varchar(200) NOT NULL, PRIMARY KEY (`id`), KEY `cid` (`cid`), KEY `fid` (`fid`), FULLTEXT KEY `nfo` (`nfo`) ) ENGINE=MyISAM AUTO_INCREMENT=152507 DEFAULT CHARSET=latin1 CREATE TABLE `collections` ( `id` int(10) unsigned NOT NULL auto_increment, `subject` text NOT NULL, `filename` text NOT NULL, `numfiles` smallint(4) unsigned NOT NULL default '0', `groups` text NOT NULL, `partids` mediumtext NOT NULL, `size` bigint(20) unsigned NOT NULL default '0', `from` tinytext NOT NULL, `date` int(10) unsigned NOT NULL default '0', `group1` smallint(4) unsigned NOT NULL, `group2` smallint(4) unsigned default NULL, `group3` smallint(4) unsigned default NULL, `group4` smallint(4) unsigned default NULL, `group5` smallint(4) unsigned default NULL, `nfo` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `filename_U` (`filename`(255)), KEY `from` (`from`(50)), KEY `date` (`date`), KEY `Size` (`size`), KEY `nfo` (`nfo`), KEY `groupindex` USING BTREE (`group1`,`group2`,`group3`,`group4`,`group5`), FULLTEXT KEY `Subject_full` (`subject`) ) ENGINE=MyISAM AUTO_INCREMENT=1865103 DEFAULT CHARSET=latin1 MIN_ROWS=100000 MAX_ROWS=2500000 AVG_ROW_LENGTH=500
-
just tried it in 5.0.41 and everything was fine. returned 0.
-
no it works since I switched the and that you had with where. Seems to work pretty good now that I optimized the tables a little bit more. Thank you very much for the help.
-
ok here are the 2 queries I have managed to get working. They both take the same time to run and vary big time. SELECT collections.id , collections.subject , collections.numfiles , collections.groups , collections.size , collections.from , collections.date , collections.nfo , ( SELECT sum(mainsubjects.num) FROM mainsubjects INNER JOIN collectionpartids ON mainsubjects.id = collectionpartids.fid where collectionpartids.cid = collections.id ) AS small , ( SELECT sum(mainsubjects.max) FROM mainsubjects INNER JOIN collectionpartids ON mainsubjects.id = collectionpartids.fid where collectionpartids.cid = collections.id ) AS big FROM collections WHERE MATCH (collections.subject) AGAINST ('dvdr' IN BOOLEAN MODE) GROUP BY collections.id order by `date` desc limit 500 SELECT `collections`.`id`,`collections`.`subject`,`collections`.`numfiles`, `collections`.`groups`,`collections`.`size`,`collections`.`from`,`collections`.`date`, `collections`.`nfo`,sum(mainsubjects.num) as small,sum(mainsubjects.`max`) as big FROM temp.collections, temp.mainsubjects, collectionpartids WHERE match (`collections`.`subject`) against ('dvdr' in boolean mode) and collections.id = collectionpartids.cid and collectionpartids.fid = mainsubjects.id group by collections.id order by `collections`.`date` desc limit 500
-
I would just like to find a way to get this to work faster than the query I have now. The one I have now takes anywhere from 10-60 secs each time.
-
that it don't work or that it works on his yet don't on mine. I ahve tried it on 2 different servers still could not get it to work.
-
[SOLVED] connection lost during queries over 30 secs?
binindex replied to binindex's topic in MySQL Help
I have all values set to 28800 and it still don't help. After like 30 seconds it just stops. Don't matter if it's doing a repair table to whatever the server quits doing what its doing and I get a lost connection to server. But I don't find anything in the error log. -
I don't use the select_db function but I do put my connection into a $mysql variable then pass it in with my query.
-
ok I just installed 5.0.41 and 5.0.21 on another server and get the same error with both. SO not sure what is different with yours over mine.
-
that or point me to where I can download ver 5.0.21 of mysql. I will downgrade to get this thing working.
-
ok it must be the mysql version. Cause I just installed sqlyog and I get the same error in it.
-
what version of mysql do you have? I have 5.0.41. I have tried mysql query browser and the mysql.exe console.
-
ok I get an error of unknown column `collections.id` in `on clause` when I try this.
-
[SOLVED] connection lost during queries over 30 secs?
binindex replied to binindex's topic in MySQL Help
ok what can I change for this then. Any query whether it be a repair table or a select at the 30 second mark it says lost connection with server. I have tried with query browser and with a straight connect with the console mysql program. -
ok since there is a size limit I can only submit 400 collections and the corresponding data for the other tables. Just for a fyi. The collections table has around 400k+ rows. The collectionpartids and mainsubjects tables both have around 4 million + rows. Like takes a whole lot longer. The match is for the fulltext index that is used on the collections table. [attachment deleted by admin]
-
I moved my db to another system and used the same my.ini file from my old server. For some reason any query that takes over 30 seconds will get a connection lost. Even table repairs. Does anyone know how to fix this. I have windows xp and mysql 5.0.41
-
do you mean see if the gang name exists if it does then do this part of code in say php or do this. Using it like an if?
-
do a showprocesslist and then kill the thread it is running on.
-
the match wuery with order by is super fast. There are just over 400k rows and it usually does the query in .1 secs or less. Also the other part with the other 2 tables is lightning fast if I just use them and one collection id. I have people searching for anywhere from 50-500 results and it takes forever anywhere from 5-60 secs per query.
-
It's kinda hard to read but here it is. +----+-------------+-------------------+----------+----------------------+------ --------+---------+----------------------------+------+------------------------- ---------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+----------+----------------------+------ --------+---------+----------------------------+------+------------------------- ---------------------+ | 1 | SIMPLE | collections | fulltext | PRIMARY,Subject_full | Subje ct_full | 0 | | 1 | Using where; Using tempo rary; Using filesort | | 1 | SIMPLE | collectionpartids | ref | PRIMARY | PRIMA RY | 4 | temp.collections.id | 10 | Using where; Using index | | 1 | SIMPLE | mainsubjects | eq_ref | PRIMARY | PRIMA RY | 3 | temp.collectionpartids.fid | 1 | Using where | +----+-------------+-------------------+----------+----------------------+------ --------+---------+----------------------------+------+------------------------- ---------------------+ 3 rows in set (0.00 sec)