[liphp] Big MySQL problem
Frank Imburgio
[email protected]
Wed, 04 Jun 2003 13:12:59 -0400
--Boundary_(ID_5uLEO17b0iBDsA5Jt++IWg)
Content-type: text/plain; charset=us-ascii; format=flowed
Content-transfer-encoding: 7BIT
Sean:
Good point about that -- in my days as a DBA, 'database tuning' almost
always involved rewriting killer queries like the one you describe. In
fact, from Chris's latest response, you hit the nail on the head!
One killer query I came across quite a bit was a broken join like you've
described, with a SELECT DISTINCT thrown in to 'fix' it. This then
returns the 'correct' result, but asks the database engine to do about
ten times more work then it has to do. Another was SELECT COUNT(*) of
things, just for the hell of it, like to see if a table exists (if not
SELECT COUNT(*) FROM employee;create table employee) -- the COUNT(*)
asks the engine to literally count all the rows, and this can be an
expensive thing to do....better to do: if not SELECT 1 FROM
employee;create table employee, which simply asks if the table exists
without also asking for a count.
A final example: select * from employee where
substring(employee_num,2,5) LIKE 'abc%'. An index built on employee_num
wont help here; indexes always index the entire key starting from the
left. If you have to query often on a substring like this, you can build
yourself an special index on the same substring and it will speed things
up immensely.
--Frank
Sean McCorkle wrote:
>>I had a similar problem however it was due to poor query design. Certain
>>queries would bog down my server big time. I had to re-evaluate the design
>>of my database, rebuild an index or two, and either rewrite certain
>>queries or eliminate them completely from my app.
>>
>>
>
> related:
> A common mistake is to do a join on two tables without some sort
> of relational constraint, like "where table1.col1 = table2.col2".
> The server then performs a cartesian join, giving you NxM rows,
> where N and M are the numbers of rows of table1 and table2. If both
> are large, it can hose you.
>
>=sean
>-------------------------------------------------------------------------------
>Sean R. McCorkle [email protected] | Haven't you heard, that aside
>Genome Groupph | from the details, there's
>Biology Departmentfax | nothing further to be found?
>Brookhaven National Laboratory | --Master Dogen's 300 Koan
>Upton, New York 11973 | Shobogenzo (commentary)
>
>
>_______________________________________________
>Liphp mailing list
>[email protected]
>https://www.liphp.org/mailman/listinfo/liphp
>
>
--Boundary_(ID_5uLEO17b0iBDsA5Jt++IWg)
Content-type: text/html; charset=us-ascii
Content-transfer-encoding: 7BIT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body>
Sean:<br>
<br>
Good point about that -- in my days as a DBA, 'database tuning' almost always
involved rewriting killer queries like the one you describe. In fact, from
Chris's latest response, you hit the nail on the head! <br>
<br>
One killer query I came across quite a bit was a broken join like you've
described, with a SELECT DISTINCT thrown in to 'fix' it. This then returns
the 'correct' result, but asks the database engine to do about ten times
more work then it has to do. Another was SELECT COUNT(*) of things, just
for the hell of it, like to see if a table exists (if not SELECT COUNT(*)
FROM employee;create table employee) -- the COUNT(*) asks the engine to literally
count all the rows, and this can be an expensive thing to do....better to
do: if not SELECT 1 FROM employee;create table employee, which simply asks
if the table exists without also asking for a count.<br>
<br>
A final example: select * from employee where substring(employee_num,2,5)
LIKE 'abc%'. An index built on employee_num wont help here; indexes always
index the entire key starting from the left. If you have to query often on
a substring like this, you can build yourself an special index on the same
substring and it will speed things up immensely.<br>
<br>
--Frank<br>
<br>
Sean McCorkle wrote:<br>
<blockquote type="cite"
cite="[email protected]">
<blockquote type="cite">
<pre wrap="">I had a similar problem however it was due to poor query design. Certain
queries would bog down my server big time. I had to re-evaluate the design
of my database, rebuild an index or two, and either rewrite certain
queries or eliminate them completely from my app.
</pre>
</blockquote>
<pre wrap=""><!---->
related:
A common mistake is to do a join on two tables without some sort
of relational constraint, like "where table1.col1 = table2.col2".
The server then performs a cartesian join, giving you NxM rows,
where N and M are the numbers of rows of table1 and table2. If both
are large, it can hose you.
=sean
-------------------------------------------------------------------------------
Sean R. McCorkle <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a> | Haven't you heard, that aside
Genome Groupph | from the details, there's
Biology Departmentfax | nothing further to be found?
Brookhaven National Laboratory | --Master Dogen's 300 Koan
Upton, New York 11973 | Shobogenzo (commentary)
_______________________________________________
Liphp mailing list
<a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a>
<a class="moz-txt-link-freetext" href="https://www.liphp.org/mailman/listinfo/liphp">https://www.liphp.org/mailman/listinfo/liphp</a>
</pre>
</blockquote>
<br>
</body>
</html>
--Boundary_(ID_5uLEO17b0iBDsA5Jt++IWg)--