Asked  7 Months ago    Answers:  5   Viewed   25 times

How is the performance of a Stored Procedure? Is it worth using them instead of implementing a complex query in a PHP/MySQL call?

 Answers

86

Stored procedures will give you a small performance boost, but mostly they are for doing tasks that are difficult or impossible to do with a simple query. Stored procedures are great for simplifying access to data for many different types of clients. Database administrators love them because they control how the database is used as opposed to leaving those details to the developer.

Look to indexes and proper table design to get better performance.

Wednesday, March 31, 2021
 
Yarin
answered 7 Months ago
16

Why not use MySQL's datetime field and the vast array of functions that make date-related queries a snap?

Wednesday, March 31, 2021
 
saad
answered 7 Months ago
43

You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.

e.g. SELECT get_foo(myColumn) FROM mytable is not valid if get_foo() is a procedure, but you can do that if get_foo() is a function. The price is that functions have more limitations than a procedure.

Wednesday, June 9, 2021
 
clean_coding
answered 5 Months ago
19

PHP is attempting to open a connection to localhost. Because your computer is connected to your network via IPv6 it's trying the IPv6 version of 'localhost' first, which is which is an IP address of ::1

http://en.wikipedia.org/wiki/IPv6_address#Special_addresses

::1/128 — The loopback address is a unicast localhost address. If an application in a host sends packets to this address, the IPv6 stack will loop these packets back on the same virtual interface (corresponding to 127.0.0.0/8 in IPv4).

It looks like your MySQL server isn't listening to that address, instead it's only bound to an IPv4 address and so once PHP fails to open the connection it falls back and tries to open localhost via IPv4 aka 127.0.0.1

I personally prefer to use either IP addresses or use ether the Windows hosts file or Mac equivalent to define 'fake' domain names and then use those when connecting to MySQL, which resolve to IP addresses. Either way I can know exactly whether an IPv4 or IPv6 address will be used.

Both MySQL and Apache support IPv6 but you have to tell them to use an IPv6 address explicitly. For MySQL see: http://dev.mysql.com/doc/refman/5.5/en/ipv6-server-config.html

For Apache config see: http://httpd.apache.org/docs/2.2/bind.html

Apache supports multiple IP addresses so you can use both at once - if the network card in the machine has both an IPv4 and IPv6 address. MySQL only supports one address.

Wednesday, June 30, 2021
 
Stefan
answered 4 Months ago
54

Possible character set issue? If your table character set is different from your database character set, this may be causing a problem.

See this bug report: http://bugs.mysql.com/bug.php?id=26224

[12 Nov 2007 21:32] Mark Kubacki Still no luck with 5.1.22_rc - keys are ingored, query takes within a procedure 36 seconds and outside 0.12s.

[12 Nov 2007 22:30] Mark Kubacki After having changed charsets to UTF-8 (especially for the two used), which is used for the connection anyways, keys are taken into account within the stored procedure!

The question I cannot answer is: Why does the optimizer treat charset conversions an other way within and outside stored procedures? (Indeed, I might be wrong asking this.)

Tuesday, August 3, 2021
 
user1865027
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :