Jump to content

Search the Community

Showing results for tags 'MYSQL'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Welcome
    • Announcements & News
    • New Arrivals
  • Web Development
    • Programming
    • Database Development
    • Server Administration
    • Hosting & Domains
    • Frameworks
  • Web Design
    • HTML & CSS
    • Graphics & Multimedia
  • Desktop Discussion
    • Linux Development
    • Windows Development
    • Mac/Apple Development
    • Hardware Discussion
  • Marketing & Business
    • Advertising, Marketing, Monetization & Social Media
    • Search Engine Optimization & Traffic Building
    • Buy, Sell or Trade
  • Prodjex Web Development Applications, Tools and Plugins
    • IP.Board Applications and Plugins
    • Web Tools
  • The Developer Dump
    • General Chat

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start





Website URL







Found 15 results

  1. An application I've been troubleshooting has had major performance issues last week. I spent a few hours on it banging my head against a wall. Finally after doing some research I found that a lot of the database tables were created years ago in the MyISAM storage engine. After reading further I saw that for this application at least, InnoDB made much more sense. I'm actually not sure why you would ever use MyISAM anymore really... The server itself was showing massive MySQL connections at any give time, 20k-30k! There were not near that many people online so I was confused as to what was happening. I knew the page that was causing a majority of the issues though as it was showing massive load times, approximately 40 seconds. I tracked down the query and it was this: select lat, lon, createDate from activityTracking where userID = 404 order by id desc limit 1 There were already indexes on the userID field and id is a primary key on the table. The query would run for about 4 seconds and it was in a PHP loop for multiple records that were displaying on the page, so 4 seconds about 10 times = 40 second page load time. The query at first look seems to be very efficient, it has indexes, and order by and limit. So I randomly removed the limit 1 from the query just to see what would happen. select lat, lon, createDate from activityTracking where userID = 404 order by id desc This time it executed in .0099 seconds. How can that be? Well the table itself have records inserted every second, probably 100 records per second, so there must be some table locking going on or some index being rebuilt way too often that's causing my issue I figured. That's when I stumbled upon the MyISAM and InnoDB storage engine questions and explanations. InnoDB: The InnoDB storage engine in MySQL. Support for transactions (giving you support for the ACID property). Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM. Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables. InnoDB is more resistant to table corruption than MyISAM. Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes. MyISAM is stagnant; all future enhancements will be in InnoDB. This was made abundantly clear with the roll out of Version 8.0. MyISAM: The MyISAM storage engine in MySQL. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. -- Mostly no longer true. Full-text indexing. -- InnoDB has it now Especially good for read-intensive (select) tables. -- Mostly no longer true. Disk footprint is 2x-3x less than InnoDB's. -- As of Version 5.7, this is perhaps the only real advantage of MyISAM. MyISAM Limitations: No foreign keys and cascading deletes/updates No transactional integrity (ACID compliance) No rollback abilities 4,284,867,296 row limit (2^32) -- This is old default. The configurable limit (for many versions) has been 2**56 bytes. Maximum of 64 indexes per table InnoDB Limitations: No full text indexing (Below-5.6 mysql version) Cannot be compressed for fast, read-only (5.5.14 introduced ROW_FORMAT=COMPRESSED) You cannot repair an InnoDB table So that was my issue, my select is getting queued up behind the inserts that were firing and causing the long query times. I logged into phpmyadmin and modified the table to InnoDB and all of a sudden my queries were running in .0004 seconds. The page then loaded in less than a second and everything was all set. I'm still not sure why you would ever use MyISAM tables. InnoDB is something I'll be using going forward for sure. Here's a little more info on MyISAM vs InnoDB - http://blog.danyll.com/myisam-vs-innodb/
  2. The simplest way of selecting random rows from the MySQL database is to use "ORDER BY RAND()" clause in the query. SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1; The problem with this method is that it is very slow when your tables contain thousands of records. I was wondering if you knew any workarounds to speed things up?
  3. A few years ago the free database system best known by developers was purchased by Oracle company, it continues for free, however I wonder if it is still considered free software?
  4. Can any one tell me how to add(sum) two columns from two different tables but from same database. please send me exact query if you know..
  5. I have a column within my MySQL table that holds city and states for records. The dataset looks like the following: Clay County, AL Cleburne County, AL Coffee County, AL Colbert County, AL Conecuh County, AL Coosa County, AL Covington County, AL I need to just select the state name for all of the records. Since I know this is not user input and the data is all clean/structured as above I can trim off what I don’t need with the following command: select right(trim(cityStateField), 2); This then returns the needed results with just the state abbreviation: AL AL AL AL AL AL AL The post MySQL Trim appeared first on Kansas City Web Consulting | Kansas City Web Development. View the full article
  6. These are two different commands with different outputs when added to a SQL query. MySQL “in” command for example: select * from assets a left outer join assetInterests ai on a.id = ai.assetID where orgID = 2011 and deviceID in (11) and interestID IS NULL In the table I have the following deviceID’s: 11 11 11 11,9 The result set will only return the top 3. The 11,9 result will be missing. Why is that? 11 is in that column. The issue is that “in” I could say and deviceID in (1,3,11) and any column that had just the 1 or 3 or 11 will return, but any multiples will not. For that I need to use the MySQL command find in set like this: select * from assets a left outer join assetInterests ai on a.id = ai.assetID where orgID = 2011 and find_in_set (11,deviceID) and interestID IS NULL The post MySQL – find in set vs in appeared first on Kansas City Web Consulting | Kansas City Web Development. View the full article
  7. My server has a site that's getting a lot of traffic lately and I'm seeing some mysql errors coming up in the IP.Board logs. It says it's an error with mysql not the software. Anyone know how to fix the error: Error: 23 - Out of resources when opening file
  8. Is it important to close your database connection at the end of your PHP file? I assumed it closed as soon as the page was done executing, so what would be the benefit in including it in your code?
  9. I'm inserting into my MYSQL database via a PHP page. For some reason it keeps losing the formatting and line breaks. What do I need to change to retain these? It's inserting into a MEDIUMTEXT field. Current Code: mysql_query("INSERT INTO projects (UserID, Title, Description, CreateDate) VALUES ('$userid', '$ProjectName', '$ProjectDescription', '$Date')"); This data is coming from the page before where the user enters the data in a <textarea> field from a html form.
  10. I'm not sure what to call what I'm trying to do so hard to run a Google search on it. I have a spot where I want to assign team members to a project. There is an input box where I can type their names. As I start typing their name I would like it to query the users table and show possible results, then I can click their name and they are added, then I can start typing another persons name and add multiple people.
  11. I have a jQuery date picker that passes the date format dd-mm-yyyy to my page that inserts data into the database. My table tructure requires a date format of yyyy-mm-dd. Is there an easy way to change the date format in the jQuery Datepicker?
  12. My below code isn't returning data for <h1><?php echo($query['id']); ?><h1> What am I missing? <?php //Connect to the Database $host = localhost; $username = db_user; $password = 'pass'; $connect = mysql_connect($host,$username,$password); //Select the Database $db = 'db_pm'; mysql_select_db($db); //Query the needed data $query = "select id,userid,title,description from projects where id=1"; $result = mysql_query($query) or die('Query failed: ' . mysql_error()); ?> <link href="../assets/front.css" rel="stylesheet" type="text/css" /> <?php include("header.php");?> <div class="workplace"> <div class="newprojectbox"> <h1><?php echo($query['id']); ?><h1> </div> </div> <?php include("footer.php");?>
  13. This following will allow you to upload a .csv file via a web form then insert that data into a MYSQL database. I had a request for it this morning and so now that I have it created I figured I would share. Let me know if you see anything that would be an improvement. Step 1 - Create Web Form <form enctype="multipart/form-data" action="uploader.php" method="POST"> <input type="hidden" name="MAX_FILE_SIZE" value="100000" /> Choose a file to upload: <input name="uploadedfile" type="file" /><br /> <input type="submit" value="Upload File" /> </form> Step 2 - Create the Uploader.php File <?php // Where the file is going to be placed $target_path = "uploads/"; /* Add the original filename to our target path. Result is "uploads/filename.extension" */ $target_path = $target_path . basename( $_FILES['uploadedfile']['name']); $target_path = "uploads/"; $target_path = $target_path . basename( $_FILES['uploadedfile']['name']); if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) { echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded"; } else{ echo "There was an error uploading the file, please try again!"; } ?> <br><a href="./import.php">Click To Import</a> Step 3 - Create the Import.php File that will insert the data into MYSQL <?php /********************************/ /* Edit the entries below to reflect the appropriate values /********************************/ $databasehost = "localhost"; $databasename = ""; $databasetable = ""; $databaseusername =""; $databasepassword = ""; $fieldseparator = ","; $lineseparator = "n"; $csvfile = "uploads/nameof.csv"; /********************************/ /* Would you like to add an ampty field at the beginning of these records? /* This is useful if you have a table with the first field being an auto_increment integer /* and the csv file does not have such as empty field before the records. /* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure. /* This can dump data in the wrong fields if this extra field does not exist in the table /********************************/ $addauto = 0; /********************************/ /* Would you like to save the mysql queries in a file? If yes set $save to 1. /* Permission on the file should be set to 777. Either upload a sample file through ftp and /* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql /********************************/ $save = 0; $outputfile = "output.sql"; /********************************/ if(!file_exists($csvfile)) { echo "File not found. Make sure you specified the correct path.n"; exit; } $file = fopen($csvfile,"r"); if(!$file) { echo "Error opening data file.n"; exit; } $size = filesize($csvfile); if(!$size) { echo "File is empty.n"; exit; } $csvcontent = fread($file,$size); fclose($file); $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); @mysql_select_db($databasename) or die(mysql_error()); $lines = 0; $queries = ""; $linearray = array(); foreach(split($lineseparator,$csvcontent) as $line) { $lines++; $line = trim($line," t"); $line = str_replace("r","",$line); /************************************ This line escapes the special character. remove it if entries are already escaped in the csv file ************************************/ $line = str_replace("'","'",$line); /*************************************/ $linearray = explode($fieldseparator,$line); $linemysql = implode("','",$linearray); if($addauto) $query = "insert into $databasetable values('','$linemysql');"; else $query = "insert into $databasetable values('$linemysql');"; $queries .= $query . "n"; @mysql_query($query); } @mysql_close($con); if($save) { if(!is_writable($outputfile)) { echo "File is not writable, check permissions.n"; } else { $file2 = fopen($outputfile,"w"); if(!$file2) { echo "Error writing to the output file.n"; } else { fwrite($file2,$queries); fclose($file2); } } } echo "Found a total of $lines records in this csv file.n"; ?> For this to work correctly all you need to do is put all 3 of these .php files in the same directory and create another directory within called "uploads".
  14. So I could do this in MSSQL, but how is this done in MYSQL? I data in field "order_items" like: 121x1 134x4 1001x2 163x1 What I want to do is select everything before the "x".
  15. I'm trying to figure out how to use this code, but it's not working right. This query works fine: $var1= $_POST['user']; $var2 = $_POST['order']; //Connect to the Database $host = localhost; $username = user; $password = pass; $connect = mysql_connect($host,$username,$password); //Select the Database $db = 'devwp'; mysql_select_db($db); //Query the Needed Data $query = 'select distinct pm.meta_value from wp_shopperpress_orders o inner join wp_posts p on left(o.order_items, instr(o.order_items, "x")-1) = p.id inner join wp_postmeta pm on p.id = pm.post_id'; $result = mysql_query($query) or die('Query failed: ' . mysql_error()); I want to modify it to have a where clause in the query, but the code I'm trying to do fails. I'm trying to have $var2 filter in the where clause. $var1= $_POST['user']; $var2 = $_POST['order']; //Connect to the Database $host = localhost; $username = user; $password = pass; $connect = mysql_connect($host,$username,$password); //Select the Database $db = 'devwp'; mysql_select_db($db); //Query the Needed Data $query = 'select distinct pm.meta_value from wp_shopperpress_orders o inner join wp_posts p on left(o.order_items, instr(o.order_items, "x")-1) = p.id inner join wp_postmeta pm on p.id = pm.post_id where o.order_id = ' . $var2; $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  • Create New...