mysql查询执行时强制托管端到块数据库[关闭]

database of my website has been blocked by hosting side... they said you queries has been taken too much time to execute...the message has been send by them is:

    This message is to advise you of a temporary block placed on your database. The database "**DATABASE NAME**" was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system's CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case.

Running Processes:
fastlynx 26884 0.3 0.0 0 0 ? ZN 23:26 0:00 [php] <defunct>

Running Queries:
*************************** 1. row ***************************
USER: USERNAME
DB: SB_NAME
STATE: optimizing
TIME: 40
COMMAND: Query
INFO: SELECT * FROM price WHERE country LIKE '27'/*--*/and/*--*//*!30000if(ascii(substring((user()),11,1))<121,BENCHMARK(151973069.6,MD5(0x41)),0)*//*--*/and/*--*/'x'='x%' GROUP BY country ASC order by country asc limit 0, 12
*************************** 2. row ***************************
USER: USERNAME
DB: DB_NAME
STATE: optimizing
TIME: 54
COMMAND: Query
INFO: SELECT * FROM price WHERE country LIKE '27'/*--*/and/*--*//*!30000if(ascii(substring((user()),4,1))<122,BENCHMARK(151973069.6,MD5(0x41)),0)*//*--*/and/*--*/'x'='x%' GROUP BY country ASC order by country asc limit 0, 12

From above it seems like that queries from a single table are taking max execution time... i already have optimized all table in database through mysql control panel. Guys..Help me to handle this situation. Thanks in advance....

Edit :

Actual queries are as:

SELECT * FROM price WHERE country LIKE 'G%' GROUP BY country ASC limit 0, 12
                               OR
SELECT * FROM price WHERE prefix LIKE '91%' GROUP BY country ASC limit 0, 12

AND one thing more that....m also using NOT LIKE some times as lika that :

SELECT * FROM price WHERE country LIKE 'G%' AND country NOT LIKE 'INDIA%' GROUP BY country ASC limit 0, 12

Database Table Price screen shot

What do you mean with "optimized"? If you already did that, we can't do anything anymore for you.

But considering you use WHERE country LIKE '27' you didnt optimize that much. If the country is always an integer value, make sure country has a numeric datatype and that you put an index on it. Also query it with WHERE country='27', because a LIKE statement doesnt use the index but will do a full table scan. (If you have 1000 rows, every single time that query is executed, all these 1000 rows will be loaded into memory and checked.)

Also since you use GROUP BY and ORDER on country, so make double sure there is a proper index on this column.

But since the other part of your query is if(ascii(substring((user()),11,1))<121,BENCHMARK(151973069.6,MD5(0x41)),0) you can forget about a quick query. That little dragon will be a b*tch on a large table.

Since I dont know which part of that is dynamic and what is static, I can't realy provide optimization improvments. Perhaps give us some insights on what you are trying to accomplish so we can help define a better database structure.

Update:

Your query seems strange. Take the last one for example. You are selecting all countries starting with G and then canceling countries that start with INDIA. Last time I check the I is not a G, so the second part is useless and only adding overhead.

SELECT * FROM price WHERE country LIKE 'G%' AND country NOT LIKE 'INDIA%'

Now i dont know how many countries you have in your table, but unless your adding some on another planet, there can't be more then ~200. And with 200 records you can run almost any query without running into problems.

I also notice that you use GROUP BY on country, which would imply that countries are in the list several times. But since you use SELECT * and dont have any AGGEGRATE FUNCTIONS in there like SUM or COUNT, I would expect there is only one record of each country.

Taking all things in to account, this seems like a very bad design pattern and without knowing what you want to do with the data I cannot help you. Try to provided at least the following

  • What field from SELECT * are you planning on using, considering the group by?

  • What is the prefix? is it a numeric column or some sore of code? What do you expect in return when you use LIKE '91%'? countries with prefix '91' and '916'? or just '91'?

  • Are countries in the table more then once?

Update 2:

The screenshot makes things more clear. Since you are showing all information based on country (which is actually country + provider) I would make the following changes:

  • Drop the prefix column from this table and create a seperate table for the prefix with a link to the auto_id field in this table.

  • Add a unique index on the country column (and thus drop all duplicates). then you can select without GROUP BY if you dont need the prefix.

  • if you often select all countries starting with a single letter, add another column as char(1) and add the first letter of the country to that column on adding/updating a row. Put an index on that column and then you can select using SELECT * FROM price WHERE newcol='G'. You can still add the second part (NOT LIKE) if needed.

  • If you have a search function on country column, look into using FULL TEXT INDEX