Adrian Noland wrote:
The key to speedy SQL is in the indexes. Without an index the SQL engine
iterates over every row. With an index it automagically grabs the
correct value.
Good to know. I heard about indices before, but wasn't really aware that
they have such a huge impact. Since my days of maintaining Paradox
tables I am a bit scared of indices as for Paradox they are often more
trouble than they are worth. I know, SQL is a different universe.
Any good tips as what to index? I doubt that indexing every column makes
sense. I see what changes when I index those columns that I use within
selects. There are some columns that I read out after I made my
selection and also in a different script in a different context and
there for only one row from.
Try using EXPLAIN to check your queries if they don't seem to be working
as expected.
EXPLAIN SELECT * FROM table_name;
I will let SQL explain to me what I try to explain to SQL. While working
on a workaround I thought about what my misfiring query does and in fact
it does what I want it to do. I'll have a copy of the old file and I
will see what happens after indexing.
The workaround is something I used before and that works fairly well. I
select the wanted rows from each of the three tables. They are all keyed
based on an ID that logically links the records together (I don't use
set table links, because I don't know how that works in MySQL). I end up
with three arrays of IDs, I merge the arrays, make the values unique,
rekey, then populate a temporary table that has the ID column and the
few columns I want to do sorts on. I then pull a new array of IDs from
that table using a simple select with the desired sorting.
Of course, the db now has to process hundreds (thousands) selects and
deal with a temp table that requires simple inserts, but since each of
those queries runs in a few microseconds if not faster this may be OK
for what I need it for.
I read a few articles where some claim that people have no right to life
for using temp tables, but others say that they are helpful and make
things faster and easier, especially when it is about working through
complex selects.
And there I thought I'm almost done...LOL.
David
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
