Is there a better way to select...

[elsenorjose]

...where several columns have to meet a certain criteria?

I need to find URLs, count how many of them there are, and sum how many bytes were delivered where:

a) The URL has a certain string in it anywhere;
b) Certain other fields in the table are NULL or have missing data or a specific value:

select url, count(url), sum(bytes)
from url_table
where  url like '%somestring%'
and
((market is null or market = ' ' or market = '-')
or    (station is null or station = ' ' or station ='-')
or    (artist is null or artist = ' '  or artist = '-')
or    (track is null or track = ' ' or track = '-')
or    (label is null or label = ' ' or label - '-'))
group by url

The source data isn't very clean and I get NULLs, spaces, or hyphens to indicate missing values in those fields. (Yes, I know and the engineers ARE working on cleaning that up). But right now this is what I have to work with.

This thing is taking HOURS to run. Any suggestions?

 

[elsenorjose]

Well, I bet it would help if I didn't have a typo in my last OR clause...

or label - '-'

Uh...should be =

 

[r937]

no joy

your WHERE condition with the LIKE has a wildcard at the left, which means it must examine every single url in the table

this means a table scan, and that's why your query is slow

does it run any faster if you have just the LIKE condition and not all those other conditions?

r937.com | rudy.ca

 

[elsenorjose]

Hmmm...I haven't tried it but based on a similar query without those conditions, though, I suspect it would.

Would using REGEXP help or hinder? That string I'm looking for is always the same although it doesn't always occur in the same place in the URL every time.

 

[r937]

no joy

what about the source data? how do you get it? any chance you can run it through a text editor before loading it into the database?

r937.com | rudy.ca

 

[elsenorjose]

It's coming to me as text files...HUGE text files. Couple hundred MB each.

Well, this is a one off research project. Guess I'll just grin and bear it.