|
|
Posted by Anze on 12/16/05 23:52
Hi!
> 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.
> 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. :)
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.
>> Actually, it should only take the records from srchIds that have a
>> corresponding entry in srchWords, where word is the one we are searching
>> for...
>
> It does take only the records from srchIds that have corresponding
> entry in srchWords (your query and mine with INNER JOIN).
>
>
>> So it should use category index.
>
> But category index is not related to this - it's not involved in joining
> the tables nor in filtering the results. Only thing it could be used
> for is grouping (but it will not help in any way even if you use some
> aggregation) or sorting (but you do not sort by category).
I meant "word" index, but I found out later that it doesn't work on "LIKE
'%abc%'"... :)
>> 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.
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
- 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
Thank you again for your help!
Anze
Navigation:
[Reply to this message]
|