MySQL SELECT rows that match a domain in URL column

Khalid Almannai 0
Viewed 87

I have a list of URLs where I want to limit the daily use for each domain using URL column that contain the full link.

If the number of daily usages exceeds 10 it won't execute the request.

Now I'm doing it like this

$mysqli = new mysqli("example.com", "user", "password", "database");
$query = "SELECT type FROM websites WHERE url=? and date=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('ss',$url,$strdate);
$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;

The above will limit the daily usage for specific page/full URL link and not by domain.

I want to count the rows in URLs column that contains that domain.

select mysql
1 Answers

Picking apart the URL column to check just the domain is quite inefficient. This because the only way to do it is to scan the entire table every time and check every row.

So instead, make sure you have the desired data in separate columns and have a suitable index. From what you have said, I suggest adding an extra column

domain VARCHAR(...) NOT NULL DEFAULT ''

and populate it as the rows are being inserted into the table.

Another issue is "daily count of 10". Is that "today, midnight to midnight"? If so, in whose timezone, the server's or the clients? Or perhaps you mean "in the last 24 hours". If the latter, then the query becomes

SELECT COUNT(*) FROM websites
    WHERE domain = ?
      and date > NOW() - INTERVAL 24 HOUR

That will deliver a scalar value; check it for being < 10.

(Note: That test works regardless of the data type of date (DATETIME, TIMESTAMP, etc), but will have minor hiccups twice a year as daylight saving turns on or off.)

For efficiency the table should have

INDEX(domain, date)

I'm puzzled as to why there is no test for user_id. Is there only one "user" of your site? Or maybe the limit of 10 applies ot everyone combined? If you do add such a test (AND user_id = ?), then the index becomes (user_id, domain, date)

Rick James0
Related