Jump to content

Jarin

Members
  • Posts

    9
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

Jarin's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. I have a rather large query that I've constructed to save me some time and resources in a PHP script, however when using EXPLAIN on the query I get 'using where; using temporary; using filesort' which I know is never a good thing on heavy-load servers. So, if anyone could give me some advice as to how to optimize this query I would greatly appreciate it. Also, for the record, I have indexed the 'groupid' and 'type' columns already. Here's the table: [code] CREATE TABLE `itemgen` (   `id` int(10) NOT NULL auto_increment,   `type` char(10) default NULL,   `groupid` char(10) default NULL,   `priority` tinyint(1) unsigned NOT NULL default '1',   `slot` char(20) default NULL,   `skill` char(15) default NULL,   `weight` int(10) default NULL,   `title` char(25) NOT NULL,   `stats` varchar(100) NOT NULL,   `requirements` varchar(100) NOT NULL,   `other` varchar(100) NOT NULL,   `icon` varchar(30) default NULL,   `tags` varchar(100) NOT NULL,   PRIMARY KEY  (`id`),   KEY `group` (`groupid`(3)),   KEY `type` (`type`(3)),   KEY `code` (`id`) [/code] And the query: [code] SELECT CONCAT( IF(ig1.title != '', CONCAT(ig1.title, ' '), ''), IF(ig3.title != '', CONCAT(ig2.title, ' '), ig2.title), ig3.title ) AS name, CONCAT( IF(ig2.stats != '', CONCAT(ig1.stats, '_'), ig1.stats), IF(ig3.stats != '', CONCAT(ig2.stats, '_'), ig2.stats), ig3.stats ) AS stats, CONCAT( IF(ig2.requirements != '', CONCAT(ig1.requirements, '_'), ig1.requirements), IF(ig3.requirements != '', CONCAT(ig2.requirements, '_'), ig2.requirements), ig3.requirements ) AS requirements, CONCAT( IF(ig2.other != '', CONCAT(ig1.other, '_'), ig1.other), IF(ig3.other != '', CONCAT(ig2.other, '_'), ig2.other), ig3.other ) AS other, CONCAT( IF(ig2.tags != '', CONCAT(ig1.tags, '_'), ig1.tags), IF(ig3.tags != '', CONCAT(ig2.tags, '_'), ig2.tags), ig3.tags ) AS tags, CONCAT( ig1.id, '_', ig2.id, '_',  ig3.id ) AS code, ig2.skill AS skill, ( ig1.weight+ ig2.weight+ ig3.weight ) AS weight, ig2.icon AS icon, ig2.slot AS slot FROM itemgen AS ig1 JOIN itemgen AS ig2 JOIN itemgen AS ig3 WHERE ig1.type='prefix' AND ig2.type='affix' AND ig3.type='suffix' AND ig1.groupid='test' AND ig2.groupid='test' AND ig3.groupid='test' ORDER BY RAND() LIMIT 1 [/code] EXPLAIN returns this: [code]1 SIMPLE ig1 ref group,type type 4 const 1 Using where; Using temporary; Using filesort 1 SIMPLE ig3 ref group,type type 4 const 1 Using where 1 SIMPLE ig2 ref group,type type 4 const 2 Using where [/code] The first row also includes 'Using filesort' but it looks like the forum truncated it..
  2. Alright, well I just turned down error reporting then. I suppose it can't hurt anything. Thanks!
  3. Hello I have a small problem with a script of mine.. I keep getting an Undefined Index error when I call for a value from an array when that value is not defined. For instance, calling $_REQUEST['do'] when 'do' has no value will return such an error. Is it safe to simply suppress these errors or is there something I should do to eliminate them? Thanks in advance.
  4. I'm having a bit of a problem.. I have some PHP code that gets a result set from the database from an SQL array and performs a loop on them. However, some of the elements of the array may have duplicate values and MySQL weeds them out and only selects that particular row once. Say, for instance, we have this query: SELECT * FROM items WHERE id IN ('1', '2', '3', '1') ('id' is the primary key in this case, each row is unique) The query would only return the first '1' in the list. However, I need it to return both '1's so that I can perform the loop on both of them, even though it is the same data twice. I have yet to find a way to do this or a workaround for it. Any tips would be much appreciated.
  5. I'm having a very strange problem that I've never encountered before. It's rather simple actually, but I can't find any reason why it would do this. So, I have a serialized array like this: a:1:{s:5:"night";s:1:"1";} which is stored in the variable $world['flags']. If I were to echo that variable, it prints out exactly as it should, a:1:{s:5:"night";s:1:"1";} However, if I assign a variable like this: $wflag = $world['flags']; Then $wflag will return a NULL value. It doesn't store the information at all. Below is the entire portion of the script in question. And yes, 'flags' is a valid field in the result. Everything else in the query works as it should. $worlds = $DB_site->query("SELECT * FROM vb3_heroadmin WHERE world='$worldid'"); $world = mysql_fetch_array($worlds); $worldn = $world['name']; $optionx = $world['xpmult']; $optiong = $world['goldmult']; $optioni = $world['itemrarity']; $optiona = $world['aprarity']; $wflag = $world['flags']; Thanks for any help you might offer.
  6. Never knew that operator existed. Thanks a lot guys, much appreciated!
  7. Well, considering I'm not very math savvy, I need a bit of help. I'm trying to determine whether one integer is evenly divisible into another integer, IE is 1033343 a multiple of 3, is 105 a multiple of 5, etc. How would one go about determining this in a memory-conservative manner? Thanks in advance.
  8. [!--quoteo(post=350255:date=Feb 28 2006, 10:24 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 28 2006, 10:24 AM) [snapback]350255[/snapback][/div][div class=\'quotemain\'][!--quotec--] Do you need it to be cryptographically secure or just compressed? Or both? Encrypting a string will almost always make it at least a little longer. You can use gzcompress() and gzuncompress() to make the string shorter, but your PHP installation needs to have compiled the zlib extension. You can also use gzcompress() and then crypt(), to make it shorter and secure. Just remember to undo it in the correct order. [/quote] I mostly need to just be able to compress the string, no need for it to be encrypted so much as shortened. You mentioned using crypt() after gzcompress(), however I wasn't aware that crypt() was reversible. Is this this case?
  9. Hello I am looking for a two-way encryption method that can compress/encrypt long strings into short string. IE, I have a serialized array like this: a:102:{i:0;s:1:"1";s:6:"itemid";s:1:"1";i:1;N;s:5:"mdsum";N;i:2;s:0:"";s:8:"baseitem";s:0...etc And I need to compress that to a string of decent length, such as one crypt() might produce. The resulting string also must be decryptable. I have tried experimenting with the mcrypt functions, but none of the resulting strings are short enough for my likes. Is there any other solution someone might be able to provide? Thanks in advance. Jarin
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.