PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Jul 21, 2019 4:43 am

All times are UTC - 5 hours




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page Previous  1, 2
Author Message
 Post subject:
PostPosted: Thu Mar 11, 2004 12:36 am 
Offline
DevNet Evangelist
User avatar

Joined: Tue May 07, 2002 9:48 am
Posts: 8391
Location: Berlin, ger


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 31, 2004 7:33 pm 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 29, 2004 5:40 am 
Offline
Forum Newbie

Joined: Tue Jun 29, 2004 5:40 am
Posts: 2
Location: Pune, India
Agreed@PHPMyAdmin, but in case you are Prompt-lover.., you have commands like mysql and mysqladmin with all its set of options to this bunch of work other than just checkd,ing for the query.

This especially helps in performance tunning and validating by taking a snapshot of the work MySQL is executing.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 22, 2005 7:56 am 
Offline
Forum Contributor

Joined: Thu Jan 08, 2004 9:28 am
Posts: 404


Top
 Profile  
 
PostPosted: Tue Apr 04, 2006 1:55 am 
Offline
Forum Contributor
User avatar

Joined: Wed Oct 19, 2005 5:14 am
Posts: 491
Location: Nepal
if we log the errors like php syntax errors or mysql syntax errors, it will be a great time saving.

The best way to see the errors at a real time is by either add a die(mysql_error()) or trace a error of mysql by using the following command :

# tail -f /var/log/mysql.err
# tail -f /var/log/mysql.log

The above method saved a lots of time for me!

Happy Debugging!!!

Dibyendra


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 06, 2007 1:19 pm 
Offline
Forum Regular
User avatar

Joined: Fri May 03, 2002 3:33 am
Posts: 512
Location: Edinburgh, Scotland
This is a tip for use when optimising your queries.

Obviously everyone uses 'explain' on their queries :wink: If not I suggest you do a bit of reading up.

It can be handy to see what the optimiser has done to your queries before it runs them.

As an example I have 2 tables

Syntax: [ Download ] [ Hide ]

test

+--------+-------------+------+-----+---------+-------+

| FIELD  | Type        | NULL | KEY | DEFAULT | Extra |

+--------+-------------+------+-----+---------+-------+

| field1 | tinyint(4)  | NO   |     |         |       |

| field2 | varchar(20) | NO   |     |         |       |

+--------+-------------+------+-----+---------+-------+

 


and

Syntax: [ Download ] [ Hide ]

test2

+--------+-------------+------+-----+---------+-------+

| FIELD  | Type        | NULL | KEY | DEFAULT | Extra |

+--------+-------------+------+-----+---------+-------+

| field1 | tinyint(4)  | NO   |     |         |       |

| fk1    | tinyint(4)  | NO   |     |         |       |

| field2 | varchar(20) | NO   |     |         |       |

+--------+-------------+------+-----+---------+-------+

 


I'll do a RIGHT JOIN to illustrate my point

Syntax: [ Download ] [ Hide ]

mysql> SELECT * FROM test2 RIGHT JOIN test ON (test2.fk1 = test.field1);

+--------+------+---------------+--------+--------+

| field1 | fk1  | field2        | field1 | field2 |

+--------+------+---------------+--------+--------+

|      1 |    1 | first linked  |      1 | first  |

|      2 |    1 | second linked |      1 | first  |

|      3 |    2 | first linked  |      2 | second |

|   NULL | NULL | NULL          |      3 | third  |

+--------+------+---------------+--------+--------+

 


so that gives me all records in test and those related records in test2.

RIGHT JOIN is not standard, behind the scenes the optimiser turns this into a LEFT JOIN.

How do I know?

run an explain plan with the 'extended' key word

Syntax: [ Download ] [ Hide ]

mysql> EXPLAIN extended SELECT * FROM test2 RIGHT JOIN test ON (test2.fk1 = test

.field1);

 


immediately after this outputs the explain plan run show warnings

Syntax: [ Download ] [ Hide ]

mysql> SHOW warnings;

 


This will show you exactly the query that was run

Syntax: [ Download ] [ Hide ]

SELECT `example`.`test2`.`field1` AS `field1`,`example`.`test2`

.`fk1` AS `fk1`,`example`.`test2`.`field2` AS `field2`,`example`.`test`.`field1`

 AS `field1`,`example`.`test`.`field2` AS `field2` FROM `example`.`test` LEFT jo

IN `example`.`test2` ON((`example`.`test2`.`fk1` = `example`.`test`.`field1`)) w

here 1

 


as you can see the optimiser turned this into a LEFT JOIN.

Enjoy.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 06, 2007 5:06 pm 
Offline
Forum Commoner

Joined: Sun Mar 04, 2007 6:35 am
Posts: 53


Top
 Profile  
 
 Post subject: Re:
PostPosted: Tue Jun 10, 2008 8:46 am 
Offline
Forum Newbie
User avatar

Joined: Tue Jun 10, 2008 8:30 am
Posts: 15
Location: Washington, DC


Top
 Profile  
 
PostPosted: Tue Jun 10, 2008 10:33 am 
Offline
DevNet Master
User avatar

Joined: Sun Jan 21, 2007 12:06 am
Posts: 4135
Will anyone ever let this thread die in peace? o_O
~twigletmac posted in this one for Christ's sake. :P


Top
 Profile  
 
PostPosted: Fri Sep 02, 2011 5:56 am 
Offline
Forum Newbie

Joined: Thu Sep 01, 2011 11:05 pm
Posts: 2
Thnks for the tips. Really like it. ;) Some days ago Mr. Kyle Gush help me in sme problem of my mysql database. Kyle Gush of Atlanta GA is well known in php and mysql. My problem was similar to your tips.


Top
 Profile  
 
PostPosted: Wed Jan 11, 2012 8:59 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
This thread is a classic example of hijacking the topic. That's not a felony, but it would really serve everyone's needs better if those who want to expand on the original topic (and what is implied by the Subject line) would simply start a new thread. Many of the suggestions are very helpful, but beyond the scope of the original poster's topic. Those who want to answer a poster's question, or challenge what a poster stated are welcome to Reply to the topic. But if you want to bend the topic to a related (or unrelated) discussion, you are encouraged to start a new thread. I thought that markl999 was pretty clear in his opening post that he wasn't trying to offer a comprehensive list of good practices, but rather to suggest that by following 3 specific tips a large proportion of "problems" that forum users post about MySQL queries could be avoided. I think he's pretty much right on that.

It might be worthwhile for several of you to work up a comprehensive list of good practices and post it as a new thread, which you could request be made a 'sticky' thread, and as people propose modifications, it could be updated. All of the comments have merit, it's just that we end up with a wandering thread that will soon fade into oblivion.


Top
 Profile  
 
PostPosted: Sun Jul 28, 2013 5:28 pm 
Offline
Forum Commoner
User avatar

Joined: Thu Dec 15, 2011 2:40 pm
Posts: 85
Location: Nelson, NZ


Top
 Profile  
 
PostPosted: Mon Feb 13, 2017 3:08 am 
Offline
Forum Newbie

Joined: Mon Feb 13, 2017 2:25 am
Posts: 7
Enable general_log and sudo tail the log file

Two ways to do this:

You can update the my.cnf and restart MySQL. Enable GL:
http://stackoverflow.com/questions/6479 ... -query-log

Easier way with this command:
mysql.server restart --general_log=1 --general_log_file=/usr/local/var/mysql/filename.gen.log


Top
 Profile  
 
PostPosted: Mon Feb 13, 2017 10:56 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
This is a 13 year old thread. I'm just going to close this now.

_________________


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page Previous  1, 2

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group