|
|
Posted by Hilarion on 12/19/05 19:14
>> You should use "`" instead of "'" for "count" column. "'" is for string
>> values, and "`" is for identifiers. Also you do not really need to specify
>> "count" as alias for "a0.count" because it's allready named "count" so
>> you are not changing anything here.
>
> Is that cross-platform? I always thought that backticks ("`") are MySQL
> specific, but I might be wrong. But I know that ticks ("'") work on Oracle,
> MS SQL and MySQL, and probably also on postgreSQL and SQLite.
Not quite. Ticks (apostrophes) are for string values (it's in SQL standards).
They are NOT for quoting identifiers (even if they do work in some engines).
Oracle uses double quotes to quote identifiers, MS SQL uses square brackets
and MySQL uses backticks. In general avoid situations when you have to use
any way of quoting identifiers, which means NOT using reserved words as
identifiers, not using spaces in identifiers etc.
In this case you do not have to use any quoting, because you used
table prefix "a0.", but I'd rename the "count" column to something like
"cnt".
>> You also do not perform any aggregation, so GROUP BY here does nothing
>> (beside maybe slowing down the query execution).
>
> You are right - thank you! :)
>
>> So it should be something like this:
>
> Thanks, I'll try and benchmark both of the solutions!
> But I need to go to sleep first. :)
>
>>> Would LEFT JOIN speed up the queries? I have a feeling that it shouldn't
>>> matter...
>>
>> Try INNER JOIN (looking at your query left outer join is probably not what
>> you need). In general you should use JOIN syntax instead of table list
>> and WHERE clause (for join purposes, not for filtering).
>
> What you learn is what you use... I never learned JOINs and never bothered
> to use them properly. It might be time to do so. :)
JOINs are - in my personal opinion - one of the most important things
in SQL.
> Just for info - are they cross-platform? I don't want to be tied to any
> specific platform too closely, even if it is open source.
They are in SQL standard, so they are very cross-platform (they work
in MySQL, MS SQL and in Oracle 9 or 10). There are exceptions. The most
significant exception is Oracle, which does not support JOIN syntax in 8i
and previous versions (version 9 does support JOINs). It provides
a substitute (for some LEFT OUTER JOIN and RIGHT OUTER JOIN situations),
but it's not as powerful as standard JOIN.
Yes, you can use WHERE clause in all SQL engines to have same result
as INNER JOIN, but depending on DBMS engine, you can make your query
perform better when using JOIN syntax. In most cases there's no way
to use WHERE to emulate OUTER JOIN behavior.
>>> and... a small table. MySQL desided it was
>>> more efficient not to use indexes because the table was so small. When I
>>> ran the query on a bigger table it used the indexes. Weird. :(
>>
>> Which indexes?
>
> I must learn to write these answers when I'm thinking clearly... %-)
>
> I tried many different combinations of the query, one of them used
> " = 'abc'" instead of "LIKE '%abc%'" - and this one used an index on word
> field. But only on larger table - on small table mySQL optimizer decided
> that it is better not to use it. Which probably makes sense, but made it
> more difficult for me to figure out what was going on.
Ah, I see now. Some DBMS engines provide specific SQL syntax which
forces the engine to use (or not use) some specific index. I know
Oracle does. I do not know if MySQL also has something like this.
The syntax may be platform-dependant (in case of Oracle it's quite
safe, because it uses comments, so it'll not break the query for
other engines, which will just ignore the comment).
> So the next step (if I want to make subword search useable) is to:
> - benchmark INNER JOIN / my syntax
> - get rid of GROUP BY and benchmark the change
Yes, and yes.
> - try FULLTEXT search - it might be useful for faster searching of the
> records that have subwords in them, though I wouldn't hope too much
If it's about subwords, then fulltext indexes can make a big difference.
> Thank you again for your help!
No problem.
Hilarion
Navigation:
[Reply to this message]
|