NYCPHP Meetup

[David Krings]

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