Program to optimise mysql queries


Basically im looking for a program that will take input (my query) and produce an output with a much nicer, more efficient query. I have seen sql programs that do this sort of thing, but nothing for mysql.
Just wonder if there is any programs that i have missed Tryed Toad for mysql, but that wasnt what i wanted, i dont want a program to admin a mysql database, simply to produce a more efficient query.

SURELY there is a program that does such a task
Posted On: Thursday 25th of October 2012 11:21:58 PM Total Views:  223
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




MySQL - program that connects to a MySQL server

Anybody can help by the program which is in "C/C++ program that connects to a MySQL server and executes an SQL query from a text file having a single query"
VIEWS ON THIS POST

119

Posted on:

Thursday 4th October 2012
View Replies!

program

... \t i want a\tC/C++ program that finds the MySQL PID file and prints its contents.... \t please help me.
VIEWS ON THIS POST

144

Posted on:

Friday 5th October 2012
View Replies!

Where does these mysql programs get install to (was "My SQL Help")

Can someone help me install a HLstatsX for my Linux dedicated CS Source server. I can not find a good installation manual to follow by. Help, Please
VIEWS ON THIS POST

113

Posted on:

Friday 12th October 2012
View Replies!

Connecting to server from localhost program

I have or want to have SAM Broadcaster on my computer, i've done this before but do not remember how i programed it. In order for SAM Broadcaster (v4) to work i need to connect to a database. Though i would like it to connect to my site's database. So i added the information only it keeps saying it can not connect now does anyone want to know if im even doing this right.. i have 2 things that is probably why it's not working 1) USERNAME 2) DATABASE NAME now would it just be... username or would my host do it like. username_ and same for DATABASE would it be username_db or _db or just db please help!
VIEWS ON THIS POST

121

Posted on:

Friday 12th October 2012
View Replies!

The benefits of keeping mysql maintained & optimised

The company I work for has no idea or concept of how their website works. They have no idea of the importance of regular back ups and optimisation of the database and removing deleted products from the system to free space up in the database. They keep telling me that these products need to stay on even when they are sold and I keep saying please look after your database. The only language they understand is and thats it. Trouble is no one is listening to me. I would like to throw some figures at the them. I would like to know how much it would cost them for a MYSQL expert to come in and repair a corrupt table full of thousands of clients details and products. I would like to know why it is important to keep MYSQL maintaind and optimised. I need an experts view, so I can print it off and show them. Secondly for my own peice of mind I back up the database every single evening without fail. What I do to test it is upload to my own server at home through phpmyadmin. When all the tablkes appear and records I presume that it is ok What do you do to test
VIEWS ON THIS POST

144

Posted on:

Friday 12th October 2012
View Replies!

Java program all of a sudden not talking with MySQL causing whole MySQL to not respon

, One one of our servers we have a Java program that runs and this program then writes new data to the MySQL server that is on that machine. This Java has been running just fine for few years, but all of a sudden as of a few days ago it is not running. And when it is started it blocks all connection to the MySQL server on that machine too resulting in this Error message when trying to connect to this server: Warning: mysql_connect() [function.mysql-connect]: Too many connections in /var/www/html/anoox.com/a1_oma.php on line 20 connection via server2.anoox.com tried an Do you have any suggestions how to fix this Or is there a Java forum here to which I should be posting this question. I
VIEWS ON THIS POST

145

Posted on:

Sunday 21st October 2012
View Replies!

How to optimise this query?

I'm trying to sort out some of my slowest queries, and here is one that takes some time, partially because it's a bit slow, but also because I run it quite often. Anyway, my table site_articles has seperate indexes on approved (int), type (int) and created (datetime), and the only one used is created. There is however a full tablescan occurring... SELECT id, title, created, headline FROM site_articles WHERE approved=1 AND type=0 AND id 14277 AND DATE(created) = DATE('20100225') ORDER BY created DESC LIMIT 15 Is there any way I can improve this one Note that I can pass the date '20100225' in any format, so I can change that if necessary.
VIEWS ON THIS POST

99

Posted on:

Sunday 21st October 2012
View Replies!

MySQL programming help

, I have just downloaded ZenCart for my e-commerce site. It says "MySQL user: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP. On an hSphere host, this would be "dba" access, or at least read/write. )" I need to set these up on my web server. I don't know any MySQL and very little PHP can someone please guide me in the right direction My boss has been on me to get this done so it needs to be fast too.
VIEWS ON THIS POST

158

Posted on:

Sunday 21st October 2012
View Replies!

Help with db schema for modular program

I've been working with MySQL for a few yeas, but typically for simple web apps. I'm in the process of designing a program to handle patient care reports for volunteer emergency medical service organizations (the project is openEPCR). I'm a volunteer EMT with a 501(c)(3) non-profit, and also do their sysadmin and programming work. We currently have a custom VB application with a MySQL backend, but would like something (Java or Python, leaning towards Java) that is platform-independent. If I'm going to go to the trouble of doing the development, I'd like it to be something that other organizations can use. The basic functionality is that you have an on-screen form that corresponds to a printed form. You fill in the form on screen, it gets put to the database, and then you print a PDF of it, filled in. The problem: The forms are largely organization-specific, so my idea is to have pluggable "modules" that specify the form fields, contain the Java classes for the on-screen forms, handle the database queries, and also have the fillable PDF. I'm looking for suggestions on how to design a database schema that can handle the modular forms. Each form will have up to 200 fields, some full text, some other types, so it seems a bit cumbersome to go with a simple table schema like ID, Key, Value, not to mention this screws up data typing. On the other hand, if the module just includes a SQL file to build the table, then any changes to the form require changes to the table. Any thoughts Just build a table unique to each module Or is there a better way
VIEWS ON THIS POST

266

Posted on:

Wednesday 24th October 2012
View Replies!

Performance - Processing of data at "query level" or at "programming level"?

I have a table containing info about football (soccer) games being played between teams. Structure: PHP Code: CREATETABLE`games`( \t`id`int(11)NOTNULLauto_increment, \t`game_id`tinyint(4), \t`team`tinyint(4), \t`goals_for`tinyint(4), \t`goals_against`tinyint(4), \t`win`tinyint(1), \t`draw`tinyint(1), \t`loss`tinyint(1), \tPRIMARYKEY\t(`id`) ) ('win', 'draw' and 'loss' fields are a not-very-pretty workaround I found for the "count() not returning 0" problem I detailed in another thread. They can be either 1 or 0, meaning 'true' or 'false' for each row) My question is: What is the best method of pulling the data from the database and displaying it in a table showing amount of games played, total points, wins, draw, losses, goal difference, etc Specifically: Is it better, performance- and resources-wise, to process the data on the query as such: PHP Code: SELECT t.nome,(SUM(p.win)*3+SUM(p.draw))ASpoints ,COUNT(p.id)ASgames ,SUM(p.win)ASwins ,SUM(p.draw)ASdraws ,SUM(p.loss)ASlosses ,SUM(p.goals_for)ASgoals_for ,SUM(p.goals_against)ASgoals_against ,(SUM(p.goals_for)-SUM(p.goals_against))ASgoal_difference FROMgamesp,timest WHEREp.team=t.id GROUPBYt.nome ORDERBYpointsDESC,winsDESC,goal_differenceDESC,gamesASC (Table 'times' is a simple table containing team id and team name. Sample output from the query: ) Code: nome points games wins draws losses goals_for goals_against goal_difference Botafogo 4 3 1 1 1 10 6 4 Fluminense 4 2 1 1 0 3 2 1 Flamengo 3 3 1 0 2 6 11 -5 or is it better to just pull the "raw" data from the database as it is now and then process it using php Oh, and just for the record, EXPLAIN SQL on that big query tells me: Code: table type possible_keys key key_len ref rows Extra p ALL NULL NULL NULL NULL 8 Using temporary; Using filesort t ALL id NULL NULL NULL 3 Using where
VIEWS ON THIS POST

79

Posted on:

Wednesday 24th October 2012
View Replies!

Is there a program for continously seeing what MySQL processes are running

Greetings, Is there a program like Linux Top that allows one to see on a continuous, that is self refreshing basis, the MySQL Processes that are running and how much CPU each process is using and for how long it is running and thus inform one as to the MySQL processes that are taking too long so that one can kill them Just to be clear a command like this: mysqladmin -u root -h localhost -p processlist will not do the job, since the above command gives you a snap shot of when it was issued and does not run on a continuous basis as for example Linux Top does. To be even further clear, I am looking for a PC based desktop software that like Putty has a MySQL equivalent Top function that provides to one on a continuous basis information about what MySQL processes are running on ones server.
VIEWS ON THIS POST

211

Posted on:

Thursday 25th October 2012
View Replies!

query doesn't work with mysql4

Can someone tell me what's wrong with the mysql query below It does work on mysql 5 but gives me an error with mysql 4. Code: SELECT rev_profile_indiv. * FROM rev_profile_indiv, rev_profile_kids_indiv WHERE 1 AND reviewing IN ( 1, 0 ) AND active IN ( 1 ) AND contract IN ( 1 ) AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) >= UNIX_TIMESTAMP( '2004-12-15' ) AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) )
VIEWS ON THIS POST

263

Posted on:

Thursday 25th October 2012
View Replies!

mysql exports and character accents and acutes

Hi I wonder if anybody can help. I have run a Database export creating a SQL file. When I imported this file I had the Character set of the file: as utf8. I've noticed that on the new server characters like The enchanting Jardn Tropical are displayed as The enchanting Jardn Tropical and the marina of Puerto Coln are displayed as the marina of Puerto Coln, I wonder if this to do with the Character set of the imported file. The new database has Collation set as latin1_swedish_ci but I am unable to determine the collation of the original database as it doesn't appear in phpMyadmin structure. In both cases my page has set as the charset
VIEWS ON THIS POST

238

Posted on:

Thursday 25th October 2012
View Replies!

accessing mysql/bin directory problem

This maybe very basic question, that I hope some of you can answer very quickly for me. I'm running mysql 4.1 on windows xp just to learn the database, and I'm in a need to run .exe in the bin directory. The problem I'm having is accessing this directory. The path is C:/Program Files/Mysql/Mysql server 4.1/bin but when I get to C:/cd "Program Files/..." I get an error message saying (parameter format not correct - "program) i have tried several things, as well as following a user guide for changing directories that states that if the folder name has spaces use " to access that subfolder...maybe there is a simple tip you might have for me...please help...thanks!!! P.S.: just so you know, because I was running the setup.exe for mysql, it automatically choose this directory...thanks
VIEWS ON THIS POST

240

Posted on:

Thursday 25th October 2012
View Replies!

avg of avg in mysql

hello all, would there be a way to get the average of the average of three columns in a MYSQL table kind of like this: SELECT AVG(avg(Field1),avg(Field2),avg(Field3)) FROM table
VIEWS ON THIS POST

227

Posted on:

Thursday 25th October 2012
View Replies!

Populating mysql database

I have set up a mysql database with several tables on a shared server. I have raw data (sql) I want to populate the tables with and am using the command line in telnet. Can someone give me an example on how I would dump the data into the database using telnet or why I get the following error message when I try to execute the sql statement using my web browser. Parse error: parse error, unexpected $ in /usr/www/users/myaccount/test100/table10.php on line 259
VIEWS ON THIS POST

302

Posted on:

Thursday 25th October 2012
View Replies!

2 simple mysql queries

have problems with 2 simple queries and i dont know how to solve them. plz help me. first one looks like this: 1.Whats the average length of the movies released in 2006 And which movie comes closest to that average length (search upwards) i wrote 2 separate queries: select avg(length) from film where release_year = 2006; and the second one: select title, length from film where release_year = 2006 order by length; i have to make this into one query. how do i do that what should i write the second query looks like this: One of the customers comes into the video store and he needs your help to find a children movie with 2 words to solve a word puzzle. In the puzzle the second word has 8 letters. He already knows that the 4th letter is E and the last 2 letters are N and D. Help him to find the movie i wrote: select title from film where title like ('% ___E__ND') and film_id in (select film_id from film_category where category_id in (select category_id from category where name = 'Children')); but the problem is that it doesnt return me a result. this query contains everything but the things are not in order. plz help me!
VIEWS ON THIS POST

346

Posted on:

Thursday 25th October 2012
View Replies!

Auto log in mysql session script

Kia Ora The following script is designed to welcome back a previously logged on user But I am little stuck as to why i cant get the $last_login variable to work the row exsists in the database table can someone with fresh eyes have a look PHP Code:
VIEWS ON THIS POST

214

Posted on:

Thursday 25th October 2012
View Replies!

how to make a switch mysql to ms sql

, My current mysql database has grown in size and usage, and its giving so many problems that i'm thinking of switching from mysql to ms sql 2005. My current application is running on php. Is it good idea to switch I havnt found any post related to php/ms sql 2005. Give your opinons. And a tutorial on switching db's would really help.
VIEWS ON THIS POST

252

Posted on:

Thursday 25th October 2012
View Replies!

desktop mysql editor

Does anyone know if there is a desktop editor that I can use to delete colomns and edit other properties from a mysql database I downloaded to my desktop. I don't want to mess with the database on my server.
VIEWS ON THIS POST

287

Posted on:

Thursday 25th October 2012
View Replies!